+ 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
    28

    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 Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    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)

Similar Threads

  1. [SOLVED] Need to concatenate multiple cells if a condition is met
    By Gssoc777 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-25-2015, 09:42 AM
  2. Concatenate a variable # of cells based on 1 criteria into a cell then autofit the text
    By DrummerAndy in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-17-2015, 12:05 PM
  3. Concatenate the cells on condition
    By kumark in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-23-2014, 02:35 AM
  4. Concatenate cells in column range if condition is met
    By AlexVen in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-02-2013, 03:30 AM
  5. [SOLVED] How to Concatenate 3 or more cells with variable inputs
    By pblobe in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-05-2013, 03:29 AM
  6. Concatenate Cells Based on a Condition in Next Row
    By brendanec in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-06-2012, 03:34 PM
  7. concatenate 3 cells based on condition
    By Pasha81 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-02-2009, 09:55 AM

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