# Thread: Compare data in two columns

1. ## Compare data in two columns

I have data in Column A and in Column E from row 2 onwards.

1) I would like to set up a formula in column F compare the data in Column E to Column A and where data exists in Column E, but not in Column A, I would like this extracted
2) I would like to set up a formula in column G compare the data in Column A to Column E and where data exists in Column A, but not in Column E, I would like this extracted
3) I would like to set up a formula in Column H or VBA code to compare column A to Column E and extract the numbers that are unique to both

http://www.excelfox.com/forum/f22/ex...-458/#post1769

2. F3: =IF(ISNUMBER(MATCH(E3,\$A\$3:\$A\$18,0)),"",E3)

G3: =IF(ISNUMBER(MATCH(E3,\$A\$3:\$A\$18,0)),"",E3)

H3: =IF(COUNT(\$F\$3:\$G\$18)<ROW()-ROW(\$F\$3)+1,"",SMALL(\$F\$3:\$G\$18,ROW()-ROW(\$F\$3)+1))

Copy down the column

Steve

3. ## The Following User Says Thank You to sdckapr For This Useful Post:

HowardC (2012-07-01)

4. Hi Steve

Thanks for the help, this is much appreciated

It would be appreciated if you you can explain the formula =IF(COUNT(\$F\$3:\$G\$18)<ROW()-ROW(\$F\$3)+1,"",SMALL(\$F\$3:\$G\$18,ROW()-ROW(\$F\$3)+1))

Regards

Howard

5. The expression:
row()-row(\$f\$3)+1

Takes the row numbef of the formula, subtracts the row of F3 (=3) and adds 1 to it. It is a way to index the rows since you are not starting on the first row [thus: Row3 = 1, Row4 = 2, etc ]

count(\$f\$3:\$g\$18) gives the count of the items in the F3:G18

Therefore:
count(\$f\$3:\$g\$18)<row()-row(\$f\$3)+1
Determines if the current "index" is greater than the total count of items. If the "index" is greater than the count, then there is no value to print, so it puts a null string (""). If the index is one of the counts the formula:
small(\$f\$3:\$g\$18,row()-row(\$f\$3)+1)

Is calculated. This gets the smallest "index" value for the items in the list. At index row 1 (= row3) the smallest value from the list is given, at row4 (index2) the 2nd smallest, row5 (index 3) the 3rd smallest, etc.

Steve

6. ## The Following User Says Thank You to sdckapr For This Useful Post:

HowardC (2012-07-01)

7. Hi Steve

Thanks for taking the time to explain how the formula works. I fully understand it now

Regards

Howard

8. Originally Posted by sdckapr
F3: =IF(ISNUMBER(MATCH(E3,\$A\$3:\$A\$18,0)),"",E3)

G3: =IF(ISNUMBER(MATCH(E3,\$A\$3:\$A\$18,0)),"",E3)

H3: =IF(COUNT(\$F\$3:\$G\$18)<ROW()-ROW(\$F\$3)+1,"",SMALL(\$F\$3:\$G\$18,ROW()-ROW(\$F\$3)+1))

Copy down the column

Steve
Hi Steve

I have a new scenario. I would like to compare Col A & E and only extract numbers that are different in Col I

Regards

Howard

9. Perhaps I don't understand the difference, but doesn't putting the formula I list in Col H in Col I work?

Steve

10. ## The Following User Says Thank You to sdckapr For This Useful Post:

HowardC (2012-07-07)

11. The formula does work when doing do

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•