Last active
January 4, 2024 11:33
-
-
Save ttarchala/1cbdfab59b4c6d97cf77fec651f5475c to your computer and use it in GitHub Desktop.
Excel range finding utility functions: lastDataRowOf1stCol, TrimToData
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| // lastDataRowOf1stCol: a utility function to find out in which row data ends | |
| // Accepts a column or a 2-d range; for the latter, considers the first column only. | |
| // Robustly considers any data type, handles blanks in the range. If no data, returns 0. | |
| // Example of use: | |
| // =lastDataRowOf1stCol(A:A) | |
| // =lastDataRowof1stCol(B4:C9) | |
| lastDataRowOf1stCol = LAMBDA(range, | |
| LET( | |
| firstCol, TAKE(range, , 1), | |
| MAX( | |
| IFNA(MATCH(1E+306, firstCol, 1), 0), | |
| IFNA(MATCH(TRUE, firstCol, 1), 0), | |
| IFNA(MATCH("", firstCol, -1), 0) | |
| ) | |
| ) | |
| ); | |
| // TrimToData: a utility function to restrict an open-ended range to the size of last piece of data in the (first) row and (first) column | |
| // Accepts a 2-d range; for the latter, considers the first row and the first column only to determine how much data to take | |
| // Robustly considers any data type, handles blanks in the range. If no data, returns a #CALC error. | |
| // Example of use: | |
| // =TrimToData(A:E) | |
| // =TrimToData(1:4) | |
| // =TrimToData(B4:C9) | |
| TrimToData = LAMBDA(range, | |
| TAKE(range, | |
| lastDataRowOf1stCol(range), | |
| lastDataRowOf1stCol(TRANSPOSE(TAKE(range,1))) | |
| ) | |
| ); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment