I am working on a voting form to rank some things (A, B, C, D, F) using Google Forms. I have it set up to import the results into Google Sheets. What I am trying to do is to then take the results and sort them in another sheet by their rankings.
So the result of the form looks something like this:
| Item A | Item B | Item C | Item D | Item E | Item F | Item G |
|---|---|---|---|---|---|---|
| A | B | F | C | F | A | F |
| B | C | F | C | D | A | F |
| B | A | D | C | F | A | F |
| B | B | D | C | D | B | F |
I then have a formula that finds the majority ranking for each item and places them into another sheet, which looks like this:
| Item A | Item B | Item C | Item D | Item E | Item F | Item G |
|---|---|---|---|---|---|---|
| B | B | D | C | D | A | F |
My goal is to have the final sheet transpose that table and sort them alphabetically by their voted upon ranking. So using the dataset above, it would look like this:
| Rank A | Rank B | Rank C | Rank D | Rank F |
|---|---|---|---|---|
| Item F | Item A | Item D | Item C | Item G |
| Item B | Item E |
Is this something that is possible to automate in Google Sheets? Currently I am doing the last part manually, but I would love to have it auto-add each ranked item to the correct column and if possible, sort them alphabetically in their respective ranks.
CodePudding user response：
try:
=ARRAYFORMULA(SUBSTITUTE(TRANSPOSE(SPLIT(FLATTEN(QUERY(
QUERY(TEXT(TRANSPOSE(SUBSTITUTE(A1:G2, " ", "×")), {"@", "Rank×@"}),
"select max(Col1) group by Col1 pivot Col2"),,9^9)), " ")), "×", " "))
CodePudding user response：
I have created this example, you will need to change the ranges in the formula to match with your data.
=TRANSPOSE(INDEX(FILTER(A1:G1, A2:G2 = "A")))
Add the above formula below each “Rank” column and change the letter “A” to the respective ranking.
