I'd like to produce a "subtotal" of a table where the subtotal function is string concat instead of some numeric function (count, sum, average).

For example, consider this table:

enter image description here

I want the following result:

enter image description here

To me, this seems like "concat" (or "join") lifted over subtotaling. But I don't think Excel really understands functional programming (no matter what some people have claimed). So how can I do this (especially, how can I do this taking advantage of the Subtotal menu item that inserts all the subtotaling formulas automatically, even if I have to subsequently edit the formulas to get what I want to work).

And, in fact, Excel is very powerful with oodles of features. So can I achieve this in some other way besides subtotaling? (I would prefer non-scripting solutions.)

And, also, Excel isn't the only tool in the toolbox. Suppose I had this table in a CSV file. Is there a way to do this with standard Unix utilities (e.g., cut? Presumably awk could handle it, how would that look)?

  • What version of Excel? – ejbytes Oct 16 '16 at 21:04
  • Seems that you are after a result, but not really using Excel for any particular reason, correct? – ejbytes Oct 16 '16 at 21:31
  • SUBTOTAL offers 11 built-in functions, all mathematical. Since Excel 2010, I believe, AGGREGATE is similar, offering 19 functions, but again, all mathematical. Even pivot table functions are mathematical. I'm not aware of a general function of a similar type that allows you to specify string operations as a parameter setting. This can be done in Excel (or other applications), but not in the manner you describe. – fixer1234 Oct 16 '16 at 21:38
  • @ejbytes Version is ... Office 365? 16.0.7167? And yes, I'm not using Excel for any particular reason in this particular case (the original data was sitting around in a text file) but I have wanted to do this sort of thing in Excel in the past. It's tough in SQL too, so I've brought the data into Excel ... and still got stuck. This time I asked a question about it.... – davidbak Oct 16 '16 at 23:10
  • Is Col1 always sorted? – Atzmon Oct 16 '16 at 23:18

This may not be the most elegant solution but try this, based on the fact that Col1 is guaranteed to be sorted:

  1. Add a column (C) to hold the cumulative concatenations of Col2 values. The formula for cell C2 is =IF(A2=A1, CONCATENATE(C1, ",", B2), B2) and you can autofill this for the rest of the cells.

  2. Add a column (D) to flag the rows holding the maximum (longest) concatenation, meaning the last row of each value in Col1. The formula for cell D2 is =IF(A2<>A3, TRUE, FALSE) and you can autfill the rest of the cells.

    The table will look like this:

    enter image description here

  3. Filter the table by TRUE values in column D:

    enter image description here

  4. You can now hide columns B and D:

    enter image description here

| improve this answer | |
  • Clever and easy to set up! Self-maintaining as the table changes too. – davidbak Oct 17 '16 at 2:32
  • Consider marking this as the answer. Thanks. – Atzmon Oct 17 '16 at 7:40

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.