Spreadsheet formula to calculate most common items in ordered items lists

Question

I am searching for a correct google spreadsheet formula to go through the list of ordered items and summarise the resulting winning items.

Like is you have an Oscar ceremony and you get votes from academy mambers and each gives his/her votes on a pre-defined list of candidates. I want to get who got the most votes for 1st place and for 2nd (other, also good but less relevant).

Imagine I have several lists of names, placed in order of preference:

1. Mike, Jane, Kate
2. Jane, Kate, Mike
3. Mike, Kate, Jane ..

the formula after applying should say that:

Mike is x 2 - first place

Kate is x 2 - second place

Any idea how to do that? usually, the list is 5-8 items and you get 10-15 votes.

Ive found a list of ALL formulas but it's hard to find if there's one: https://support.google.com/docs/table/25273?hl=en

Answer 1

Please use the following formula

=QUERY(flatten(ArrayFormula(IFERROR( 
                  REGEXREPLACE(SPLIT(A2:A11,", "),"("&REGEXREPLACE(A2:A11,", ","\)|\(")&")","$1$2$3"&" "&COLUMN(A1:C1)&" place")))), 
        "select Col1, count(Col1) group by Col1 offset 1 label count(Col1) '' ",0)

(You can adjust range A2:A11 according to your needs)

Functions used:

• QUERY
• ArrayFormula
• IFERROR
• REGEXREPLACE
• SPLIT
• COLUMN
• flatten

Answer 2

@shershen, for the sake of my suggestion here, I will assume the following:

1. That your comma-separated choices lists runs A2:A
2. That everyone will make exactly three choices (no more, no fewer)
3. That first choice = 3pts, second choice = 2pts and third choice = 1pt

That being the case, try the following formula:

=ArrayFormula({"#"&SEQUENCE(COUNTUNIQUE(QUERY(FLATTEN(SPLIT(FILTER(A2:A,A2:A<>""),", ",0)),"Select * Where Col1 Is Not Null")),1), QUERY(SPLIT(FLATTEN(SPLIT(REGEXREPLACE(FILTER(A2:A, A2:A<>""),"(\w+), (\w+), (\w+)","$1 3, $2 2, $3 1"),", ",0))," "),"Select Col1, SUM(Col2) Where Col1 Is Not Null Group By Col1 Order By SUM(Col2) Desc Label SUM(Col2) ''")})

Please report back whether or not this produces the result you expect, after which I'll do my best to explain how the formula works. (It's not an efficient use of time to explain it unless we know it works as expected.)