CODEWITHSUNDEEP
vbaexcel
wiebersk
wiebersk

Reputation: 23

Sort Excel Grouped Rows

I have a spreadsheet that has information in groups. The header row contain company names and information and then the grouped rows beneath them contain names of people in the company.

Company Name | Number of Employees | Revenue |
Employee Name | Email | Phone

Is there anyway to sort by the number of employees and/or revenue and keep the grouped employee information below the company with the information?

Normally when I try it, it will sort the company information but keep the employee information in the order that it is entered.

Upvotes: 1

Views: 2761

Answers (2)

Avan
Avan

Reputation: 51

Better late than never, I suppose, but I feel my LAselect plugin would have solved your problem. I created this plugin because I do much non-standard 'stuff' with my data and needed a tool to handle it. LAselect can produce your 'group' output too and you would not need hidden columns or anything. I mean, you would not need to change the screens you are used to to sort them in whatever way you wanted.

Upvotes: 0

Robert Groves
Robert Groves

Reputation: 7748

If I understand your question correctly, I have a way you can accomplish what you want (don't know if there is a more efficient method).

Write code which will, for each company header row, copy the number of employess and revenue data into two of the chosen unused columns. The data needs to be copied into the columns for both the header company row and detail employee rows.

In the third column assign a sequence number. This is to keep data together and in order when sorting by employee/revenue.

Now you can sort by either the newly created number of employees and/or revenue columns (along with the sequence column to maintain ordering within company).

After the sort you can delete the extra copied data rows.

So if your data looked like this to start with...

A              B                  C
Penetrode      200                750000
Micheal Bolton [email protected]   555-555-3333
Samir N        [email protected]
Initech        500                500000
Bill Lumbergh  [email protected]  555-555-1212    
Peter Gibbons  [email protected]  555-555-2222

Your code would then copy the employee count and revenue data and sequencify the rows using three unused columns.

A              B                  C             D                  E       F
Penetrode      200                750000        200                750000  1
Micheal Bolton [email protected]   555-555-3333  200                750000  2
Samir N        [email protected]    555-555-3334  200                750000  3
Initech        500                500000        500                500000  4
Bill Lumbergh  [email protected]  555-555-1212  500                500000  5
Peter Gibbons  [email protected]  555-555-2222  500                500000  6

Then you can code a sort on any of the column combos: (D,F), (E,F), (D,E,F), or (E,D,F)

Upvotes: 2

Related Questions