Find values in Column B that are not in column A? [closed]

asked 2013-02-02 05:43:39 +0100

pueblonative
13 ●1 ●2 ●3

I guess I'm looking for a SQL Difference like function

I have column A with login ids

I have column B with all possible login IDs

I want to find a way to display in Column C which items in Column B are not being used. Would this require a function or a macro?

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2015-10-21 04:12:52.319237

answered 2013-02-02 10:39:47 +0100

razon_22
2069 ●2 ●23 ●27

It can be done with the VLOOKUP function. Assuming there is no header row for the data, paste this function in cell C1.

=VLOOKUP(B1,$A$1:$A$100,1, )

If you have more than 100 rows of data, change the 100 in the formula to match the number of rows you have. Click in cell C1 then drag the handle down to the last row of data. This will copy the formula down. The function will look for the items in column B and see if they are in column A. If the item is found, column C will show the id, if not, it will show N/A. So the id's in column B that have N/A next to them are not being used.

edit flag offensive delete link

Comments

Also you can use formula =COUNTIF($A$1:$A$100;B1)=0 Or if the number of values in column A is less than 1024, you can use this method

JohnSUN ( 2013-02-04 08:36:03 +0100 )edit

This answer works well for me. The only hiccup that I encountered is that, for numeric data, I had to include FALSE as the final argument to VLOOKUP to indicate that my data is not sorted. Otherwise, false positives would appear where IDs were shown that did not match the lookup value exactly.

RNanoware ( 2018-07-10 18:44:11 +0100 )edit

add a comment

Find values in Column B that are not in column A? [closed]

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2015-10-21 04:12:52.319237

1 Answer

Comments

Question Tools

Stats

Related questions

Find values in Column B that are not in column A? [closed] edit

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp close date 2015-10-21 04:12:52.319237

1 Answer

Comments

Question Tools

Stats

Related questions

Find values in Column B that are not in column A? [closed]

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2015-10-21 04:12:52.319237