Welcome to Software Development on Codidact!
Will you help us build our independent community of developers helping developers? We're small and trying to grow. We welcome questions about all aspects of software development, from design to code to QA and more. Got questions? Got answers? Got code you'd like someone to review? Please join us.
Comments on How to pivot text?
Post
How to pivot text?
In this Q a user asked for a simple way to represent this data:
- PERSON 1 | PERSON 2 | YES
- PERSON 1 | PERSON 3 | YES
- PERSON 2 | PERSON 1 | YES
- PERSON 2 | PERSON 3 | YES
- PERSON 3 | PERSON 1 | NO
- PERSON 3 | PERSON 2 | NO
in this format:
- _________| PERSON 1 | PERSON 2 | PERSON 3 |
- PERSON 1 | X | YES | YES |
- PERSON 2 | YES | X | YES |
- PERSON 3 | NO | NO | X |
This formula was offered as a solution:
=ARRAYFORMULA({{""; UNIQUE({FILTER(A:A,A:A<>"");FILTER(B:B,B:B<>"")})}, {TRANSPOSE(UNIQUE({FILTER(A:A,A:A<>"");FILTER(B:B,B:B<>"")}));IFERROR({ VLOOKUP(UNIQUE({FILTER(A:A,A:A<>"");FILTER(B:B,B:B<>"")}), FILTER(A:C,B:B=INDEX(UNIQUE({FILTER(A:A,A:A<>"");FILTER(B:B,B:B<>"")}),1,1)),3,0), VLOOKUP(UNIQUE({FILTER(A:A,A:A<>"");FILTER(B:B,B:B<>"")}), FILTER(A:C,B:B=INDEX(UNIQUE({FILTER(A:A,A:A<>"");FILTER(B:B,B:B<>"")}),2,1)),3,0), VLOOKUP(UNIQUE({FILTER(A:A,A:A<>"");FILTER(B:B,B:B<>"")}), FILTER(A:C,B:B=INDEX(UNIQUE({FILTER(A:A,A:A<>"");FILTER(B:B,B:B<>"")}),3,1)),3,0)}, "X")}})
where it has been assumed the source table (unlabelled) starts in A2.
A mere copy/paste of a formula written for one is about the simplest possible solution, but, because of formatting, just copy pasting the above may not work. Also, without adjustment, the formula would break if ColumnsA:B were labelled, or might break in case of further content of a different nature in those columns. In addition, the question was tagged [google-sheets-query].
Is there a more memorable way to achieve the desired result, preferably using Google's Query Language?
1 comment thread