# Multiple Unique Values Across Worksheets in Google Sheets

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.

```=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

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
• Thank you so much for both an answer and an explanation. I tried using TRANSPOSE(), but I didn't use it correctly I guess. This works really well, and I really can't thank enough for explaining how it all works at the end! – Patrick Roddy Dec 5 '19 at 5:18