+ Reply to Thread
Results 1 to 12 of 12

Combine duplicate rows with conditions

  1. #1
    Registered User
    Join Date
    07-24-2014
    Location
    Los Angeles
    MS-Off Ver
    Mac 2011 and PC 2010
    Posts
    6

    Combine duplicate rows with conditions

    Hi all,

    Interested in who can solve this problem. I have multiple tests (about 2000) in column A, many of which are duplicated 2, 3 or 4 times but some are not duplicated. I'm trying to write a formula that will look at the tests that match and then combine the rows so that if 'Yes' is present in any of the Result columns then a 'Yes' is returned in a single line.

    For example, duplicate tests that I need to know if there is a Yes present in any relevant row for the same test:

    Current state

    A B C D E
    Test name Result Result Result Result
    Test 1 Yes No No Yes
    Test 1 No Yes No Yes
    Test 1 Yes No No Yes
    Test 2 No No Yes No
    Test 2 Yes No No No
    Test 3 Yes Yes No No

    Desired state

    Now the tests have been combined and if there is a Yes in any column then a Yes is returned in the combined test row:

    A B C D E
    Test name Result Result Result Result
    Test 1 Yes Yes No Yes
    Test 2 Yes No Yes No
    Test 3 Yes Yes No No
    Last edited by jamesforpm; 07-24-2014 at 04:56 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    07-07-2014
    Location
    Washington DC
    MS-Off Ver
    2007
    Posts
    1,047

    Re: Impossible Excel problem!

    The "=ARLOOKUP" would make this quite easy if you changed the column headings to "Result1", "Result2", etc. Then you could do a quick check to see if there are any "Yes" results. May be worth adding the code in VBA to save yourself some time.

    http://www.excelguide.eu/120/810/120/EN_ARLookUp.html

  3. #3
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Combine duplicate rows with conditions

    hoyasaxa215
    Your post does not comply with Rule 7 of our Forum RULES. Please do not ignore requests by Administrators, Moderators and senior forum members regarding forum rules.

    If you are unclear about the request or instruction then send a private message to them asking for clarification. Do not post a reply in a thread where a moderator has requested an action that has not yet been complied with e.g Title change or Code tags...etc

    I'll restore your answer when OP change his title

  4. #4
    Registered User
    Join Date
    07-24-2014
    Location
    Los Angeles
    MS-Off Ver
    Mac 2011 and PC 2010
    Posts
    6

    Re: Combine duplicate rows with conditions

    I renamed the thread.

  5. #5
    Registered User
    Join Date
    07-24-2014
    Location
    Los Angeles
    MS-Off Ver
    Mac 2011 and PC 2010
    Posts
    6

    Re: Combine duplicate rows with conditions

    If I were to transfer this to the Commercial Services thread and uploaded my entire spreadsheet, how many points would be required to do what I need?

  6. #6
    Valued Forum Contributor
    Join Date
    07-07-2014
    Location
    Washington DC
    MS-Off Ver
    2007
    Posts
    1,047

    Re: Combine duplicate rows with conditions

    I can take care of it in 5 minutes for you in the morning. If you can wait that long.

  7. #7
    Registered User
    Join Date
    07-24-2014
    Location
    Los Angeles
    MS-Off Ver
    Mac 2011 and PC 2010
    Posts
    6

    Re: Combine duplicate rows with conditions

    That would be incredible! Tomorrow morning would be fine. I'll send you a private message.

  8. #8
    Registered User
    Join Date
    07-24-2014
    Location
    Los Angeles
    MS-Off Ver
    Mac 2011 and PC 2010
    Posts
    6

    Re: Combine duplicate rows with conditions

    Posting another message so I meet the 5 post minimum to private message

  9. #9
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Combine duplicate rows with conditions

    jamesforpm,

    if you gonna communicate over private messages then other people cannot involve in solving your problem because they don't see what you talking about.
    Point of forum is that everybody can help you.

    Check this result (and answer below if need changes, not on PM.

    On sheet1 you have data
    on sheet2 is result
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    07-24-2014
    Location
    Los Angeles
    MS-Off Ver
    Mac 2011 and PC 2010
    Posts
    6

    Re: Combine duplicate rows with conditions

    Good point, zbor. What you've done seems to be exactly what I need help with. Attached is the file I'm working with - if you could help me, I would be extremely thankful!
    Attached Files Attached Files

  11. #11
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Combine duplicate rows with conditions

    Formula work too. You only need to change sheet names:
    Attached Files Attached Files

  12. #12
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Combine duplicate rows with conditions

    You can also take the help of a VBA code to achieve this. If you are open to a VBA solution also, please find the attached sheet to see if this works as per your requirement. Click on the Green Button on sheet2 to get the desired output.
    Attached Files Attached Files
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Impossible Conditional Formatting Problem?
    By noworriespete in forum Excel General
    Replies: 4
    Last Post: 01-08-2013, 04:47 PM
  2. [SOLVED] Problem with LOOKUP function - impossible?
    By oscarbunny in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-30-2012, 07:44 AM
  3. Replies: 0
    Last Post: 02-20-2012, 02:27 PM
  4. [SOLVED] EXCEL MISSION IMPOSSIBLE ?
    By hkappleorange in forum Excel General
    Replies: 3
    Last Post: 09-13-2005, 04:05 PM
  5. EXCEL MISSION IMPOSSIBLE ?
    By hkappleorange in forum Excel General
    Replies: 11
    Last Post: 09-12-2005, 03:05 PM

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