String concat based off empty cells below and across

Question

Data Table with what I have on left, and what I want to achieve on the right:

I need to be able to concat two cells together if the next cells are empty.

Every cell in column A will be full, but if B2 is empty, I need to put together A1+A2 (removing the 12345 of A2 in the concat.

Note: If B2 is empty, then C2 and D2 will be empty as well.

This also needs to be dynamic, as not only B2 could be empty, but also B3 and B4 which will require the associated A cells to concat together as well.

I'm so sorry, I don't really know where to begin with this. I have attempted If statements and offset function but haven't been able to fully get my understanding around it all.

Answer 1

This can also be accomplished using Power Query, available in Windows Excel 2010+ and Microsoft 365 (Windows or Mac)

To use Power Query

• Select some cell in your Data Table
• Data => Get&Transform => from Table/Range
• When the PQ Editor opens: Home => Advanced Editor
• Make note of the Table Name in Line 2
• Paste the M Code below in place of what you see
• Change the Table name in line 2 back to what was generated originally.
• Read the comments and explore the Applied Steps to understand the algorithm

Data

M Code

let

//Change next line depending on your actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table27"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,
        {{"Name", type text}, {"Nozzle", type text}, {"Time", type text}, {"Material", type text}}),
    
//Fill in the blanks to create groups
    #"Filled Down" = Table.FillDown(#"Changed Type",{"Nozzle", "Time", "Material"}),

//Group by Nozzle-Time-Material
//  and extract the combined "Name"
    #"Grouped Rows" = Table.Group(#"Filled Down", {"Nozzle", "Time", "Material"}, {
        {"Name", (t)=>     let 
                prefix = Text.Trim(Text.BeforeDelimiter(t[Name]{0},"-")),
                suffix = List.Transform(t[Name], each Text.Trim(Text.AfterDelimiter(_,"-")))
                    in prefix & " - " & Text.Combine(suffix,"+"),type text}},
        GroupKind.Local),

//Add custom to concatenate
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Name Nozzle Time Material", 
        each [Name] & " (" & Text.Combine({[Nozzle],[Time],[Material]},"-") & ")", type text),

//Remove now unneeded columns
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Nozzle", "Time", "Material", "Name"})
in
    #"Removed Columns"

Results

Answer 2

Assuming there is no Excel Constraints as per the tags posted, then one could use the following formula as well.

---

• Formula used in cell E1

=LET(
     _join, B2:B8&"-"&C2:C8&"-"&D2:D8,
     _fill, SCAN("--",_join, LAMBDA(x,y,IF(y="--",x,y))),
     _dupsjoin, MAP(_fill, LAMBDA(z, LET(a, FILTER(A2:A8,z=_fill),
      UNIQUE(TEXTBEFORE(a," "))&" - "&TEXTJOIN("+",,TEXTAFTER(a,"- "))))),
     _output, IF(_join<>"--",_dupsjoin&" ("&_fill&")",""),
     VSTACK(A1&" ("&TEXTJOIN("-",,B1:D1)&")", _output))

---

• _join --> Concat the ranges using ampersand --> &
• _fill --> Using SCAN() function to fill those `--" by the above values.
• _dupsjoin --> Using MAP() function to iterate through each cell in range A2:A8 to concat the duplicate names before the "-" based on _fill returns match.
• _output --> Checks to see if the _join is not equal to -- then return _dupsjoin concatenated with the corresponding _fill else return blank.
• Lastly, VSTACK() is used to stack a fancy header with the _output one can exclude it if needs.
• An important caveat to remember, that since TEXTJOIN() is used in the above solution, should also be aware that it may return the #VALUE! error if the resulting string exceeds 32767 characters (cell limit). However, if its less data and within the limits that one can use it successfully.

---

Answer 3

Another formula alternative:

=MAP(
     DROP(REDUCE("",A2:A8,
          LAMBDA(x,y,
                 IF(ISNA(XMATCH(y,
                                FILTER(A2:A8,B2:B8<>""))),
                    VSTACK(DROP(x,-1),
                           TAKE(x,-1)&" +"&RIGHT(y,3)),
                    VSTACK(x,
                           y)))),
          1),
LAMBDA(z,
       z&" ("&XLOOKUP(LEFT(z,10),
                      A2:A8,
                      B2:B8&"-"&C2:C8&"-"&D2:D8)&")"))

Which uses REDUCE to iterate through A2:A8. If the value is found in A2:A8 filtering out the values where B2:B8 are blanks, it returns the value of A2:A8 on top of the previous result(s). If it is not found it takes the last return and adds the right 3 characters and a +.

The reduce result is mapped to add the nozzle/time/material. This is a lookup of the left 10 characters of the mapped reduce.