Results 1 to 2 of 2

Thread: List comparison

  1. #1
    Join Date
    Dec 2003
    Location
    UK
    Posts
    1

    Exclamation Unanswered: List comparison

    I have two integer lists (columns) in an excel sheet. I need to compare each value in list one to check if the same value is in list two, and if it is to increment a counter by one - each time the same integer occurs in both lists...
    I can do this writing a very lengthy macro - but am hoping someone may know of a function I can use which will do the trick :-)
    Thanks
    Ed

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    Not sure I fully have what you want, but perhaps this will get you started. Suppose list 1 is in Column A, and the other in Column E.

    In B1 put this formula:

    =IF(COUNTIF(E:E,A1)>0,3,1)

    copy down to the bottom of column A.

    Then in F1 put this formula:

    =IF(COUNTIF(A:A,E1)>0,3,2)

    and copy down to bottom of column E.


    Now, those in column B with 1 are unique to Column A, those with 3, are in both Col. A and Col E. In Column F, if 2, then unique to Col E, if 3, then both in Col. A and Col. C.

    Then use this formula as the tally, in G1:

    =COUNTIF(B:B,1)+COUNTIF(F:F,2)
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

Posting Permissions

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