Using ArrayFormula in Google Sheets to concatenate headers of non-blank columns

0
votes

I'd like to create a column that will list the headers of the columns (for each row) in a range that are not blank (they should have dates in them, if that is relevant - but will remain blank until a date is added for a column).

I'd really like this to populate automatically using ArrayFormula (or something similar) since my spreadsheet gets added to all the time and those adding to it are unlikely to remember to spread all of the necessary formulas every time. My dummy sheet is here.

Columns E:G contain dates when A, B and C thing happened, for each person/row. I want to list the names of the columns of the things that have happened for that person. However I don't know how to make it so that it ignores blank cells.

Putting in the combination of every possible blank cell + non-blank cell combo is not really possible because in the real dataset I have more than 3 such columns and they will also be added to, to an arbitrary number of them.

You can see the desired output (I put in manually) in column M. My inadequate attempts are in P and Q. I have not yet figured out how to even do this for one cell, let alone an ArrayFormula that would do it for me.

I imagine this can be done using ArrayFormula but I'm happy to accept other solutions that work as well.

3
google-sheetsgoogle-sheets-formulaarray-formulas
Are you needHelpLeo from The Google Sheets Forum? – MattKing
No, I don't think that whoever you are talking about is me. I default to Stack Overflow. But you have helped me on here recently with the same dummy sheet. – Leo

3 Answers

2
votes

I have added a new sheet ("Erik Help - Problem 2") to your spreadsheet. The following formula is in cell M1, highlighted in green:

=ArrayFormula({"contactedFor"; IF(A2:A="",, REGEXREPLACE(SUBSTITUTE(TRIM(TRANSPOSE(QUERY(TRANSPOSE(IF(E2:G="" ,, E1:G1&"|")), " ", COLUMNS(E1:G1)))), "|", ","), ",$", ""))})

This formula exploits a quirk in the way QUERY processes headers (i.e., it allows multiple headers, not just 0 or 1). So we can "stack" E:G results as headers, TRIM out the blanks, and then TRANSPOSE them to match the row-by-row results.

1
votes

I love a good problem! There are many different ways you can approach this but I always love looking for a way to have a solution in an array so I don't have to keep copying down the formula.

=Array_Constrain(ArrayFormula(If(Right(if(E2:E <>"","A,",)&if(F2:F <>"","B,",)&if(G2:G <>"","C",))=",",regexreplace(if(E2:E <>"","A,",)&if(F2:F <>"","B,",)&if(G2:G <>"","C",), "(.),", "$1"),if(E2:E <>"","A,",)&if(F2:F <>"","B,",)&if(G2:G <>"","C",))),CountA(A2:A),1)

The array will constrain the total number of rows with string data in column A. As new rows are added, the formula will auto-populate based on the requested criteria.

Updated:

To include the header:

=Array_Constrain(ArrayFormula({"OneinaM"; If(Right(if(E2:E <>"","A,",)&if(F2:F <>"","B,",)&if(G2:G <>"","C",))=",",regexreplace(if(E2:E <>"","A,",)&if(F2:F <>"","B,",)&if(G2:G <>"","C",), "(.),", "$1"),if(E2:E <>"","A,",)&if(F2:F <>"","B,",)&if(G2:G <>"","C",))}),CountA(A:A),1)
0
votes

Try TEXTJOIN() formula like-

=ArrayFormula(TEXTJOIN(", ",TRUE,IF(E2:G2<>"",$E$1:$G$1,"")))

enter image description here