Value transformation in Google Sheets

Question

I'm working with a table of data as following:

User ID:       Event name:
111            Event 1
222            Event 2
333            Event 3
111            Event 4
444            Event 5
111            Event 6

So, what I'm trying to achieve is get a report in another sheet, which would

a) correspond user ID: 111, 222, 333, 444 with user names: name 1, name 2, etc.
b) count the total number of events per each user. Any idea how this can be resolved?

Answer 1

• create a sheet where you assign IDs to names

• use this simple query formula:

  =ARRAYFORMULA(QUERY({IFERROR(VLOOKUP(Sheet1!A1:A, Sheet2!A1:B, 2, 0)), Sheet1!B1:B}, 
   "select Col1, count(Col1) 
    where Col2 contains 'event' 
    group by Col1 
    label Col1'Name', count(Col1)'Events count'"))

• demo spreadsheet