Results 1 to 8 of 8
Thread: Compare data in two columns

20120701, 05:52 #1
 Join Date
 Feb 2008
 Posts
 1,450
 Thanks
 128
 Thanked 6 Times in 6 Posts
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
Your assistance is most appreciated
http://www.excelfox.com/forum/f22/ex...458/#post1769

20120701, 07:03 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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
SteveLast edited by sdckapr; 20120701 at 07:07.

The Following User Says Thank You to sdckapr For This Useful Post:
HowardC (20120701)

20120701, 07:17 #3
 Join Date
 Feb 2008
 Posts
 1,450
 Thanks
 128
 Thanked 6 Times in 6 Posts
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

20120701, 13:36 #4
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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

The Following User Says Thank You to sdckapr For This Useful Post:
HowardC (20120701)

20120701, 14:31 #5
 Join Date
 Feb 2008
 Posts
 1,450
 Thanks
 128
 Thanked 6 Times in 6 Posts
Hi Steve
Thanks for taking the time to explain how the formula works. I fully understand it now
Regards
Howard

20120704, 11:01 #6
 Join Date
 Feb 2008
 Posts
 1,450
 Thanks
 128
 Thanked 6 Times in 6 Posts

20120705, 05:57 #7
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Perhaps I don't understand the difference, but doesn't putting the formula I list in Col H in Col I work?
Steve

The Following User Says Thank You to sdckapr For This Useful Post:
HowardC (20120707)

20120707, 09:19 #8
 Join Date
 Feb 2008
 Posts
 1,450
 Thanks
 128
 Thanked 6 Times in 6 Posts
The formula does work when doing do