1

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.

Improve this question

2 Answers 2

Reset to default
2

=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
Improve this answer
1
  • 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, 2019 at 5:18
1

=UNIQUE({'Week1'!B2:B;'Week2'!B2:B;'Week3'!B2:B;'Week4'!B2:B}) also works

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.