This is in reference to the formula I need for cell F2 in the below image.
F2 shows the result I WANT:

spreadsheet with sample data

I NEED: The Concat Formula to concatenate the data in Column B for each variant of whatever is in Column A. So if we know there are X versions of the item (based on function in C2), then the concatenate needs to step up X −1 times to draw the text string from Column A.

Can't figure out the best way to do this! I'm also OK with making new functions if they need to be referenced (like aggregate CONCATs in F, because I can then just write an IF statement to look for "BASE"...)

  • I know the number in C2: it's the number of times the text in column A shows up
  • I know the number in E2: it's the row number
  • I know the result of D: if it's the first time the text in A shows up, it's "Base" otherwise it's "Variant"
  • I know the text in Column A is always sorted
  • There's no need to have a column just to hold the row number; you can always get that with ROW(). – Scott Oct 11 '18 at 1:56
  • Thanks Scott; I looked at Freeman’s answer in that question you linked, that won’t work... but I will have to try your answer when I am at the spreadsheet. I’m aware about ROW, I just put it in there as a reference in case someone thought using the differential of ROW #s would help.. – mehmetic Oct 11 '18 at 7:25
  • 1
    Do you have Office 365 or 2016 or 2019? If yes, you can use TEXTJOIN Built-in function. – patkim Oct 11 '18 at 8:32

Adapting my answer to Conditional concatenate cell content across rows, enter the following formulas:

  • F2=IF(AND(A1<>A2,A2<>""), G2, "")
  • G2=IF(A2<>A3, B2, B2 & ", " & G3)

(assuming that your data begin in Row 2, as shown in the question).  If you don’t want to use Column G, you can use any available column (e.g., Z or AG) and change the references accordingly.  Drag/fill down to cover all your data. 

The formula in the second column (I’ll assume that it is Column G) builds the comma-separated list of sizes for the current thing in Column A from the bottom up.  More precisely, cell Gn contains the list for Rows n and below.  (It might help you to take a peek at the illustration now.)  It says,

  • If the next row has a different value in Column A than the current row (A2<>A3), then this is the last row for that value, and so the list for this row and below is just the value for this row.
  • Otherwise, this is not the last row for that value, and the row below this one has the list for that row and below.  So I want to take the value from this row and “prepend” it to the list from the row below.

Then the formula in Column F displays the complete list for a thing on the first row for that thing, and blank elsewhere.  Also display blank if the Column A value in this row is blank.

illustration of spreadsheet

You may want to hide Column G once you get this working.

  • Wow. This is a nice solution. Thanks Scott. It works like a charm... You've also added IF( <> ) to my arsenal - thanks! – mehmetic Oct 11 '18 at 18:24

Use an extra column.. then hide it. ( :

F2  ----> =IF(A2="","",IF(A1=A2,"",G3&", "&B2))
G2  ----> =IF(A2="","",IF(A2=A3,A2&", "&G3,B2))

and drag downwards... please share if it works(or doesn't) ( :

  • 1
    Hi @p._phidot_ , yes it works with ONE small change..<br> =IF(A2="","",IF(A2=A3,**B2**&", "&G3,B2)) – mehmetic Oct 11 '18 at 18:28
  • All praise to god.. glad it solves. ( : – p._phidot_ Oct 11 '18 at 19:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.