Results 1 to 8 of 8
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 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
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    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

    Steve
    Last edited by sdckapr; 2012-07-01 at 07:07.

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

    HowardC (2012-07-01)

  4. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 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

  5. #4
    WS Lounge VIP sdckapr's Avatar
    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

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

    HowardC (2012-07-01)

  7. #5
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Steve

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

    Regards

    Howard

  8. #6
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Quote Originally Posted by sdckapr View Post
    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. #7
    WS Lounge VIP sdckapr's Avatar
    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

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

    HowardC (2012-07-07)

  11. #8
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    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
  •