+ Reply to Thread
Results 1 to 10 of 10

Pull data from multiple cells into one cell based on criteria?

  1. #1
    Forum Contributor
    Join Date
    08-06-2012
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    119

    Talking Pull data from multiple cells into one cell based on criteria?

    I need a formula to pull data from multiple cells into a single cell based on common criteria in each row.

    For example: I have a stock number for every color each of my products comes in. They all have the same name however. I am trying to make a catalog in publisher by merging data from an excel sheet. So I need a formula that will match the product name and pull the colors and stock numbers for all of the rows with that specific name into one cell.
    Last edited by chaddug; 08-06-2012 at 07:52 PM. Reason: Solved

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Pull data from multiple cells into one cell based on criteria?

    Hi
    Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

    Show a before and after situation and if it's not obvious how you have arrived at your results some explanatory notes as well.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Contributor
    Join Date
    08-06-2012
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    119

    Re: Pull data from multiple cells into one cell based on criteria?

    Ok, here is a sample of my data. As you can see there are many different color sku's that all have the same description. I need a single cell at the end of each description that contains all of the colors for that name.EXAMPLE.xlsx

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Pull data from multiple cells into one cell based on criteria?

    Hi,

    See attached for a simple solution which uses a couple of helper columns
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    08-06-2012
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    119

    Re: Pull data from multiple cells into one cell based on criteria?

    Thanks, thats definitely helpful, is there anyway to make it so that different colors include the sku and are separated by a line break not a comma?

  6. #6
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Pull data from multiple cells into one cell based on criteria?

    Hi,

    C2:
    =IF(A2=A3,"",IF(A2=A1,D1&CHAR(10)&B2,B2))
    D2:
    =IF(A2=A1,D1&CHAR(10)&B2,B2)

    both copied down and columns C & D formatted to Wrap Text

  7. #7
    Forum Contributor
    Join Date
    08-06-2012
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    119

    Re: Pull data from multiple cells into one cell based on criteria?

    Thanks so much! Thats almost perfect, the only thing is, and im not even sure if this is possible, but what im looking for is like a character or character code i can put in the formula that creates a line break after each sku number, because formatting cells to wrap text only breaks the line based on the size of the cell and unfortunately each color is a different number of characters, so it wraps some correctly and others in-between the color and sku.

  8. #8
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Pull data from multiple cells into one cell based on criteria?

    Hi,

    That's what the reference to Char(10) is.
    This is the character that forces a line break.

    It will only wrap between the colour and the sku if the column is too narrow since you are then limiting the characters that can be displayed on one line. Widen column C sufficiently so that you can see the longest Colour/sku combination.

  9. #9
    Forum Contributor
    Join Date
    08-06-2012
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    119

    Re: Pull data from multiple cells into one cell based on criteria?

    Oh, okay, Its likely because Im on a Macbook that its not working. I am going to be making the catalog on a XP pc running 2007, but I am working on my laptop at the moment running Office 2011 MAC, and the Char(10) isn't working.

    Thanks so much for your help, again. Made my life about a million times easier.


    *** Solved on a mac for reasons unknown to me Char(10) has to be Char(13).
    THANKS AGAIN!
    Last edited by chaddug; 08-06-2012 at 07:51 PM.

  10. #10
    Registered User
    Join Date
    07-12-2010
    Location
    West Sussex, England
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Pull data from multiple cells into one cell based on criteria?

    Im trying to achieve something similar but mines using dates.

    Sheet List will have dates and data entry, sheet two will have a list of dates with all data from that date in one cell. There will sometimes be no entries for a date and sometimes 5 or 6. Can this be done?

    See attached example

    exampledate.xlsx

+ 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