+ Reply to Thread
Results 1 to 3 of 3

Concatenate by condition / variable cells

  1. #1
    Registered User
    Join Date

    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

    Thanks in advance D

  2. #2
    Registered User
    Join Date
    MS-Off Ver
    Excel 2013

    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.

    Please confirm.
    Attached Files Attached Files
    Last edited by maibacher; 06-14-2018 at 08:53 PM.

  3. #3
    Forum Guru
    Join Date
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    XP - 2007 & Win10 - Office 365

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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