[deaexcelpadawan]

Hi everyone

I hope you are all well! I have an excel problem and it would be much appreciated if someone has the time to take a look...

In column A I have product names, in B I have product categories and in C:H the product categories from column B are titles and the name of the subcategory is written in the cell.

Because there are mutlipule sub categories that a product can belong to the report produces mirrored lines with one sub category on each line.

What I need to do is combine all of these subcategory entries into one cell separated by a comer (,)

Any help you could give on this would be great, thank you v.much for your time!

Pete

P.S if you have a way to get rid of the copied lines after that it would make life a lot easier tooo. Thanks.

[ScottGem]

Lookup the Concatenate function.

[JBeaucaire]

Concatenate is the correct function. But your description above doesn't match your posted sample file.

If you provide an exact representation of what you want one of the final cells to look like, we can be more helpful.

To concatenate two cells together, use =A1&B1.

To add missing spaces or commas, use another concatenate: =A1&", "&B1

[deaexcelpadawan]

Hi Thank you very much

The format would be name, name, name

Is is possible to combine this function with a lookup or a match so the formula could choose to only collate info (from the same column) from the same product using the product codes in column A.

Hope this makes sense... I'm abit tired this morning *he says looking at the clock and its nearly the afternoon! Lol

[JBeaucaire]

You'll have to create some examples of what you're talking about. Using the sheet you've already posted, give us some examples of what you would want shown as a result and the logic behind your thinking.

We'll see if we can then come up with a concatenate formula that can create the same results.

[ScottGem]

Is this all about the same spreadsheet you previously posted? I agree with JB you need to give us more info or better examples.

[deaexcelpadawan]

Hi

=CONCATENATE(C5&"+"&C6&"+"&C7&"+"&C8)

DressingTables+HomeOffice+50sAngledRange+MirroredF urniture

I have filled in the spreadsheet with the formula above which produces the line of text below (Thank you guys). This is the format that I need it to look like.

In the new spreadsheet I have created an extra column (D) to collate the info from C.
I will have to repeated this for all the categories From C To O but for this example I have only made an extra column for Lighting in column E, and its duplicate column F.

So here's the tricky part;

I need to match D1=Furniture with the info in B.

Column A is the product ID this will be unique to each set of entries that need collating.


SO

I need to match
Column A - GHG9236 which is A5:A8
Column B must = D1 (Furniture) - which is a few of them in this example.
And in Column C I need to copy the rows that match the criteria above in one cell as written below and in the example.
DressingTables+HomeOffice+50sAngledRange+MirroredF urniture


Thanks for you Time :)

[ScottGem]

And why do you have to do this? What are you trying to accomplish?

[deaexcelpadawan]

This is part of a huge spreadsheet that communicates and updates our website so, I'm trying to save myself time by coming up with a formula so I don't have to go through 4000 rows and micro mange the data
:)

[JBeaucaire]

I appreciate all the worded explanation, but that's where I get lost. All I want is for you to provide a few rows showing ANSWER (no formula used, just manually figure it out and put the answers in) and let us look at it.

It's usually easier to grasp your process if we can see the goal already and then try to make a formula that does that.

Can you just present a few rows showing what you're end goal looks like so we can analyze it?

[ScottGem]

Frankly, I don't think Excel is the correct tool for you. I think you may need to use a database application like Access. To be able to massage the data the way you want.

[deaexcelpadawan]

Hi

This is an example of want I was after.

I've highlighted this cells that I'm trying to collate

Cheers

[JBeaucaire]

These are simple Concatenate formulas. No problem with anything so far.