How To Enable Gaming Mode on Android 12

Back in Februry, folks at XDA had spotted an unreleased Gaming Dashboard in the works for Android 12. But up until now, there were no clear...

Tuesday

Get the Headers of First Non-blank Cell in Each Row in Google Sheets – Array Formula

Posted by   on

To get the headers of the first non-blank cell in each row, we can use an array or non-array formulas in Google Sheets.


Why should one find the headers or field labels of the first non-blank cell in each row?


Let’s consider a payment schedule/break-up of a few contracts.


Assume I have signed contracts with five clients, and I have entered their payment schedule in one of my Google Sheets.


Please find the example below (except column C).


Headers of First Non-blank Cell in Each Row - Example

The data is arranged similarly to a pivot table.


I want to find the first payment date of each client from the schedule. For that, I have used formula(s) in column C.


To get that, we have to find the headers of the first non-blank cell in each row in the range D3:J7.


Here is how to do that.


In cell C3, insert the following formula.


=index($D$2:$J$2,MATCH(FALSE,ISBLANK(D3:J3),0))

Then copy it and paste it into the range C4:C7.


How does this formula find and return the header of the first non-blank cell in a row?


Formula Explanatioin (Non-Array)


1. The MATCH part of the formula finds the relative position of the first non-blank cell in the range D3:J3.


I have detailed the same within one of my earlier tutorials, and that link you can find after a few paragraphs below.


As a side note, the relative position is the count of cells from a starting cell to a target cell (here) horizontally.


Regarding the range D3:J3, the starting cell is D3, and the target cell is F3.


2. The INDEX offsets that many cells (Match output) in the range in the header, i.e., in $D$2:$J$2.


When we drag the C3 formula down, the header range (Index reference) remains the same because we have used absolute reference (dollar signs).


But it returns a different relative position in each row as the reference in the Match, i.e., D3:J3 is relative (without dollar signs).


Related:- Google Sheets: How to Return First Non-blank Value in a Row or Column.


Array Formula to Get the Headers of First Non-blank Cell in Each Row


We can’t convert the above Index and Match combination as an array formula that expands down.


It’s because the Match will only accept a single row or column reference.


But that doesn’t limit a Google Sheets user from writing an array formula to get the headers of the first non-blank cell in each row.


How?


Please follow the below steps and formula explanation.


Steps


1. Empty the range C3:C7.


2. Enter the following array formula in cell C3, and voila!


=index( sortn( query( split( flatten(ROW(D3:J7)&"|"&D2:J2&"|"&D3:J7), "|" ), "Select * where Col3 is not null" ), 9^9,2,1,1 ), 0,2
)

How does the above formula return the header of the first non-blank cell in each row in Google Sheets?


What I have done is – A) unpivot the table range D3:J7, B) filter out blank value cells and C) delete duplicate rows.


I’ll explain that below.


Formula Explanation (Array)


We must peel the formula or take out FLATTEN, SPLIT, QUERY, SORTN, and INDEX parts from it and test them individually.


When testing each part, except SORTN and INDEX, I may additionally use the ARRAYFORMULA function. Otherwise, they won’t work in standalone use.


A) Unpivot Data:


1. FLATTEN


=ArrayFormula(flatten(ROW(D3:J7)&"|"&D2:J2&"|"&D3:J7))

I have combined row numbers and headers with every value in the range in the following syntax.


Syntax: row_number|header|value


If any cell in the range is blank, the formula may only return row_number|header|


Please see this.


Step 1 - Flatten to Unpivot Data

2. SPLIT


=ArrayFormula(split(flatten(ROW(D3:J7)&"|"&D2:J2&"|"&D3:J7),"|"))

In this step, we will separate the row numbers, headers, and values into separate columns.


We have unpivoted the data!


B) Filter Out Blank Value Rows:


3. QUERY


=ArrayFormula(query(split(flatten(ROW(D3:J7)&"|"&D2:J2&"|"&D3:J7),"|"),"Select * where Col3 is not null"))

The Query filter out rows that has blank values, or we can say keep row_number|header|value and omit row_number|header|


C) Delete Duplicate Rows:


For the below tests the ARRAYFORMULA is not required.


4. SORTN


Here it removes duplicates based on a selected column, and that is column contains row numbers.


=sortn(query(split(flatten(ROW(D3:J7)&"|"&D2:J2&"|"&D3:J7),"|"),"Select * where Col3 is not null"),9^9,2,1,1)

SORTN to Get the Headers of First Non-blank Cell in Each Row

5. In the final formula, I have used INDEX to extract the second column.


Conclusion


When you use an array formula to get the headers of the first non-blank cell in each row in Google Sheets, you must make sure one thing.


What’s that?


There must be at least one value in each row.


As per the above example, there must be at least one payment in the range D3:J7 in each row.


That’s all. Thanks for the stay. Enjoy!


Resources:

No comments:
Write Comments

Hello Friends, welcome to autobloginc.blogspot.com we Hope You'll like it - COntact US
!!THANK YOU FOR YOUR SUPPORT!!