Created
November 4, 2022 02:03
-
-
Save YeeBePrime/dd3ba8b4f3d2b873068a72ba86e1104a to your computer and use it in GitHub Desktop.
Returns unique values in column by column of range
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
| /* | |
| uniqueCols | |
| Description | |
| Returns unique values in column by column of range | |
| Syntax | |
| uniqueCols(datarange,[by_col],[exactly_once],[lastresult],[lastcol]) | |
| The uniqueCols function syntax has the following arguments: | |
| datarange Required. The item range you want to get unique values | |
| by_col Optional. TRUE will compare columns against each other and return the unique columns | |
| FALSE (or omitted) will compare rows against each other and return the unique rows | |
| exactly_once Optional. TRUE will return all distinct rows or columns that occur exactly once from the range or array | |
| FALSE (or omitted) will return all distinct rows or columns from the range or array | |
| lastresult Optional. for recursive purpose. | |
| lastcol Optional. for recursive purpose. | |
| Return unique values of range. | |
| Example | |
| A1=1 B1=15 C1=21 | |
| A2=2 B2=12 C2=22 | |
| A3=3 B3=13 C3=23 | |
| A4=2 B4=14 C4=23 | |
| A5=5 B5=15 C5=25 | |
| A10=uniqueCols(A1:C5) | |
| Return | |
| A10=1 B10=15 C10=21 | |
| A11=2 B11=12 C11=22 | |
| A12=3 B12=13 C12=23 | |
| A13=5 B13=14 C13=25 | |
| A10=uniqueCols(A1:C5,,1) | |
| Return | |
| A10=1 B10=12 C10=21 | |
| A11=3 B11=13 C11=22 | |
| A12=5 B12=14 C12=25 | |
| */ | |
| uniqueCols=LAMBDA(datarange, [by_col], [exactly_once], [lastresult], [lastcol], | |
| LET( | |
| rng, datarange, | |
| lresult, IF( | |
| ISOMITTED( | |
| lastresult | |
| ), | |
| "", | |
| lastresult | |
| ), | |
| lcol, IF( | |
| ISOMITTED(lastcol), | |
| 0, | |
| lastcol | |
| ), | |
| ncol, IF( | |
| lcol > 0, | |
| lcol + 1, | |
| 1 | |
| ), | |
| result, HSTACK( | |
| lresult, | |
| UNIQUE( | |
| INDEX( | |
| rng, | |
| , | |
| ncol | |
| ), | |
| by_col, | |
| exactly_once | |
| ) | |
| ), | |
| IF( | |
| ncol + 1 <= | |
| COLUMNS(rng), | |
| uniqueCols( | |
| rng, | |
| by_col, | |
| exactly_once, | |
| result, | |
| ncol | |
| ), | |
| IFERROR( | |
| DROP( | |
| result, | |
| , | |
| 1 | |
| ), | |
| "" | |
| ) | |
| ) | |
| ) | |
| ) | |
| ; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment