Comparing two columns of data

Question

Hello

In Excel I have two columns of data - one of which is longer than the other.

I want to compare the two and to identify which items are missing from the shorter column so that I can go search for them and then import them.

Does anyone know how I can I do this please?

Answer 1

For example, if A1:A10 is the long column, B1:B3 is the short column, then try to enter the next formula to C1:

=FILTER(A1:A10,XLOOKUP(A1:A10,B1:B10,B1:B10,"?")="?")

Use ; instead of , depending on your Excel.

Answer 2

Hi @Paul Calderwood

In earlier versions of Excel, you may try Index and Small function.

In my sample, Column 1 compared to Column 2, to find out Column 1 missing data, the formula could be =INDEX(B$1:B$9,SMALL(IF(COUNTIF($A$2:$A$5,B$2:B$9)=1,1000,ROW($2:$9)),ROW(A1))), then we need to press Ctrl + Shift + Enter to switch it to be an array formula.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

---