Sorting multiple sub-total groups at once

[Mikalas]

Hello,

I have a spreadsheet that lists companies and their employees and how many calls each makes. Each company is already grouped and subtotaled with the employees and their calls underneath the company header. I have several companies within this sheet. I need to sort the calls by employee within each company from highest to lowest calls. Is there a way to do this for all companies all at once rather than do it by each company and highlight the rows within each company and sort by employee calls? This gets a bit tedious. I was hoping there is a way to do this more efficiently. Thank you!

 

[DanteAmor]

Hi and welcome to MrExcel!

Try this:

Sub Sorting_groups()
  Dim a As Range
  For Each a In Range("A2:A" & Range("C" & Rows.Count).End(3).Row).SpecialCells(xlCellTypeBlanks).Areas
    a.Offset(, 1).Resize(, 2).Sort a.Offset(, 2).Cells(1), xlDescending, , , , , , xlNo
  Next
End Sub

 

[Mikalas]

Thank you for your response! I will try it this weekend. I'm excited that it will work and save me some time!
Michelle

 

[CA_Punit]

You can use Power Query to Sort Data

Example which i have used: Unformated Data

Book1
	B	C	D
8	Company Name	Technician	Calls per Day
9	A	X	10
10	A	X1	2
11	A	X2	15
12	B	Y	13
13	B	Y1	14
14	B	Y2	10
15	C	Z	4
16	C	Z1	2
17	C	Z2	6
Sheet1

Which i have formatted to the following using Power Query

Book1
	A	B	C
1	Company Name	Technician	Calls per Day
2	C	Z2	6
3	C	Z	4
4	C	Z1	2
5	B	Y1	14
6	B	Y	13
7	B	Y2	10
8	A	X2	15
9	A	X	10
10	A	X1	2
Sheet2

 

[DanteAmor]

Thank you for your response! I will try it this weekend. I'm excited that it will work and save me some time!
Michelle

The macro assumes that the first employee is in cell A2 and the other data is in columns B and C.