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
Tabletop RPGs
Tabletop RPGs
Community Proposals
Community Proposals
tag:snake search within a tag
answers:0 unanswered questions
user:xxxx search by author id
score:0.5 posts with 0.5+ score
"snake oil" exact phrase
votes:4 posts with 4+ votes
created:<1w created < 1 week ago
post_type:xxxx type of post
Search help
Notifications
Mark all as read See all your notifications »
Q&A

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.

How to pivot text?

+3
−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?

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

1 comment thread

General comments (3 comments)

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

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

0 comment threads

+3
−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.

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

0 comment threads

Sign up to answer this question »