Source
r/excel•
kava

Visually impaired Person here having difficult time asking google the right question: I need to know how to compare two columns of information. Better details inside.

solved

Please be kind. I know this is likely a very easy question but I am visually impaired (read: blind) and am having quite a difficult time trying to phrase the question in a way that google can answer as I do not know the terminology. I'm having a hard time explaining it even here.

Here is what I need and I realize I sound like a 4th grader, I assure you I am not.

I have two columns of information.
I want Excel to compare the two columns.
I want the third column to tell me the items that are in the first list, but NOT in the second list.

Here is hopefully a picture that works as a good explanation of what I'm after. The first column is a list of fruit in a basket. The second column is the fruit that has been eaten. Excel would compare the two columns and give me the third column which would be the fruit that is left now that some fruit has been eaten.

I don't know how to post a picture to reddit so I used postimage.com, I'm hope that's okay

Excel-Fruit.jpg

Thank you in advance for any help, even if you can point me to a website or the terminology that would help me google this. Thank you kindly.

Posted Dec 7, 2022, 11:37 PM UTC

7821
GregorJEyre409234•

Hi, if you're on a newer version of Excel, you should be able to use this formula, just pop it into a cell on the side

=FILTER(first_range,NOT(ISNUMBER(MATCH(first_range,second_range,0))))

••

"first_range" would be the list of all your fruits

"second_range" would be the list of eaten fruits

56
finickyone1570•

Another variation on this with fewer function calls is

=FILTER(first_range,COUNTIF(second_range,first_range)=0)
11
GregorJEyre409234•

oh that's nice, ISNUMBER(MATCH(..)) is just my go to for finding if something is in something else, so this is just what came to me first

but thanks, might come in handy later 🤝

5
kavaOP•

Brilliant, this worked just fine. I was getting a spill error but realized it was because I defined a range as the whole column instead of just the cells with the data. Both versions worked perfectly - thank you for saving me so much time and aggravation.

3
JHKerr14•

This is the best way.

5
OphrysApifera•

This is the way.

5
kavaOP•

Brilliant, this worked just fine. I was getting a spill error but realized it was because I defined a range as the whole column instead of just the cells with the data. Both your version and u/finickyone worked perfectly. Thank you, you've saved me so much time and aggravation.

2
AutoBot5•

Hey, hopeful one of the responses helped you out!

I just wanted to say that you described your question and what you needed perfectly. Actually far better than some people I know with college degrees!

22
Grey_Patagonia_Vest46•

What makes you think they don’t have a college degree?? 🤔

0
AutoBot5•

My reply was in regards to OP saying they may sound like a grader. Wasn’t implying anything relating to OP’s actually education.

6
timespreader66•

With your two source columns being A and B,
put this in the third column and copy down.

  • =IF(A1="","",(IF(ISNA(MATCH(A1,B:B,0)),A1,"")))

It will return entries that are in A only.

7
OphrysApifera•

This works on older versions of Excel as well. Bonus points.

3
timespreader66•

Good to hear; thanks for this feedback.

3
lothcent•

I am no expert- but perhaps this might help you out how to compare 2 columns in excel

after lurking here awhile- I know that there are talented people that might have a better answer

6
Decronym•

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

|Fewer Letters|More Letters| |-------|---------|---| |COUNTIF|Counts the number of cells within a range that meet the given criteria| |FILTER|Office 365+: Filters a range of data based on criteria you define| |IF|Specifies a logical test to perform| |ISNA|Returns TRUE if the value is the #N/A error value| |ISNUMBER|Returns TRUE if the value is a number| |MATCH|Looks up values in a reference or array| |NOT|Reverses the logic of its argument|

^(Beep-boop, I am a helper bot. Please do not verify me as a solution.)
^(7 acronyms in this thread; )^(the most compressed thread commented on today)^( has 17 acronyms.)
^([Thread #20559 for this sub, first seen 7th Dec 2022, 23:58]) ^[FAQ] ^([Full list]) ^[Contact] ^([Source code])

4
great_raisin1•

Good bot

-2
discountgenghiskhan•

This is the most well described question I’ve ever seen, hope solutions posted work for you, good luck

3
Luckypenny4683•

OP, this is easily, the most kind, helpful, sub on this site. You should ask all the questions that you need help with! Everyone is very cool here.

3
AutoModeratorMOD•

u/kava - Your post was submitted successfully.

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

1
lykewtf•

How sad that you have to ask people to be kind.

1
ImMrAndersen•

Please don't feel like you have to justify asking questions - i think that that is what this sub is for! Furthermore, not even that easy of a task. I see that smart people have already commented with more elegant solutions than i would have provided, so that is all I have to say.

Best of luck in your future excel endeavours, feel free to ask if you hit any barriers again, most likely, someone here will have a great solution and lots of others (myself included) can learn something new!

1