Google-sheets – Spreadsheet formula to calculate most common items in ordered items lists

formulasgoogle sheetsgoogle-sheets-arrayformulagoogle-sheets-queryregex

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

Best Answer

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)

enter image description here

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

Functions used:

Related Topic