+ Reply to Thread
Results 1 to 9 of 9

How to lookup and combine multiple rows (different colums) when duplicate values exist

  1. #1
    Registered User
    Join Date
    04-04-2012
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2010
    Posts
    19

    How to lookup and combine multiple rows (different colums) when duplicate values exist

    Hi,

    Hoping someone might be able to help me with this. Not sure if it is possible.

    I have a sheet which shows which consultant is working on a given project in a given week.
    As the sheet is organised by project, a consultants name can appear multiple times if they finish on one and move on to another.

    On a seperate sheet i am trying to show the availability of the consultants by drawing on the data in the first sheet.
    Unfortunately vlookup only gives me the first value and index requires unique values.

    Attached is an example spreadsheet. I am trying to combine the data from sheet 1 into sheet 2.

    Example.xlsx

    Any help would be greatly appreciated.

  2. #2
    Forum Expert
    Join Date
    12-15-2009
    Location
    San Francisco
    MS-Off Ver
    Microsoft Office 365
    Posts
    3,163

    Re: How to lookup and combine multiple rows (different colums) when duplicate values exist

    Ignore this post. Formula failed.
    Last edited by JieJenn; 04-04-2012 at 07:45 PM. Reason: Fixed formula

  3. #3
    Valued Forum Contributor ron2k_1's Avatar
    Join Date
    09-30-2009
    Location
    Not the USA
    MS-Off Ver
    Excel 2003, 2007
    Posts
    606

    Re: How to lookup and combine multiple rows (different colums) when duplicate values exist

    I don't quite get what your objective is. Can you show us on Sheet2 what your objective is?
    Ron
    Knowledge is knowing that a tomato and pepper are fruits. Wisdom is knowing whether to put these in a fruit salad

    Kindly

    [1] Use code tags. Place "[code]" before the first line of code and "[/code"]" after the last line of code. Exclude quotation marks
    [2] Mark your post [SOLVED] if it has been answered satisfactorily by editing your original post in advanced mode.
    [3] Thank (using the little scale) those that provided useful help; its nice and its very well appreciated

  4. #4
    Registered User
    Join Date
    04-04-2012
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: How to lookup and combine multiple rows (different colums) when duplicate values exist

    Hi Ron,

    Sheet two will combine the values from sheet one into a single row. Attached.

    Thanks for having a look at this.Attachment 148871Example.xlsxExample.xlsx

    Couldn't figure out which file it was.

  5. #5
    Registered User
    Join Date
    04-04-2012
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: How to lookup and combine multiple rows (different colums) when duplicate values exist

    its the first Example.xlsx link

  6. #6
    Valued Forum Contributor ron2k_1's Avatar
    Join Date
    09-30-2009
    Location
    Not the USA
    MS-Off Ver
    Excel 2003, 2007
    Posts
    606

    Re: How to lookup and combine multiple rows (different colums) when duplicate values exist

    This gives me your same results. Assuming that you have the same column dates on both sheets. Enter on B2 and drag down/right as needed:
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    04-04-2012
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: How to lookup and combine multiple rows (different colums) when duplicate values exist

    Awesome it does work. One question though, if my actual sheet one has more letters than just "Y", is there a way i can include these letters in the formula as well?

  8. #8
    Valued Forum Contributor ron2k_1's Avatar
    Join Date
    09-30-2009
    Location
    Not the USA
    MS-Off Ver
    Excel 2003, 2007
    Posts
    606

    Re: How to lookup and combine multiple rows (different colums) when duplicate values exist

    You can change the formula to give you a result as long as the cells in the Schedule/Calendar section are NOT blank:
    Please Login or Register  to view this content.
    However, note that if you notate that being free could mean an "X" or "-" then, the formula will interpret that as NOT being free and will return a "Y" on your Sheet2

    Ron

  9. #9
    Registered User
    Join Date
    04-04-2012
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: How to lookup and combine multiple rows (different colums) when duplicate values exist

    Hi Ron,

    Thanks for your help. Apologies but i am completely illiterate when it comes to this.
    I get the logic below, but how would i expand the formula to get it to display additional letters.
    E.g. the first one you gave me converted a "Y" to a "B", what if i wanted to keep this but also wanted to formula to pick up an L as well as the "Y"?

    Sorry for being a pain!

    Regards,

    Sandy

+ 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