spreadsheet Quandary

[chip83]

I can use Numbers or Mac Excel 2011.

I have a list of 1400 names and addresses that all require labels, I have a second list of 682 names that already have labels. I need to know if there is a way to take the first list and the second list and output a list that shows me the remaining names that need labels. In a math sense I need list A- list B = list C.

Does anyone know how to do this or am I stuck doing it the old fashioned way with Paper and highlighters/sharpies?

 

[jaduff46]

If the names in the shorter list are consistent with the names in the longer list (i.e same exact spelling), then you can use vlookup in excel on the longer list of names to see if the name is in the shorter list.

Use FALSE as the last argument in the Vlookup call to indicate you're looking for an exact match in the shorter list. Probably helps to sort both lists into name order so you can see what's going on, but not strictly necessary.

So something like

Vlookup(a1, $g$1:$n$681,1,false), where a1 is the name you're looking up, $g1:$n$681 is the shorter list of names and addresses, 1 is the column in the shorter list containing the names, and false indicates look for an exact match.

This should return either the name in the shorter list matching the name in the longer list, or an #N/A error indicating the longer list name is not in the shorter list. You can vary the third argument (1) to return different elements from the shorter list such as the city or zip code.

Lots of info on the web on MS Excel vlookup and how to do this, but this should get you looking in the right place (no pun intended). Just try it out and play around.

 

[campyguy]

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

More info on both functions here:
https://support.office.com/en-us/ar...0a0-a171-f2d869135665?ui=en-US&rs=en-US&ad=US
https://support.office.com/en-us/article/MATCH-function-e8dffd45-c762-47d6-bf89-533f4a37673a

Both lists and arrays work, it's a simple matter to extract the data you're looking for and strip out the extraneous cells to make a new, contiguous list (if you wish...).