Combine text at each change in date

[johncaseymilford]

Hello!

I am looking for a way to combine the text in various numbers of rows at each new date entry in a separate row.

I attach a spreadsheet showing the current layout and the desired layout.

I have been using Fill Justify and TEXTJOIN but there's 150+ pages of data and so a formula would be amazing.

Any help appreciated!

Cheers

 

[p45cal]

Power Query solution in the orange table on the DESIRED LAYOUT sheet of the attached.
1. Select the sheet with the source table
2. Select a cell within that table
3 & 4. Make it a proper Excel table with Insert|Table


Make sure the new table completely encompasses your source data.
5 & 6. Rename that table Table1:


[Note that Excel won't let you name 2 tables with the same name, so as I have already got a Table1 on my copy of your sheet called CURRENT LAYOUT (2), you will either need to delete that sheet or rename its table to something else.]

Anyway, that's the donkey work done, all you need to do now is to go to that orange table on the DESIRED LAYOUT sheet, right-click somewhere in it and choose Refresh.

I have assumed that the TRANSACTIONS column is the only column that needs treating this way.

 

[johncaseymilford]

That is amazing P45cal!! Thank you so much!

Is there any way you could explain to me how you made the orange table in the DESIRED LAYOUT tab? I know you mentioned that it was done with Power Query but I would love to know how you did it?

Many thanks again!

John

 

[p45cal]

As you're not familiar with Power Query it would take too long for me to outline the steps, but I'll point you to where things happen in your workbook, then you can read up about PQ on the web.
In that file, right-click on the orange table and choose Table, then Edit Query… this will take you straight to the Power Query Editor with the table in it. On the right hand side you'll see APPLIED STEPS below which you can click on each step and see the result in the table on the left. Each step (most steps) has a cogwheel to its right which you can click on to see the code (which you can also see in the formula bar) or a dialogue box which creates the code.
To close all this you can click the X in the top right and discard any changes, or you can go into the Home tab and choose Close & Load to commit your changes to the query.

 

[Peter Bartholomew]

It is possible to restructure the data using Excel 365 Lambda functions and, in my eyes, is of similar complexity as the Power Query solution. The perfect solution would be to read the data direct from the source into PQ and transform it before it ever reaches the worksheet.

Given that the data is what it is, the following is my worksheet formula

= LET(
      criterion,      ISNUMBER(ImportTbl[DATE]),
      impTransact,    ImportTbl[TRANSACTIONS],
      filteredTable,  FILTER(ImportTbl, criterion),
      transactions,   FormatFieldλ(impTransact, criterion),
      selectedField?, {1,1,0,1,1},
      IF(selectedField?, filteredTable, transactions)
   )

The catch is that the formula contains a further lambda function, FormatFieldλ, defined by

= LAMBDA(list, criterion,
    LET(
        N,      ROWS(list),
        rowNum, SEQUENCE(N),
        start,  FILTER(rowNum, criterion),
        end,    FILTER(rowNum, Rotateλ(criterion, -1)),
        lines,  1 + end - start,
        MAP(start, lines, ConcatEntryλ(list))
    )
)

which concatenates the lines of transaction data into a single record. Rotateλ and ConcatEntryλ are reasonably straightforward but still have to be written. One conclusion I have drawn is that 365 solutions based upon functional programming bear very little similarity to any traditional approach.