# Comparing two columns of data

0 Reputation points
2024-08-31T18:11:45.4366667+00:00

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?

Excel
Excel
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
1,822 questions

1. 116.6K Reputation points
2024-08-31T20:14:19.4166667+00:00

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.

2. 9,175 Reputation points Microsoft Vendor
2024-09-02T07:39:01.1466667+00:00

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.