+ Reply to Thread
Results 1 to 9 of 9

Find duplicate info in two columns and copy info from duplicate rows

  1. 11-09-2011, 11:27 PM #1
    USGS
    USGS is offline
    Registered User
    Join Date
    11-09-2011
    Location
    Oregon
    MS-Off Ver
    Excel 2003
    Posts
    6

    Angry Find duplicate info in two columns and copy info from duplicate rows

    Hello,

    I could really use some help! I've got 65000 items in an excel sheet. Long explanation short:
    Product code lists in Column B and Column AA

    I need to find products that are in Column B that also exist in Column AA

    When there is a Product in Column B that is also in Column AA I want the information from Column D,F,G and H (in the same row as the duplicate product shown in Column B) to be copied into Columns AM, AN, AO and AP in the same row that is the duplicate part from Column B in Column AA... see the sample! The desired result is in red.

    THANK YOU THANK YOU THANK YOU!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 11-10-2011, 12:19 AM #2
    Azam Ali
    Azam Ali is offline
    Valued Forum Contributor
    Join Date
    05-23-2011
    Location
    Lahore PK
    MS-Off Ver
    Excel 2007, 2013
    Posts
    627

    Re: Find duplicate info in two columns and copy info from duplicate rows

    in cell # AM1 use

    =IF(ISERROR(INDEX(D:D,MATCH($AA1,$B:$B,0))),"",INDEX(D:D,MATCH($AA1,$B:$B,0)))

    and copy on the range
    Azam
    If you want to say Thank you to a member, click the reputation icon (Star) in the left bottom of the post.
    For prompt answer, be descriptive, concise, short, direct, and to-the-point.
    Reply With QuoteRegister To Reply
  3. 11-12-2011, 03:06 AM #3
    USGS
    USGS is offline
    Registered User
    Join Date
    11-09-2011
    Location
    Oregon
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Find duplicate info in two columns and copy info from duplicate rows

    This should be simple but it's not. I just don't grasp this excel stuff!

    column B has 65535 part #s. column AA has the same amount. Some will be the same. If column AA has a part # that is listed in column B I want to copy the information next to column B (same row) in columns F,G, and H and paste them in columns AM,AN, and AO (Same row has the duplicate part # showing in column AA)

    If this is not possible will someone help me with a basic countif to put a number in column AM if there is a duplicate part # showing in AA? That would even be a great help at this point! THANK YOU THANK YOU!
    Reply With QuoteRegister To Reply
  4. 11-12-2011, 04:11 AM #4
    Azam Ali
    Azam Ali is offline
    Valued Forum Contributor
    Join Date
    05-23-2011
    Location
    Lahore PK
    MS-Off Ver
    Excel 2007, 2013
    Posts
    627

    Re: Find duplicate info in two columns and copy info from duplicate rows

    Hi USGS
    dont this formula give the required results?

    =IF(ISERROR(INDEX(D:D,MATCH($AA1,$B:$B,0))),"",INDEX(D:D,MATCH($AA1,$B:$B,0)))

    as mentioned above
    Reply With QuoteRegister To Reply
  5. 11-12-2011, 04:26 AM #5
    USGS
    USGS is offline
    Registered User
    Join Date
    11-09-2011
    Location
    Oregon
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Find duplicate info in two columns and copy info from duplicate rows

    No, it doesn't work. I found a duplicate part # and tested it. It just remains blank.
    Reply With QuoteRegister To Reply
  6. 11-12-2011, 04:31 AM #6
    Azam Ali
    Azam Ali is offline
    Valued Forum Contributor
    Join Date
    05-23-2011
    Location
    Lahore PK
    MS-Off Ver
    Excel 2007, 2013
    Posts
    627

    Re: Find duplicate info in two columns and copy info from duplicate rows

    kindly upload a sample workbook by using the above formula and your desired results
    Reply With QuoteRegister To Reply
  7. 11-12-2011, 04:42 AM #7
    USGS
    USGS is offline
    Registered User
    Join Date
    11-09-2011
    Location
    Oregon
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Find duplicate info in two columns and copy info from duplicate rows

    Attached. THANK YOU for taking another look at it!

    You'll see the duplicate part #s highlighted in yellow.
    The desired result is when a part # shows up in AA that is also in B the URLs from F,G and H are posted in AM, AN, and AO. You'll see my desired results in red font. The end result is that the URLs are in the same row as the AA part # not in the original B part # row.

    Thank you again for your help!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 11-12-2011, 05:37 AM #8
    Azam Ali
    Azam Ali is offline
    Valued Forum Contributor
    Join Date
    05-23-2011
    Location
    Lahore PK
    MS-Off Ver
    Excel 2007, 2013
    Posts
    627

    Re: Find duplicate info in two columns and copy info from duplicate rows

    see attatchment

    actually there are spaces after the codes in both B & AA column. for this reason above formula was not working. An alternate is to add the trim fucntion to eliminate these spaces
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 11-12-2011, 07:31 PM #9
    USGS
    USGS is offline
    Registered User
    Join Date
    11-09-2011
    Location
    Oregon
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Find duplicate info in two columns and copy info from duplicate rows

    Perfect! THANK YOU!!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1