Communities

Writing
Writing
Codidact Meta
Codidact Meta
The Great Outdoors
The Great Outdoors
Photography & Video
Photography & Video
Scientific Speculation
Scientific Speculation
Cooking
Cooking
Electrical Engineering
Electrical Engineering
Judaism
Judaism
Languages & Linguistics
Languages & Linguistics
Software Development
Software Development
Mathematics
Mathematics
Christianity
Christianity
Code Golf
Code Golf
Music
Music
Physics
Physics
Linux Systems
Linux Systems
Power Users
Power Users

Dashboard
Notifications
Mark all as read
Q&A

How to pivot text?

+2
−0

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?

Why does this post require moderator attention?
You might want to add some details to your flag.
Why should this post be closed?

3 comments

Why is this called "pivoting text"? Moshi‭ 8 months ago

@Moshi It's very similar to an Excel pivot table - in general summarizing multi-dimensional data with rows and columns is called pivoting data. The row / column / values are called pivots. Sigma‭ 8 months ago

@Sigma I see. I guess I was more confused about the "text" part - aren't you pivoting the cells, not text? Perhaps I'm just not familiar with the terminology Moshi‭ 8 months ago

2 answers

+3
−0

This seems to be looking at the outcome of a bracket in which successive players show an outcome. The table can be easily created by looking at each combination of players and listing them in the table using the index-match combination to find the correct value. Combinations that are not found in the table will return a #N/A error, and we can use iferror to show an exception.

The formula in column D should be a simple concatenation of the values in columns A and B:

=A2&B2

The formula in the top left cell of the table should should be:

=iferror(INDEX($A$1:$C$6,MATCH($F2&G$1,$D:$D,0),3),"x")

Copy & paste to fill the rest of the cells.

screenshot of data and table

Why does this post require moderator attention?
You might want to add some details to your flag.

0 comments

+1
−1

Perhaps (if a process of several relatively familiar steps is easy to remember) but additional formulae may be appropriate, a little extension to the source data required and some formatting. However, the offered solution only works for at most three states (here YES, NO, and X).

First, add the required additional data. This is the trigger for the Xs, so perhaps a formula in A7 and copied across to B7 of:

=unique($A2:$A7)

Enter 0 in ColumnD of each of the rows populated by the UNIQUE formula. In D2 and copied down (say by double-clicking the fill handle):

=IF(C2="YES",1,-1)

Then in say F2:

=query(A2:D10,"select A, sum(D) group by A pivot B")

Finally for presentation purposes select from ColumnF and to the right and Format > Number > More Formats > Custom number format and:

"YES";"NO";"X"

and Apply.

Inputs and output

This is extensible for more people (possibly most easily by inserting rows at the position of the UNIQUE formula) but would then require additional population of ColumnD and extending the Query range.

The process would be much simpler if a fill (could be applied with Conditional Formatting) was allowed instead of the Xs and would be easier to extend if a blank row were allowed in the pivot table under its headings.

Why does this post require moderator attention?
You might want to add some details to your flag.

0 comments

Sign up to answer this question »