PDA

View Full Version : Search and Compare



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

Bob Phillips
05-12-2012, 01:58 AM
If you have Excel 20907 or 2010 use

=IF(COUNTIFS($A$1:$A1,$A1,$B$1:$B1,$B1)<=COUNTIFS($C:$C,$A1,$D:$D,$B1), "Y", "N")

GTO
05-12-2012, 06:07 PM
If yoou have Excel 20907...

Wow! I've heard of advanced copies, but jeepers!:bow:

Bob Phillips
05-13-2012, 04:16 AM
Haven't you got that version yet you old dinosaur? :devil2:

GTO
05-13-2012, 04:39 AM
Creak...thunk...Ouch!

Aussiebear
05-13-2012, 04:18 PM
Hmmmm... death by falling stone tablet, I guess.

t_kid
06-03-2012, 01:59 AM
Hi xld,

Thank you so much, this done exactly what I needed!

Really appreciate your help