J JoeM May 1, 2009 #1 I have a number of ids in two columns and am trying to represent which ones in the second column also appear in the first.
I have a number of ids in two columns and am trying to represent which ones in the second column also appear in the first.
T T. Valko May 1, 2009 #2 One way... Comparing column B to column A with the list in column A in the range A1:A20. =IF(COUNTIF(A$1:A$20,B1),"match","") Copy down as needed.
One way... Comparing column B to column A with the list in column A in the range A1:A20. =IF(COUNTIF(A$1:A$20,B1),"match","") Copy down as needed.
J Jacob Skaria May 1, 2009 #3 Apply the formula in C1 which will check the value of B1 in A and return Y or N =IF(COUNTIF(A:A,B1)>0,"Y","N")
Apply the formula in C1 which will check the value of B1 in A and return Y or N =IF(COUNTIF(A:A,B1)>0,"Y","N")
G Glenn May 1, 2009 #4 JoeM said: I have a number of ids in two columns and am trying to represent which ones in the second column also appear in the first. Click to expand... Assuming your two columns are A and B, select column B and enter this for conditional formatting: =COUNTIF($A:$A,B1)>0 Select the format you want to "represent" matches.
JoeM said: I have a number of ids in two columns and am trying to represent which ones in the second column also appear in the first. Click to expand... Assuming your two columns are A and B, select column B and enter this for conditional formatting: =COUNTIF($A:$A,B1)>0 Select the format you want to "represent" matches.
H Harlan Grove May 1, 2009 #5 T. Valko said: One way... Click to expand... And the slow way . . . Comparing column B to column A with the list in column A in the range A1:A20. =IF(COUNTIF(A$1:A$20,B1),"match","") Click to expand... Bit faster to use =IF(COUNT(MATCH(B1,A$1:A$20,0)),"match","") or just =MATCH(B1,A$1:A$20,0) which would return a number when there's a match and #N/A when there isn't.
T. Valko said: One way... Click to expand... And the slow way . . . Comparing column B to column A with the list in column A in the range A1:A20. =IF(COUNTIF(A$1:A$20,B1),"match","") Click to expand... Bit faster to use =IF(COUNT(MATCH(B1,A$1:A$20,0)),"match","") or just =MATCH(B1,A$1:A$20,0) which would return a number when there's a match and #N/A when there isn't.
T T. Valko May 1, 2009 #6 I'm bored too! -- Biff Microsoft Excel MVP Harlan Grove said: And the slow way . . . Bit faster to use =IF(COUNT(MATCH(B1,A$1:A$20,0)),"match","") or just =MATCH(B1,A$1:A$20,0) which would return a number when there's a match and #N/A when there isn't. Click to expand...
I'm bored too! -- Biff Microsoft Excel MVP Harlan Grove said: And the slow way . . . Bit faster to use =IF(COUNT(MATCH(B1,A$1:A$20,0)),"match","") or just =MATCH(B1,A$1:A$20,0) which would return a number when there's a match and #N/A when there isn't. Click to expand...
