Concatenate text strings

[Hans Knudsen]

I have what to me is a rather complicated concatenation problem. I have tried to describe the problem in a text box in sheet VP_2 of the attached file. Please tell me if I haven't described the problem sufficiently.
I would prefer a formula solution but is open to whatever.

 

[Peter Bartholomew]

Hello Hans, good to hear from you.
So far, I have a formula

= BYROW(data, Encodeλ)

that returns the entire table as a column. I guess a little more explanation would be in order, which I will get back to soon!
I have not, as yet, studied the separators you use; I hope it is a consistent pattern.

 

[Peter Bartholomew]

Seems I was right to hold off posting the formula; sorting out the use of separators was something of a nightmare!

Encodeλ
= LAMBDA(values,
     LET(
        names, TEXTBEFORE(TEXTAFTER(headers, "["), "]"),
        omit1, HSTACK("", DROP(names,,1)),
        seps,  {"+","+++'",":",":","+++","+"},
        items, CONCATENATE(omit1, seps, values),
        TEXTJOIN("'", 0, items)
     )
  );

Picking out the text between brackets is as per your formula. The next line replaces the "CST" form the first header by a blank element.
I then define an array of separators because there is little consistency in those. CONCATENATE works element by element across the row arrays to get attribute/value pairs. TEXTJOIN combines the pairs with an apostrophe "'" separator.

The Lambda function could be used row by row using a relative row reference, but I chose to use it within BYROW to return the result as a single array.

 

[Hans Knudsen]

Thank you very much Peter, you the undisputed master of New Excel.
Please give me some time to study your solution and I will try my best to say something reasonably bright.

 

[Hans Knudsen]

Please see my comment in sheet VP_2

 

[Peter Bartholomew]

I only addressed the problem as expressed on sheet VP_1 because that was a statement of a problem I could understand.

 

[p45cal]

There have been intervening responses since I started to look at this which I haven't yet looked at which means there may be mistakes in my offering below.
This is very much work in progress and is in 2 parts.
1. A shortened version of your sheet VP_1 J2 formula in K2. It seems to produce the same results.
2. On sheet VP_2, beneath your B22 formulae, equivalent formulae starting at cell B29 copied down. The aim of these is to incorporate all columns C:U which may have values in them.
In cells B36 and down I've compared equivalent formulae and most are not equivalent (FALSE) but it's because of what happens at the end of your results; some of your strings end with a single quote, some don't and some have FALSE near the end of them. I've added data into row 7 of that sheet to test how it handles more numbers than 2 or 3. You tell me if the results are right. If the final single quote at the end of each string is needed that's easy to tweak.

Ultimately I'd be looking at eliminating the need for column J (or K) in sheet VP_1.

Separately, would a Power Query solution be OK?

 

[Hans Knudsen]

I only addressed the problem as expressed on sheet VP_1 because that was a statement of a problem I could understand.

OK Peter - thanks anyway.

 

[Hans Knudsen]

@p45cal Thank you so much.
Your solution seems to be exactly what I am after both part A and part B
As I tried to describe in my question the single quote at the end (and 'FALSE') shouldn't be there (fault somewhere in my formula I guess) so your results in VP_2!B29:B34 are exactly as I would expect.
A Power Query solution would be fine, but I think your above formula solution works perfectly well, but please give me some more time to test it.
I am really grateful for your help.

 

[p45cal]

Is it ever even remotely possible that the text within the square brackets is the same in more than one column header?

 

[Hans Knudsen]

No this is not possible.

 

[Peter Bartholomew]

Hans. Sorry I appeared to give-up, but it took me a while to follow your second sheet. Pascals solution helped but even then, I am not very good at following direct cell referencing. There was sufficient difference in the two steps of encoding lists into strings that I chose to write a second Lambda function, giving

= LET(
     detailList, BYROW(values, Encodeλ),
     BYROW(masterIdx, Combineλ(detailList,1))
  )  & "'"

where

