# Concatenate by condition / variable cells

1. ## Concatenate by condition / variable cells

Hi again forum
Grateful of some advice on a formula that has me stumped
I have data (from a large and changeable source) organised in a pivot
It looks like the table below

Pine 2.75M £50.50
Cedar 2.70M £45.00
Cedar 3.50M £35.00
Oak 2.75M £25.00
Ash 2.25M £75.00
Ash 5.00M £80.00
Ash 3.75M £90.00
Beech 4.25M £55.00
Maple 1.50M £45.00

The number of rows for each wood varies depending on entries in data
I need to be able to concatenate into single rows by wood types
So the multiple rows of M and £ data are concatenated into a table similar to shown below

Pine 2.75M,£50.00
Cedar 2.70M,£45.00,3.50M,£35.00
Oak 2.75M,£25.00
Ash 2.25M,£75.00,5.00M,£80.00,3.75M,£90.00
Beech 4.25M,£55.00
Maple 1.50M,£45.00

Grateful if someone could suggest a formula please

2. ## Re: Concatenate by condition / variable cells

Here's a starting point worksheet for you -- please have a look. But on reading your post a second time, it looks like the =sumif() function that I used is slightly different than what you have shown. Does my example do the trick for you? As you can see, I summed the volume per type of wood (ex: "2.5M" + "2.5M" = "5M"), and included it in the same line that sums the monetary value for the given type of wood.

If you need it exactly as you showed it (I'm assuming you do), then my first thought about how to concatenate using an =if() function might be totally wrong. I'd say if you can do it the way I showed, then go for it. Otherwise, I can return to this on Friday.

3. ## Re: Concatenate by condition / variable cells

@ Digory,

Please update your profile to show what version(s) of Excel you are using this for. Contributors tailor solutions with that in mind.

Additionally there are new functions that will do what you want. Otherwise I believe you are going to need VBA to do this.

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