+ Reply to Thread
Results 1 to 3 of 3

Concatenate by condition / variable cells

  1. #1
    Registered User
    Join Date
    11-13-2004
    Posts
    36

    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
    06-09-2018
    Location
    Virginia
    MS-Off Ver
    Excel 2013
    Posts
    26

    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
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    XP - 2007 & Win10 - Office 365
    Posts
    8,813

    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.
    Dave

+ 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