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

1. ## 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.

2. ## 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.

3. ## 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. ## 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

5. ## 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. ## 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. ## 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. ## 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. ## 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.

*** Solved on a mac for reasons unknown to me Char(10) has to be Char(13).
THANKS AGAIN!

10. ## 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

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

#### 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