Thread: Using duplicate cell linked data

Hi all

Am struggling with this problem which is eluding me. Your assistance is greatly appreciated.

I have in column C a list of paired duplicate names (text) which can be separated by rows (see John 1) or sequential (see Finn 1) and in column D associated dates. For example say in C3 is the name John 1 (first occurance) and in C7 is John 1 (duplicate).

I would like to write a formula in the cell in column K of the same row of the duplicate ie row 7 which will use the return date value that's in D3 once first occurance of John 1 has been found.

Is this possible?

C................D............K
Milarn 1......12/5/11....
Spencer 1...23/2/11....
John 1........05/1/11....
Finn 1.........31/5/11....
Finn 1.........24/2/11...
Cooper 1.....20/3/11...
John 1.........21/2/11....Formula in this cell to use 05/1/11 eg is 01/1/11 <05/1/11?
Alyiah 1.......10/5/11...

Formula is to appy to all cells in column K where there are entries in column C.

I hope what I have set out is clear and a solution is possible?

Many thanks in anticipation.

Cheers

mgerada

Try:

=IF(COUNTIF(C:C,C2)>1,IF(VLOOKUP(C2,C:D,2,FALSE)<>D2,VLOOKUP(C2,C:D,2,FALSE),""),"")

Regards

Hi TMShucks

Thx for your thoughts however I am not sure which cell this formula

=IF(COUNTIF(C:C,C2)>1,IF(VLOOKUP(C2,C:D,2,FALSE)<>D2,VLOOKUP(C2,C:D,2,FALSE),""),"")

is meant to go in and what the actual parts are retrieving. Could you pls help a slow learner with some further explanation?
Many thanks.
mgerada

I have assumed your data starts in row 2 allowing for a header.

This is the formula for cell K2 ... and then copy down.

Regards

Yes, I knew that, at least with your small sample. ;-)

=IF(COUNTIF(C:C,C2)>1,IF(VLOOKUP(C2,C:D,2,FALSE)<>D2,VLOOKUP(C2,C:D,2,FALSE),""),"")

VLOOKUP(C2,C:D,2,FALSE) use the value in cell C2 to search column C and return the matching value in column D

IF(VLOOKUP(C2,C:D,2,FALSE)<>D2 ... checks to see if the value returned is NOT the same as the value in cell D2 ... which should be the case for values other than the first value.

IF(COUNTIF(C:C,C2)>1 ... only do evaluate the following formulae if there is more than one entry equal to the value in C2.

So, if there's more than one entry for a given name, check to see if the date is different and, if it is, return the date. Otherwise, if there's only one entry for a name, or if the date is the same in the entries found, return a blank value.

Hope that helps

Regards

You're welcome. Thanks for the rep.

If this has answered your question, please mark your thread as solved. See my signature for details or the FAQ.

Regards