2

I have a spreadsheet like this, where the values A-E are the same options coming from a form:

+------+------+------+
| Opt1 | Opt2 | Opt3 |
+------+------+------+
|   A  |   A  |   B  |
|   B  |   C  |   A  |
|   C  |   C  |   B  |
|   A  |   E  |   C  |
|   D  |   B  |   E  |
|   B  |   E  |   D  |
+------+------+------+

I want to make a ranking, showing the most chosen options for each option. I already have this, where Rank is the ranking of the option and number is the count of the option:

+------+------+------+
| Rank | Opt1 | Numb |
+------+------+------+
|   1  |   A  |   2  |
|   1  |   B  |   2  |
|   3  |   C  |   1  |
|   3  |   D  |   1  |
+------+------+------+ (I have 3 of these, one for each option)

I want to do now a summary of the 3 options, making the same ranking but joining the options. It would be something like:

+------+------+------+
| Rank |Opt123| Numb |
+------+------+------+
|   1  |   B  |   5  |
|   2  |   A  |   4  |
|   2  |   C  |   4  |
|   4  |   E  |   3  |
|   5  |   D  |   2  |
+------+------+------+

The easiest way to do this would be getting the data from the three ranking tables or from the original three data columns?

And how would I do this?

I already have the formula to get the names of the options, the count and ranking, but I don't know how to make them work with multiple columns.

What I have (the F column is one of the data columns):

Column B on another sheet:

=SORT(UNIQUE(FILTER('Form Responses'!F2:F;NOT(ISBLANK('Form Responses'!F2:F)))); RANK(COUNTIF('Form Responses'!F2:F; UNIQUE(FILTER('Form Responses'!F2:F;NOT(ISBLANK('Form Responses'!F2:F))))); COUNTIF('Form Responses'!F2:F; UNIQUE(FILTER('Form Responses'!F2:F;NOT(ISBLANK('Form Responses'!F2:F))))); TRUE); FALSE)

Column C:

=ArrayFormula(COUNTIF('Form Responses'!F2:F; FILTER(B2:B;NOT(ISBLANK(B2:B)))))

Column A:

=ARRAYFORMULA(SORT(RANK(FILTER(C2:C;NOT(ISBLANK(C2:C))); FILTER(C2:C;NOT(ISBLANK(C2:C))))))
Community
  • 1
  • 1
Rodrigo Castro
  • 1,573
  • 14
  • 38

1 Answers1

3

Edited:

Merge cols:

=TRANSPOSE(split(join(",",D2:D,E2:E),","))

merges 2 cols, not very clean, but works. (Same as here Stacking multiple columns on to one?)

Full formula:

=SORT(UNIQUE(FILTER(TRANSPOSE(split(join(",",D2:D,E2:E),","));NOT(ISBLANK(TRANSPOSE(split(join(",",D2:D,E2:E),",")))))); RANK(COUNTIF(TRANSPOSE(split(join(",",D2:D,E2:E),",")); UNIQUE(FILTER(TRANSPOSE(split(join(",",D2:D,E2:E),","));NOT(ISBLANK(TRANSPOSE(split(join(",",D2:D,E2:E),","))))))); COUNTIF(TRANSPOSE(split(join(",",D2:D,E2:E),",")); UNIQUE(FILTER(TRANSPOSE(split(join(",",D2:D,E2:E),","));NOT(ISBLANK(TRANSPOSE(split(join(",",D2:D,E2:E),","))))))); TRUE); FALSE)

The transpose could be done after the sort.

Community
  • 1
  • 1
eddyparkinson
  • 3,680
  • 4
  • 26
  • 52
  • Thanks, but the "A" must be dynamic, because I have over 80 options, and making this for each one would not be practical at all. Also, not all the 80 or so options appear in all the columns, if no one has chosen it yet, it won't appear. – Rodrigo Castro Mar 15 '13 at 04:36
  • I don't know if I was clear enough, but for example, in my first example, the Opt1 column didn't have any occurrence of "E". So I have to somehow first combine all the options in the three columns, make them unique and than I can put them in the COUNTIF condition as an ArrayFormula. I'm stuck in this first part of joining the options of all 3 columns. – Rodrigo Castro Mar 15 '13 at 04:42
  • do you only have 3 columns? if yes, then you should be able to use this approach. The method you use at the moment to make the "A" dynamic should work. Maybe I am missing something ... – eddyparkinson Mar 15 '13 at 04:52
  • I'll try this, thanks, but I think I already tried something very similar to this and due to one column not having a record that another column does, my count went south. I'll try to see what caused it, I too think this approach should work if I can make it dynamic. – Rodrigo Castro Mar 15 '13 at 22:14
  • I tried this approach here and it kinda worked. I could join the 3 columns of options using this answer http://stackoverflow.com/questions/9442099/stacking-multiple-columns-on-to-one, but when I put them all together, the SORT function that takes all the options and sorts them according to the rank didn't work, it just sorted them alphabetically, and the only difference between this and the sort I had previously is the this uses countif + countif + countif, and takes seconds to evaluate. – Rodrigo Castro Mar 16 '13 at 17:41
  • My function looks like this now: `=SORT(options; rank(3x countif; 3x countif; true); false)`. True and false is to order by the count decreasing and rank increasing. I think it's not working due to some bug or limitation with Google spreadsheet, because everything works separatedly but when I join the formulas, I get a monstruous formula that takes just too long to execute, maybe the sort is not waiting for the rank to evaluate, I don't know... – Rodrigo Castro Mar 16 '13 at 17:46
  • I edited the answer. If it is slow, can you let me know how many rows you have. – eddyparkinson Mar 18 '13 at 04:44
  • Thank you, I'll accept that as it's the closest thing I got from working, and because I also think this is right, but it's breaking due to the high processing time. – Rodrigo Castro Mar 18 '13 at 16:41
  • Oh, and it's a survey response spreadsheet, with a little over 800 responses, and 16 columns. I'll take a look now at querying to see if I can solve my problem faster that way. – Rodrigo Castro Mar 18 '13 at 16:44
  • I just solved it! Realized I could count the transposed results, instead of doing 3 countifs that took 3 times more to process. My query was something like `SORT(transposed_options; rank(countif(transposed_options; blablabla); countif(transposed_options; blablabla); true); false)`. Works like a charm, and it's much faster now. – Rodrigo Castro Mar 18 '13 at 17:36