I have two columns of data: names and number values. I want to find all existing unique combinations of names and values and transpose them as an array to another cell.
Let's say I start with this data set:
| A | B |
|---|---|
| Brad | 1 |
| Bryan | 5 |
| Albert | 1 |
| Ronnie | 20 |
| Brad | 50 |
| Brad | 1 |
| Ronnie | 20 |
What I'm trying to return is something like:
| A | B |
|---|---|
| Albert | 1 |
| Brad | 1 |
| Brad | 50 |
| Bryan | 5 |
| Ronnie | 20 |
Through some Googling, I was able to find and modify this query:
=ARRAYFORMULA(SPLIT(ARRAYFORMULA(TRANSPOSE(SPLIT(REPT(CONCATENATE(UNIQUE(FILTER(A:A,A:A<>""))&char(9)),COUNTA(UNIQUE(FILTER(B:B,B:B<>"")))),char(9)))&"|"&TRANSPOSE(SPLIT(CONCATENATE(REPT(UNIQUE(FILTER(B:B,B:B<>""))&char(9),COUNTA(UNIQUE(FILTER(A:A,A:A<>""))))),char(9)))),"|"))
It returns all possible combinations, but not just the existing combinations. I'm trying to avoid having to paste the possible combinations and run another check against the original columns. As the data set grows over time, listing all possible combinations could prove expensive.
How can I return only the existing combinations?
CodePudding user response：
Try this:
=arrayformula(sort(UNIQUE(A1:B8)))
Then you can see what you are trying to return.