Formula to extract only rows with values.(Microsoft Excel)
I will introduce a formula to extract only valid rows using only formulas without filters or macros.
Because it uses a new function added to Excel in September 2022. It cannot be used with older versions of Excel.
Contents
Formula
The LET, HSTACK, BYROW, LAMBDA, MAP, DROP, FILTER, CHOOSECOLS, and COUNTA functions are used.
Cell ranges in red should be changed as necessary.
=LET(Area,A:C,
WorkArea,HSTACK(BYROW(Area,LAMBDA(Row,COUNTA(Row))),Area),
MAP(DROP(FILTER(WorkArea,CHOOSECOLS(WorkArea,1)>0),,1),LAMBDA(Cell,IF(Cell="","",Cell)
)))
Rows with no values are excluded from the formula result.
You can use the formulas as they are, but it is easier to use them if you register them in the Name Manager section.
Click New.
Specify any function name in Name and the following formula in Reference range.
=LAMBDA(Area,LET(WorkArea,HSTACK(BYROW(Area,LAMBDA(Row,COUNTA(Row))),Area),MAP(DROP(FILTER(WorkArea,CHOOSECOLS(WorkArea,1)>0),,1),LAMBDA(Cell,IF(Cell="","",Cell)))))
It will be available with the specified function name.
---