1

I have a spreadsheet that I use for my World of Warcraft guild, and I've hit a bit of a wall while trying to add new functionality to it.

For reference, when our guild goes through dungeons, we fairly distribute items that drop based on a 'plus count' system. That is, everyone starts at 0 and can roll dice against other 0s to try to win an item. If someone wins something, they become +1 and can then only roll against other +1s, so one person doesn't win everything just because they're luckier.

So, we have a sheet that logs every single item that drops, who won it, where it was won and if it counts to the +1 system (there are some items that are not worth counting for). Next to the item log, there's a query that displays what everyones plus count is at automatically, which looks like this:

Loot Tracker
--------------
Name     Count
--------------
Jim      2
Bob      1
Liam     1
Dave     1
Luke     1

The actual query for that is this, currently:

 =QUERY(A:I,"
    Select D, count(E) 
    where C = 'Victrix' and G = true and A >= date '"&TEXT(IF(WEEKDAY(J1,1)=3,J1,J1-WEEKDAY(J1+4)),"yyyy-mm-dd")&"' and A <= date '"&TEXT(J1+8-WEEKDAY(J1+5),"yyyy-mm-dd")&"' 
    group by D 
    order by count(E) desc, D 
    label D 'Name', count(E) 'Count'
 ")

We're now at a point where people aren't just rolling for their own character, but for their other specializations (something they won't use right away, but no-one else needs it for their main character, so they're rolling for what's called an 'off-spec') and this also needs to be tracked on the loot tracker.

Say I have this data set:

 |  A         B              C      D
----------------------------------------------
1|  Name      Item           +1     Spec
----------------------------------------------
2|  Jim       Lorem          [✓]    MS
3|  Bob       Ipsum          [✓]    MS
4|  Jim       Dolor          [✓]    OS
5|  Liam      Sit            [✓]    MS
6|  Dave      Amet           [✓]    MS
7|  Luke      Consectetur    [✓]    OS

I somehow need a query, or single formula, that makes the loot tracker work like this:

Loot Tracker
-------------------
Name     MS+   OS+
-------------------
Jim      1     1
Bob      1     0
Liam     1     0
Dave     1     0
Luke     0     1

I hope this makes some sense. I have no idea how I'd go about this. I tried separate query formulas on the third column here. However it didn't sort correctly or line up with the names.

It needs to be ordered by MS first, and then OS.

  • Welcome. Please remember that as per site guidelines when an answer addresses your question, you should accept it so others can benefit as well. – marikamitsos Nov 14 at 12:13
1

The following code will give you the exact output you are looking for in your bottom example:

=arrayformula(if(
         query(query({A:A,C:C*1,D:D},"select Col1,sum(Col2) where Col1 <> '' group by Col1 PIVOT Col3",1),"select Col1,Col2,Col3 order by Col2 desc, Col3 desc")="",0,
         query(query({A:A,C:C*1,D:D},"select Col1,sum(Col2) where Col1 <> '' group by Col1 PIVOT Col3",1),"select Col1,Col2,Col3 order by Col2 desc, Col3 desc label Col1 'Name', Col2 'MS+', Col3 'OS+'")))

How the formula works:
1. We multiply column C by 1 to convert the checkboxes to numbers and use the pivot feature in the query to break out the columns the way you want.
2. You then have to wrap the entire thing around another query to sort it the way you want because you can't sort and pivot in the same query with aggregated columns.
3. Then sadly, we wrap the entire thing in an if statement so if there are any blanks we can turn them into zeros.
4. If you didn't care about the blanks the formula would be half as large and you would basically just use the false portion of the if statement.

  • Thank you for the edit. I mixed the columns' letters. Sorry. However. The Name label will not appear unless you put back the label Col1 'WHATEVER' that you erased. – marikamitsos Nov 14 at 13:31
  • @marikamitsos for me it shows name with the exact formula as shown? My test data is including the headers exactly as shown in the original question? – CodeCamper Nov 14 at 14:25
  • You are right. My sheet was "stuck". :( Tested it in another spreadsheet. Works fine. – marikamitsos Nov 14 at 14:52
  • @marikamitsos maybe you didn't have headers in your test data originally or something weird? – CodeCamper Nov 14 at 14:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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