Combineλ = LAMBDA(detailLst,[lf?],
    LAMBDA(indices,
        LET(
            n,       COUNT(indices),
            lf,      IF(lf?, CHAR(10), ""),
            tags,    TAKE(masterHdr, , n),
            strings, INDEX(detailLst, TAKE(indices, , n)),
            items,   CONCATENATE(tags, strings),
            TEXTJOIN("'" & lf, 0, items)
        )
    )
);

Encodeλ = LAMBDA(values,
    LET(
        names, TEXTBEFORE(TEXTAFTER(headers, "["), "]"),
        omit1, HSTACK("", DROP(names, , 1)),
        seps,  {"+", "+++'", ":", ":", "+++", "+"},
        items, CONCATENATE(omit1, seps, values),
        TEXTJOIN("'", 0, items)
    )
);

masterHdr = DROP(MasterTbl[#Headers],,2);

masterIdx = DROP(MasterTbl,,2);

headers =   DROP(DetailTbl[#Headers], , 1);

values =    DROP(DetailTbl, , 1);

 

[Hans Knudsen]

Thank you very much Peter.
The Final Strings are correct apart from the single quote at the end of each string which shouldn't be there, that is the strings should end with ADR-2, ADR-3 and so on.
Immediately after having read your solution, I thought that it all is way over my head. I wish I could handle LET and LAMBDA and other new Excel functions with such easiness as you, but I am not there - far from. I will give it a try, but I must admit that to me p45cal's solution is easier to understand.

 

[Peter Bartholomew]

If you have questions, by all means, ask for an explanation. There are a number of forum members who may be interested.
As for the final single quote, I added that as an afterthought because I thought it was required All I need do is delete the final

& "'"

 

[p45cal]

A Power Query version in the attached. Keep the tables' names Table6 and Table9, right click the green table at cell X1 of the VP_2 sheet and choose Refresh (as you would a Pivot) should you change the data in either of those source tables.

 

[Peter Bartholomew]

Hi Hans
You enquired off-line
In which order should I try to understand your code in message #12 of yesterday
Encodeλ
Combineλ
LET??

Keeping the discussion on-line for the moment, the starting point is the LET within Encodeλ. The Lambda function just serves to reference a single row of the first table. A useful trick with LET is to introduce a variable name for the formula that returns the result and then insert a comma at the end of the line and repeat the variable name itself on the next line (Alt/Enter) to return the result. That allows you to check the content of any step of the calculation by placing another variable at the end of the LET statement. I describe the Encodeλ function in post #3. The input parameter should reference a single row of the first Table.

The worksheet formula at the start of post #12 Uses Encodeλ to generate a column of concatenated strings which it then passes it as a parameter to the second Lambda function, Combineλ. Combineλ has the same basic structure as Encodeλ in that it uses BYROW to process an array row by row. A major difference is that it used what I would consider to be an 'advanced' technique. The function is headed by two Lambda functions, which causes the parameters to be read in as two parameter sets, processing from the left. The first two parameters are provided by the developer whilst the row references come from the BYROW helper function.

A major difference between LET and a traditional spreadsheet formula is that LET uses variable names to store partial calculations for later use, rather than using helper ranges, so reducing the volume of 'sheet junk' (i.e. intermediate results that occupy space but are of no intrinsic interest.) Lambda then hides the detail of the formula, providing instead a statement of the intent of the formula as well as listing the references.

 

[Hans Knudsen]

I am in doubt if this is the right place to have such lengthy discussion regarding basic understanding of LET, LAMBDA and other new Excel functions. That is why I attach a workbook where I have made some comments on my understanding so far. Disregard from this workbook if you aren't interested. Otherwise see sheet VP_2 column W.

 

[Hans Knudsen]

@p45cal
Thank you for your Power Query solution. I will return to that solution later. Just now I have my hands more than full trying to understand Peter Bartholomew's solution.

 

[Hans Knudsen]

@p45cal
Your Power Query solution seems to work absolutely fine. Thank you. I think I will go with either your formula solution or Peter Bartholomew's. Understanding those two solutions is at the very limit of my abilities and right now I can't handle also trying to understand the m-code.