So I've been searching for days. I am running a spreadsheet to track game stats for a video game League. I need to break the players down into their specific roles, and show their stats for only the weeks they played those roles.
Got the stats working but having a problem pulling all the players' names. I was using a UNIQUE(QUERY) function to pull the names from each week's games, but I noticed that not all the players were being pulled even if they matched the criteria. What did I do wrong?
Here's a copy of the spreadsheet for you all to comment on: https://docs.google.com/spreadsheets/d/18C-edA8uvyVLnozBLqOhOba4_ukzyhtXKFIno-XXsKU/edit?usp=sharing
Formula in question is most easily seen on the "THE ADCs" tab, E11.
The specific Formula Array is as follows:
=UNIQUE(QUERY({Week1_Stats!$B4:$AH,Week2_Stats!$B4:$AH,Week3_Stats!$B4:$AH,Week4_Stats!$B4:$AH,Week5_Stats!$B4:$AH,Week6_Stats!$B4:$AH,Week7_Stats!$B4:$AH,Week8_Stats!$B4:$AH,Week9_Stats!$B4:$AH,Week10_Stats!$B4:$AH},"Select Col2 Where Col3 ='ADC' and Col5 > .5 Order by Col5 Desc Limit 10"))
You've got a number of problems in your formula. First, you've used commas to separate your array of ranges for QUERY to process; but commas signify placing an element to the right of the previous element. You need semicolons to stack those ranges one above the other*.
You're also asking QUERY to LIMIT results before UNIQUE is applied. This would leave you with 10 items, some of which may be repeats, which are then reduced to unique items; whereas I assume you want the final list of unique entries to be 10.
You also need to add WHERE Col2 Is Not Null to your QUERY conditions, because a blank entry counts as UNIQUE unless ruled out.
Try the following in place of your present formula in 'THE ADCs'!E11 and see if it produces the result you were expecting:
=ArrayFormula(ARRAY_CONSTRAIN(UNIQUE(QUERY({Week1_Stats!$B4:$AH;Week2_Stats!$B4:$AH;Week3_Stats!$B4:$AH;Week4_Stats!$B4:$AH;Week5_Stats!$B4:$AH;Week6_Stats!$B4:$AH;Week7_Stats!$B4:$AH;Week8_Stats!$B4:$AH;Week9_Stats!$B4:$AH;Week10_Stats!$B4:$AH},"Select Col2 Where Col2 Is Not Null and Col3 ='ADC' and Col5 > 0.5 Order by Col5 Desc")),10,1))