Search code examples
if-statementgoogle-sheetsconcatenationtransposegoogle-sheets-query

How to collect data and headers for non blank cells in a row in Sheets

I cannot find a solution to my problem:

I have a sheet with ~290 rows and ~80 columns. The first row and column are fixed/header.

I would like to collect non-blank values and their header into column B.

enter image description here

I've tried to search for solutions, but I'm not as good at excel, so I cannot wrap my head around most of the advice that I've found.

Solution

  • In Google Sheets you could use an Array formula. I got this: enter image description here

    The formula I've used:

    =ArrayFormula(CONCATENATE(IF(--(C2:G2<>"")*COLUMN($C$1:$G$1)<>0;$C$1:$G$1&" "&C2:G2;"")))

    This is how it works:

    1. (--(C2:G2<>"") will return an array of 0 and 1 if the cell is blank or not
    2. COLUMN($C$1:$G$1) will return an array of column numbers of each cell
    3. (C2:G2<>"")*COLUMN($C$1:$G$1) we multiply both arrays, so we will get an array of column numbers of non blank cells and 0 of blank cells
    4. <>0;$C$1:$G$1&" "&C2:G2;"") We check if each number in the array obtained in step 3 is 0 or not. If it's 0, it returns a null value, if not, it returns the value of cell
    5. CONCATENATE will concatenate all values from previous array (step 4) so we concatenate null values with real values of non blank cells.

    Not sure if this will make the sheet load slower if you have too many records.

    Hope this helps