Search code examples
excelexcel-formulaconcatenationworksheet-functionsubstitution

Concatenate by group excluding group member from same row

Is it possible to create the 'cluster' column in the image? Needing to concatenate the 'unit' values for all common 'overall' values, but not include itself as ones of the values being concatenated. Can I please get some help creating the 'cluster' column?

Each 'cluster' has all Units from the same Overall other than the Unit for the row for the result:

Overall     Unit       Cluster
Overall1    UnitA1     {UnitA2, UnitA3}
Overall1    UnitA2     {UnitA1, UnitA3}
Overall1    UnitA3     {UnitA1, UnitA2}
Overall2    UnitA4     {UnitA5}
Overall2    UnitA5     {UnitA4}
Overall3    UnitB62    {UnitB63, UnitB64, UnitB65}
Overall3    UnitB63    {UnitB62, UnitB64, UnitB65}
Overall3    UnitB64    {UnitB62, UnitB63, UnitB65}
Overall3    UnitB65    {UnitB62, UnitB63, UnitB64}

Thinking it could be a two step process, concatenate then maybe a search and replace?

Solution

  • Can be achieved with formulae, but rather a rigmarole. Assumes Sorted as indicated and Overall is in A1:

    1. In C1 Cluster, in C2 (labels assumed in Row1): 

      =IF(A1=A2,C1&", "&B2,"{"&B2)

    2. In D2: 

      =A1<>A2

    3. Copy both formulae down to suit.

    4. Select all, Copy, Paste Special, Values.

    5. Select all, DATA > Outline - Subtotal, At each change in: Overall, Use function Count, Add subtotal to: (ColumnD), Replace current subtotals, Summary below data, OK.

    6. Filter and for ColumnA, Text Filters, Contains..., co, OK.

    7. In first row visible below header (assumed to be Row5) in ColumnC enter: 

      =C4

      and copy down to suit.

    8. Take off Filter, select all, Copy, Paste Special, Values.

    9. Select ColumnA, HOME > Editing - Find & Select, Replace..., Find what: Count, Replace All. (There is a space before the C of Count.)

    10. Select all, DATA > Outline, Subtotal, Remove All. Delete Grand row.

    11. Filter to select all but TRUE and FALSE in ColumnD and insert 1 in first blank cell of ColumnB and copy down to suit. Take off Filter.

    12. Sort by ColumnA Values, A to Z, then by Column B Values, A to Z.

    13. Filter ColumnD to select TRUE and FALSE and delete contents of ColumnC for the selected rows (leave label).

    14. Take off Filter and select ColumnC. HOME > Editing - Find & Select, Go To Special..., Blanks, OK.

    15. =, UP, Ctrl+Enter.

    16. Select all, Copy, Paste Special, Values.

    17. Select all but TRUE and FALSE in ColumnD and delete selected rows.

    18. In E2 copied down to suit: 

      =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2,"{"&B2&", ","{"),", "&B2,""),", "&B2,"")&"}"

    19. Select all, Copy, Paste Special, Values, delete Columns C:D, enter Cluster in C1.

    .