Compare two columns and find the difference between the two column
 

I have two columns, 1 with 3800 entries and 2 with 1500 entries. I want to
find the entries which are in column 1 but not in column 2.
--
kpk

Answer: Compare two columns and find the difference between the two column
 

Here's how you can compare two columns and find the difference between them:

1. First, select a blank cell where you want to display the results of the comparison.
2. In that cell, type the following formula:
   Code:

   ---

   =IFERROR(INDEX(Column1,MATCH(0,COUNTIF(Column2,Column1),0)),"")

   ---

3. Replace "Column1" with the range of your first column (e.g. A1:A3800).
4. Replace "Column2" with the range of your second column (e.g. B1:B1500).
5. Press Ctrl + Shift + Enter to enter the formula as an array formula. This will ensure that the formula is applied to all cells in the selected range.
6. The formula will return the first value in Column1 that is not in Column2. To find the rest of the values, simply drag the formula down to the rest of the cells in the column.

This formula uses the INDEX and MATCH functions to find the first value in Column1 that is not in Column2. The COUNTIF function is used to count the number of times each value in Column1 appears in Column2. The IFERROR function is used to display a blank cell if there are no more values to compare.

Compare two columns and find the difference between the two column
 

I'd use a formula like this in a helper column:

=isnumber(match(a1,sheet2!a:a,0))
and drag down.

True means that a1 appears in sheet2, column A.

If that's not enough...

Chip Pearson has lots of techniques to work with duplicates:
http://www.cpearson.com/excel/Duplicates.aspx

kpk wrote:

I have two columns, 1 with 3800 entries and 2 with 1500 entries. I want to
find the entries which are in column 1 but not in column 2.
--
kpk

--

Dave Peterson