Google-sheets – Multiple Unique Values Across Worksheets in Google Sheets

formulasgoogle sheetsgoogle-sheets-arrayformula

I rarely use spreadsheets, but I thought creating a workbook for a contest would be really helpful. I am not sure if what I am asking is possible without a custom function. I am grateful for any and all help that may be offered.

I am trying to make a template that keeps track of a weekly writing contest. Each week is scored independently and then added up at the end of the month. I wanted to have a sheet that keeps track of the grand total. I thought I could do a UNIQUE() search of each worksheet and have it output a single column. As per this answer I've tried 

=UNIQUE({'Week1'!B2:B,'Week2'!B2:B,'Week3'!B2:B,'Week4'!B2:B})

This pulls unique values for each sheet, but lines them up in multiple columns. I was hoping to just get all of the usernames in column A, and then use a lookup formula to add up the totals from each week in column B. Here is the Proof of Concept workbook if anyone can take a look.

Again I appreciate any and all suggestions or directions to get this to work. If it can't be done I can just go to manually tabulating totals, but I was hoping to streamline my workflow.

Best Answer

=UNIQUE( TRANSPOSE( SPLIT( CONCATENATE(ARRAYFORMULA(UNIQUE(Week1!B2:B)&CHAR(9)))& CONCATENATE(ARRAYFORMULA(UNIQUE(Week2!B2:B)&CHAR(9)))& CONCATENATE(ARRAYFORMULA(UNIQUE(Week3!B2:B)&CHAR(9)))& CONCATENATE(ARRAYFORMULA(UNIQUE(Week4!B2:B)&CHAR(9))), CHAR(9)) ) )

Props-Will Cervarich

Screenshot

Blindingly straightforward when it is broken down like this:

  1. the unique names for each week are obtained,
  2. a 'Tab' character is added,
  3. the whole is wrapped in an arrayformula (to automatically list all values)
  4. the lot is concatenated - resulting in a string of the unique names each followed by a tab.
    • the result looks like this:
      User 1 User 2 User 3 User 4 User 5 User 6
  5. Rinse and repeat for each of the four weeks and joining all the weeks
  6. Split this using char(9) as a delimiter. The result is a horizontal series of the individual unique names from the four weeks.
  7. Transpose the names list to the vertical
  8. Lastly, get the unique names of the vertical list
Related Topic