# Using duplicate cell linked data

1. ## 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

2. ## Re: Using duplicate cell linked data

Try:

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

Regards

3. ## Re: Using duplicate cell linked data

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.

4. ## Re: Using duplicate cell linked data

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

5. ## Re: Using duplicate cell linked data

Hi TMShucks

It works!!

But then you knew that. Well done!

I'm still unclear what the formula is actually doing. Any enlightment would be very much educative and appreciated.

Many thanks

6. ## Re: Using duplicate cell linked data

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

7. ## Re: Using duplicate cell linked data

Hi again

Awesome! It is such a buzz when the light goes on.

Many thanks for your generous assistance. You are building a better world!!

The more I work with excel the more I realise how little I know.

Cheers

8. ## Re: Using duplicate cell linked data

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

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1