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 each row. I want to be able to return the headers of columns that match the date entered in column BF at the end of each row (the spreadsheet spans to HK7000)
Maybe this one. I tried to keep BYROW's usage to a minimum because it will be an issue on a larger data set.
=LET( results, IF(data = target, header, ""), BYROW(results, LAMBDA(row, TEXTJOIN(", ", 1, row))) )- Thank you so much for your help. Unfortunately this is not giving me the output I needed. Perhaps an error on my part inputting the formula(?) I'll upload a sample file in case that explains it. I have tried replacing blank cells with X but that hasn't made any difference.
I have typed
=LET(results, IF(T2 = D2:FY2, header, ""),BYROW(results, LAMBDA(row, TEXTJOIN(",",1,row))))
It just gives me the error #NAME?- It might have issue with 'header' which is a defined name in my sample. It refers to the first row which contains the column headings.
=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....
