+ Reply to Thread
Results 1 to 8 of 8

Using duplicate cell linked data

  1. #1
    Registered User
    Join Date
    03-23-2009
    Location
    Ballina, Australia
    MS-Off Ver
    Excel 2013
    Posts
    22

    Unhappy 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
    Last edited by mgerada; 07-25-2011 at 06:40 AM.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    33,525

    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
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    03-23-2009
    Location
    Ballina, Australia
    MS-Off Ver
    Excel 2013
    Posts
    22

    Cool 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.
    mgerada
    Last edited by mgerada; 07-24-2011 at 07:01 AM. Reason: Solved

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    33,525

    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. #5
    Registered User
    Join Date
    03-23-2009
    Location
    Ballina, Australia
    MS-Off Ver
    Excel 2013
    Posts
    22

    Smile 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

    mgerada

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    33,525

    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. #7
    Registered User
    Join Date
    03-23-2009
    Location
    Ballina, Australia
    MS-Off Ver
    Excel 2013
    Posts
    22

    Smile 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

    mgerada

  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    33,525

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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