t_kid
05-12-2012, 01:30 AM
Hi Guys,
A few weeks ago I wanted to match names, i.e. if columns A and B contain names, I wanted column C to tell me if there was a match without repeats, so if there was a match for cell A1 somewhere in column B then C1 would display Y and display N otherwise, with the condition that if A2 had the same name as A1, but column B contained the name only once, C2 would display N. mikerickson gave me the following formula which worked fantastic.
The formula was =IF(COUNTIF($A$1:$A1, $A1)<=COUNTIF($B:$B, $A1), "Y", "N")
I now want to do more than match names. For example, let columns A and B have names and dates respectively, and columns C and D also have names and dates respectively, and I now want to match names AND dates with no repeats. So if A1=James and B1=08/02/2012 corresponding to James, I want to know if there is a match in columns C and D, that is, is there some Cn=James with Dn=08/02/2012.
I tried to extend the formula (now entered into column E) you gave me as follows:
=IF(AND(COUNTIF($A$1:$A1,$A1)<=COUNTIF($C:$C, $A1), COUNTIF($B$1:$B1, $B1)<=COUNTIF($D:$D, $B1)), "Y", "N")
For the most part it works, but sometimes not. For instance, if there was an Andrew also with 08/02/2012 in C1 and D1 respectively, my understanding is that if James is somewhere further down the list in columns C and D, "N" will be displayed, because there are more "08/02/2012" occurrences in the C and D columns. At least this is why I believe it doesn't work in these cases. Do you know of any way around this problem?
I truly appreciate any help you can give.
t_kid
A few weeks ago I wanted to match names, i.e. if columns A and B contain names, I wanted column C to tell me if there was a match without repeats, so if there was a match for cell A1 somewhere in column B then C1 would display Y and display N otherwise, with the condition that if A2 had the same name as A1, but column B contained the name only once, C2 would display N. mikerickson gave me the following formula which worked fantastic.
The formula was =IF(COUNTIF($A$1:$A1, $A1)<=COUNTIF($B:$B, $A1), "Y", "N")
I now want to do more than match names. For example, let columns A and B have names and dates respectively, and columns C and D also have names and dates respectively, and I now want to match names AND dates with no repeats. So if A1=James and B1=08/02/2012 corresponding to James, I want to know if there is a match in columns C and D, that is, is there some Cn=James with Dn=08/02/2012.
I tried to extend the formula (now entered into column E) you gave me as follows:
=IF(AND(COUNTIF($A$1:$A1,$A1)<=COUNTIF($C:$C, $A1), COUNTIF($B$1:$B1, $B1)<=COUNTIF($D:$D, $B1)), "Y", "N")
For the most part it works, but sometimes not. For instance, if there was an Andrew also with 08/02/2012 in C1 and D1 respectively, my understanding is that if James is somewhere further down the list in columns C and D, "N" will be displayed, because there are more "08/02/2012" occurrences in the C and D columns. At least this is why I believe it doesn't work in these cases. Do you know of any way around this problem?
I truly appreciate any help you can give.
t_kid