I saved your xlsx and then linked to it.
Then modified the SQL to use the linked Excel file and created a new query
The SQL
Code:
SELECT t1.LOCATION, t1.DEPT,t1.[DEPT DESC],t1.[SumOfQTY SOLD], t1.[SumofNet SALES]
FROM [TOP SELLERS Query] AS t1
WHERE (
(
(t1.[SumofNet SALES]) IN (
SELECT TOP 20 T2.[SumofNet SALES]
FROM [TOP SELLERS Query] AS T2
WHERE T2.LOCATION = T1.LOCATION
)
)
)
GROUP BY t1.LOCATION, t1.DEPT,t1.[DEPT DESC], t1.descr,t1.[SumOfQTY SOLD], t1.[SumofNet SALES]
ORDER BY t1.location, t1.[SumofNet SALES] desc
The result:
LOCATION |
DEPT |
DEPT DESC |
SumOfQTY SOLD |
SumofNet SALES |
DFW |
60 |
Specialty Apparel |
9 |
$299.20 |
DFW |
60 |
Specialty Apparel |
2 |
$281.20 |
DFW |
60 |
Specialty Apparel |
3 |
$237.00 |
DFW |
61 |
Specialty Accessories |
3 |
$209.60 |
DFW |
60 |
Specialty Apparel |
2 |
$204.80 |
DFW |
60 |
Specialty Apparel |
6 |
$200.60 |
DFW |
60 |
Specialty Apparel |
2 |
$168.00 |
DFW |
60 |
Specialty Apparel |
2 |
$148.00 |
DFW |
60 |
Specialty Apparel |
4 |
$144.00 |
DFW |
60 |
Specialty Apparel |
1 |
$138.00 |
DFW |
60 |
Specialty Apparel |
2 |
$136.00 |
DFW |
60 |
Specialty Apparel |
3 |
$132.00 |
DFW |
60 |
Specialty Apparel |
3 |
$132.00 |
DFW |
60 |
Specialty Apparel |
2 |
$116.00 |
DFW |
60 |
Specialty Apparel |
2 |
$116.00 |
DFW |
60 |
Specialty Apparel |
4 |
$116.00 |
DFW |
60 |
Specialty Apparel |
2 |
$104.40 |
DFW |
60 |
Specialty Apparel |
1 |
$98.00 |
DFW |
60 |
Specialty Apparel |
1 |
$98.00 |
DFW |
60 |
Specialty Apparel |
2 |
$96.00 |
HRH |
61 |
Specialty Accessories |
2 |
$285.50 |
HRH |
60 |
Specialty Apparel |
1 |
$224.00 |
HRH |
60 |
Specialty Apparel |
2 |
$196.00 |
HRH |
60 |
Specialty Apparel |
2 |
$156.00 |
HRH |
60 |
Specialty Apparel |
1 |
$145.00 |
HRH |
60 |
Specialty Apparel |
1 |
$138.00 |
HRH |
60 |
Specialty Apparel |
2 |
$128.00 |
HRH |
60 |
Specialty Apparel |
1 |
$118.00 |
HRH |
60 |
Specialty Apparel |
2 |
$116.00 |
HRH |
60 |
Specialty Apparel |
1 |
$98.00 |
HRH |
61 |
Specialty Accessories |
1 |
$94.00 |
HRH |
61 |
Specialty Accessories |
2 |
$91.20 |
HRH |
60 |
Specialty Apparel |
1 |
$89.50 |
HRH |
60 |
Specialty Apparel |
1 |
$88.00 |
HRH |
60 |
Specialty Apparel |
1 |
$88.00 |
HRH |
60 |
Specialty Apparel |
1 |
$88.00 |
HRH |
60 |
Specialty Apparel |
1 |
$88.00 |
HRH |
60 |
Specialty Apparel |
1 |
$79.00 |
HRH |
61 |
Specialty Accessories |
1 |
$78.40 |
HRH |
60 |
Specialty Apparel |
1 |
$78.00 |
HRH |
60 |
Specialty Apparel |
1 |
$78.00 |
IAH |
60 |
Specialty Apparel |
3 |
$400.20 |
IAH |
60 |
Specialty Apparel |
2 |
$250.80 |
IAH |
60 |
Specialty Apparel |
3 |
$218.40 |
IAH |
60 |
Specialty Apparel |
2 |
$196.00 |
IAH |
61 |
Specialty Accessories |
4 |
$187.20 |
IAH |
60 |
Specialty Apparel |
1 |
$168.00 |
IAH |
60 |
Specialty Apparel |
3 |
$162.00 |
IAH |
60 |
Specialty Apparel |
2 |
$150.10 |
IAH |
60 |
Specialty Apparel |
1 |
$138.00 |
IAH |
60 |
Specialty Apparel |
2 |
$136.00 |
IAH |
60 |
Specialty Apparel |
2 |
$129.20 |
IAH |
61 |
Specialty Accessories |
3 |
$126.00 |
IAH |
60 |
Specialty Apparel |
1 |
$118.00 |
IAH |
60 |
Specialty Apparel |
2 |
$115.20 |
IAH |
60 |
Specialty Apparel |
3 |
$110.20 |
IAH |
60 |
Specialty Apparel |
1 |
$108.00 |
IAH |
60 |
Specialty Apparel |
1 |
$108.00 |
IAH |
61 |
Specialty Accessories |
3 |
$108.00 |
IAH |
61 |
Specialty Accessories |
1 |
$108.00 |
IAH |
60 |
Specialty Apparel |
2 |
$102.60 |
LAS |
60 |
Specialty Apparel |
2 |
$196.00 |
LAS |
60 |
Specialty Apparel |
2 |
$156.00 |
LAS |
60 |
Specialty Apparel |
3 |
$108.00 |
LAS |
60 |
Specialty Apparel |
1 |
$98.00 |
LAS |
61 |
Specialty Accessories |
1 |
$98.00 |
LAS |
60 |
Specialty Apparel |
2 |
$91.20 |
LAS |
60 |
Specialty Apparel |
1 |
$88.00 |
LAS |
60 |
Specialty Apparel |
1 |
$88.00 |
LAS |
61 |
Specialty Accessories |
1 |
$88.00 |
LAS |
60 |
Specialty Apparel |
1 |
$84.00 |
LAS |
60 |
Specialty Apparel |
2 |
$84.00 |
LAS |
60 |
Specialty Apparel |
1 |
$76.00 |
LAS |
60 |
Specialty Apparel |
1 |
$68.00 |
LAS |
61 |
Specialty Accessories |
3 |
$65.00 |
LAS |
60 |
Specialty Apparel |
2 |
$64.00 |
LAS |
61 |
Specialty Accessories |
1 |
$61.20 |
LAS |
60 |
Specialty Apparel |
1 |
$58.00 |
LAS |
60 |
Specialty Apparel |
2 |
$58.00 |
LAS |
60 |
Specialty Apparel |
1 |
$58.00 |
LAS |
60 |
Specialty Apparel |
1 |
$49.00 |
LAX |
61 |
Specialty Accessories |
11 |
$418.00 |
LAX |
60 |
Specialty Apparel |
16 |
$352.00 |
LAX |
61 |
Specialty Accessories |
8 |
$280.80 |
LAX |
60 |
Specialty Apparel |
4 |
$270.00 |
LAX |
60 |
Specialty Apparel |
4 |
$270.00 |
LAX |
60 |
Specialty Apparel |
11 |
$261.60 |
LAX |
60 |
Specialty Apparel |
3 |
$256.00 |
LAX |
60 |
Specialty Apparel |
5 |
$250.80 |
LAX |
60 |
Specialty Apparel |
6 |
$240.00 |
LAX |
60 |
Specialty Apparel |
6 |
$232.00 |
LAX |
61 |
Specialty Accessories |
6 |
$228.00 |
LAX |
60 |
Specialty Apparel |
3 |
$220.00 |
LAX |
60 |
Specialty Apparel |
5 |
$216.00 |
LAX |
61 |
Specialty Accessories |
5 |
$210.00 |
LAX |
60 |
Specialty Apparel |
6 |
$204.00 |
LAX |
60 |
Specialty Apparel |
3 |
$196.00 |
LAX |
61 |
Specialty Accessories |
5 |
$190.00 |
LAX |
60 |
Specialty Apparel |
8 |
$187.20 |
LAX |
60 |
Specialty Apparel |
4 |
$181.30 |
LAX |
60 |
Specialty Apparel |
5 |
$170.00 |
SFO |
60 |
Specialty Apparel |
3 |
$284.20 |
SFO |
60 |
Specialty Apparel |
4 |
$232.00 |
SFO |
60 |
Specialty Apparel |
2 |
$196.00 |
SFO |
60 |
Specialty Apparel |
1 |
$188.00 |
SFO |
60 |
Specialty Apparel |
1 |
$168.00 |
SFO |
60 |
Specialty Apparel |
2 |
$158.00 |
SFO |
60 |
Specialty Apparel |
2 |
$136.00 |
SFO |
60 |
Specialty Apparel |
2 |
$116.00 |
SFO |
60 |
Specialty Apparel |
2 |
$116.00 |
SFO |
60 |
Specialty Apparel |
2 |
$116.00 |
SFO |
60 |
Specialty Apparel |
1 |
$108.00 |
SFO |
60 |
Specialty Apparel |
1 |
$108.00 |
SFO |
61 |
Specialty Accessories |
3 |
$108.00 |
SFO |
61 |
Specialty Accessories |
1 |
$108.00 |
SFO |
60 |
Specialty Apparel |
1 |
$98.00 |
SFO |
60 |
Specialty Apparel |
1 |
$98.00 |
SFO |
60 |
Specialty Apparel |
1 |
$98.00 |
SFO |
60 |
Specialty Apparel |
1 |
$98.00 |
SFO |
60 |
Specialty Apparel |
1 |
$98.00 |
SFO |
60 |
Specialty Apparel |
2 |
$96.00 |
SFO |
60 |
Specialty Apparel |
2 |
$96.00 |