Combining text from different rows based on a date?

You could do all the concatenations in the first table then use a lookup function/formula to bring them into the second table. An example is below:

The formula in D2 =IF(B2<>"",IFERROR(INDEX(D$1:D1,XMATCH(B2,B$1:B1,0,-1))&CHAR(8232),"")&C2,"")

I used the new XMATCH function so I could specify searching bottom to top. For the carriage return/line feed I used the Unicode line separator code 8232 (2028 hex) because CHAR does not accept the number for a true carriage return or line feed.

If you hide column D, the rows go back to normal one-line height.

The function is NOT sort-safe. You will not be able to sort the table. This is a bug in Numbers that needs to be fixed ASAP. The so-called ABSOLUTE cell references get changed in the ranges when sorting. It sorts properly in Excel.

If you need it to be sort-safe while they work on this bug, you could move column D to be its own table. That way it does not get sorted. Make it extra long to accomodate the max length you'll think Table 1 will ever be. The lookup function would be like below if D is dragged out to create Table 1-1:

Table 2 cell A2 has the date to look for

Table 2 cell B2=INDEX('Table 1-1'::A,XMATCH($A2,Table 1::B,0,-1)+1)

The +1 in that formula is because if you use an entire column reference (Table 1::B) in XMATCH it ignores headers and footers. INDEX, however, does not ignore headers and footers so we have to add 1 to account for the one header row.

The bug has been reported and the team has contacted me for info on it so they are aware and hopefully working on it. I look forward to the update that fixes it.

Always more than one way to do things. It occurred to me that the formula given in my last post is more complicated than you probably need. It doesn't care if the dates/values in column B are sorted, they could be scattered. If your dates will be sorted so each date is together with others of the same date, a much simpler and sort-safe formula can be used in Table 1:

D2=IF(B2=B1,D1&CHAR(8232)&C2,C2)

Fill down

The table must be sorted. If there is, for example, a stray "8/14/20" not with the other "8/14/20's", it will not get grouped with the rest of them.

It's quite cumbersome to do concatenations like this using formulas in a spreadsheet. You'd lots of extra columns.

It's possible to use a script to do this.

But first you might want to experiment with Categories. In one click that functionality can give you something like this:

SG