I have a sheet with a tab/column of email addresses submitted via Google Form (FORM RESPONSES/EMAIL). I have written a formula that counts the unique occurrences of EMAIL and outputs the COUNT.
I have another sheet with two columns: master list of EMAIL addresses and associated NAMES.
I would like to know what formula I might use to achieve a list on a third sheet of NAMES (associated with the email addresses counted earlier) and COUNT.
FORM RESPONSES:
| B |
|---|
| email1 |
| email2 |
| email3 |
| email1 |
| email1 |
| email2 |
MASTER EMAIL LIST
| A | B |
|---|---|
| email1 | Thomas |
| email2 | BeckyJ |
| email3 | PaulaG |
CURRENT OUTPUT
=QUERY('Form Responses 1'!B2:B,"SELECT B, COUNT(B) WHERE B IS NOT NULL GROUP BY B ORDER BY COUNT(B) DESC LIMIT 10 LABEL COUNT (B) ''",0)
| B | count |
|---|---|
| email1 | 3 |
| email2 | 2 |
| email3 | 1 |
DESIRED OUTPUT
| A | count |
|---|---|
| Tom | 3 |
| BeckyJ | 2 |
| PaulaG | 1 |
I have tried multiple variations of formulae that seem to break everything. Is this possible?
Since you already have a working QUERY, you can replace the values with XLOOKUP or VLOOKUP and create a virtual column with names instead of mails:
=QUERY(INDEX(XLOOKUP('Form Responses 1'!B2:B,Master!A:A,Master!B:B,)),
"SELECT Col1, COUNT(Col1) WHERE Col1 IS NOT NULL GROUP BY Col1 ORDER BY COUNT(Col1) DESC LIMIT 10 LABEL COUNT(Col1) ''",0)