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

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)