Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Show corresponding duplicate row number (Excel 2003)

    Hi all,

    Using this formula will shows you the same row number of the duplicate item.

    =IF(COUNTIF($A$1:$A$10,A1)>1,ROW(),"")

    Result : if cell A1 and A7, this will show the row as 1 and 7 respectively.

    How can I tweak it to show the corresponding duplicate row number?

    eg. cell A1 will show the result of A7 and cell A7 will show the result A1

    Is this possible?

    regards, francis
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Show corresponding duplicate row number (Excel 2003)

    The request you have poses some complications if there are more than two duplicates. What must happen if there are three or more duplicates. This is then not just a matter of inverting the number?
    Regards,
    Rudi

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Show corresponding duplicate row number (Exce

    Francis

    By far the easiest method in this situation is to use Conditional Formatting using the format in the image below,as Rudi suggests what is to happen if there is more than one duplicate. At Least this highlights them <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Jerry

  4. #4
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Show corresponding duplicate row number (Exce

    Hi Jezza,

    Thanks, agreed that conditional formatting does serves it purpose in finding duplicates. .

    Hi Rudi, thank for the headup!
    if that happen as suggested, is it possible to have the third duplicate show the row number of the first two, <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>


    regards, francis
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Show corresponding duplicate row number (Excel 2003)

    How about:
    =IF(COUNTIF($A$1:$A1,A1)>1,"A"&MATCH(A1,$A$1:A1,0) ,"")

    Steve

  6. #6
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Show corresponding duplicate row number (Excel 2003)

    Hi Steve,

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> this work as expected.Would you mind explain what the "A" in front of & is for. I understand the first part and the last part of the formula, but
    unsure about the "A". <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30>

    cheers, francis
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Show corresponding duplicate row number (Excel 2003)

    You wanted the formula to return something like A3 or A7.
    MATCH(A1,$A$1:A1,0) is the row number such as 3 or 7. "A" puts the A in front of the row number.

Posting Permissions

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