Forum Discussion
Returning multiple column headers based on duplicated dates within a row
Hi I have read many similar questions & answers, but none of the replies seem to work with my data. I have a spreadsheet with dates in varying order from left to right and some blank cells within ea...
=IFERROR(BYROW(AX2:BF19,LAMBDA(row,TEXTJOIN(",",,FILTER($AX$1:$BE$1,TAKE(row,,8)=TAKE(row,,-1))))),"")Does this return the intended result for the sample data? The ranges of the formula can be adapted to the actual size of the data.
- Again many thanks for your help. Looking at this above it looks perfect. Sadly it is not working when I put it into my worksheet. I'll upload a sample.
OliverScheurich i might recommend using DROP(row,,-1) to make it more easily adapted to different sizes:
=BYROW(AX2:BF19,LAMBDA(row,TEXTJOIN(",",,FILTER($AX$1:$BE$1,DROP(row,,-1)=TAKE(row,,-1),""))))I also moved the alternative "" inside the FILTER but wasn't sure if there was any other reason for the IFERROR ...
- Hi Thanks for your reply. This did give me column headers in the output cell, but they didn't correctly relate to the cell content of that column (some were blank.) When I changed the formula to include my range it just gives me an error. I'll upload a sample file. I was hoping to be able to find which columns matched that with header S2REV, then use the same principle to use other columns as the reference column and perhaps in future look for the column(s) with a date after the reference column....
Clare1487 so it might be how you typed the formula in. mine is based on Quadruple_Pawn's and it expects the data to be immediately followed by target so range AX2:BF19 expects the last column to be the target and does not include the header and then $AX$1:$BE$1 is the header of only the data range (i.e. stops at BE)
=BYROW(AX2:BF19,LAMBDA(row,TEXTJOIN(",",,FILTER($AX$1:$BE$1,DROP(row,,-1)=TAKE(row,,-1),""))))
