Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!

Group Sorting Or Automatic Grouping

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Hi Guys,

Does anyone know if one or both of the following are possible:

Scenario 1; Working with Sheet one there is data in Columns A - O
In Column A are names and column C different number


Bob "Blank" 1435
Bob "Blank" 6548
Angela "Blank" 6849
Jason "Blank" 1235

every person that has 2 or more numbers in C are Grouped together (Data > Group and OUtline > Group)

But as per the example above the names are not in alphabetical order.
Is there a way of sorting these groups, or single rows if not grouped) while retaining the contents in the group?

If Scenario above is not possible:

Bob "Blank" 1435
Bob "Blank" 6548
Angela "Blank" 6849
Jason "Blank" 1235

Before grouping the above, it is sorted alphabetically based on Column A
Is there a macro that can group rows based on the values in A?

Any ideas would be most appreciated.


View Answers     

Similar Excel Tutorials

Group Data Together for Increased Readability in Excel
How to group data together or collapse it in order to focus only on the important data in Excel. This allows you t ...
Stop Formula Calculation in Excel - Increase Worksheet Performance
I'll show you how to stop formulas and functions from performing their calculations and updating in Excel. This ma ...
Sorting Data by Date, Text, or Number in Excel
Sorting Data can be done with a few quick clicks of the mouse. I have used the same data as the previous 2 tutorial ...
Sort Data Left to Right in Excel
How to sort columns of data in Excel.  This is the same as sorting left to right. This will change the position of ...

Helpful Excel Macros

Delete Blank Rows in Excel
- This is a macro which will delete blank rows in excel. This version will delete an entire row if there is a blank cell
Delete Rows in Excel if Completely Empty
- This macro will delete only completely blank rows in an excel spreadsheet. It allows you to make a selection of rows, ru
Delete Hidden Rows in a Workbook
- This macro will delete hidden rows from every worksheet in an entire workbook. However, note that this will only delete
Sort Data that Doesn't Have Headers in Ascending Order in Excel
- Sort data that doesn't have headers in ascending order in Excel with this macro. This is a simple sort macro that assum
Sort Data that Doesn't Have Headers in Descending Order in Excel
- Sort data in Excel that doesn't have headers using a macro. This Excel macro assumes that your data set does not have a

Similar Topics

Hello everybody!

I have an excel sheet that has 180 groups. Each group is seperated with 2 blank rows. I would like to have each group sorted in 4 parts and have a blank row between each splitted group.

will look like this

blank row
blank row
blank row
blank row
blank row
blank row
blank row
blank row
blank row

First post and total newb to VBA, so please bear with me. I did search this forum and found a few similar threads that would likely have worked if I knew how to adjust VBA coding beyond the bare minimum... but I don't.

I am trying to use VBA to group rows according to the contents in specific columns. The process can easily be done manually, but the content will change and I want future users to group the rows as they were intended to be grouped.

These are the steps that need to be completed with the VBA code:

1. Remove all previous grouping (to prevent unintentional sub-groups - may be an unnecessary step as I am not sure how the VBA code will treat existing groups)
2. Group the first level based on column C
1. I need the code to start at cell C11 then go down the rows, find a non-blank cell (example: C12), move from that cell until it reaches the next non-blank cell (example: C34), then group all rows between C12 and C34 (again, these are example cells) EXCEPT for the row immediately above the second non-blank cell (in this example, row 33) - in summary, this example would have resulted in rows 13-32 being grouped, then continuing on to the next section
2. When the code is finished doing its thing, the closed-group result should have alternating blank and non-blank cells in column C, all the way down to the end of the data, with the final group including the entries below the final non-blank cell in C (now that I think about it, it would probably be easier to start at the bottom and work up, but I'll leave that to people who know what they're talking about
3. Group the second level based on columns D-H
1. Similar to the above, except the sub-groups need to be based on column D instead of C. Column C labels the overall categories, then D labels the sub-categories. Everything in columns E-H that it is under an entry in column D will need to be grouped
2. Like the first-level grouping, the sub-groups will require a row above and below them, such that there will be alternating blank and non-blank cells in column D when all the groups are closed
3. Continuing the above example, let's say that cells D14, D24, and D31 all have content. If the code were correct, the first level of grouping would have rows 13-32 grouped, and the second level would group rows 15-22, 25-29, and 32 (if the cells in columns E-H were were empty beyond row 32 in this first segment)

Hopefully that makes sense. If not, I would be happy to clarify. Or is there a way to post an example image or spreadsheet?

Thanks in advance for any help!


First off, I love this site!

Right now I have data listed in column A. The data is in 'groups', separated by a blank row. I need to transpose the data in each 'group' onto their own row. The problem is, each group will have a different number of rows.

A1 through A7 is one 'group'. Then there is a blank row. Then A9 through A12 is another 'group'. Then another blank row and so on. There are thousands of 'groups'.

What i need is A1 through A7 to be transposed to A1, B1, C1, D1, E1, F1, G1.
A9 through A12 would be transposed A2, B2, C2, D2 and so on.

I can't find nor create a macro that can account for a different number of cells in each group, and that can start a new row based on a blank cell. Any help is appreciated.

Thank you!

I have an excel document with rows of data going down column A. The first 10 rows have names of fruits. Then there is a blank row after that. The following 10 or so rows have more names of fruit. This is also followed by a blank row. The following 10 or so rows also have names of fruit followed by a blank row and so on and so on.
So here is the problelm I am having. I am trying to come up with a macro to sort the first group of rows in ascending order until I hit the blank row. Then sort the next group of rows until I hit the blank row. I want to do this until I reach the very last row containing data.

Sorting the data is easy enough. But the macro for selecting which group of rows needs to be sorted is the hard part. Any ideas?


i have a 5 by 10 table (5 columns 10 rows)
values only equal 1 or blank
i want to re-order this table in another identical table so that no row will have more than one value (each row will have a maximum of one "1")

currently, the table looks something like this:

blank blank blank 1 1
blank blank blank blank 1
blank blank blank blank 1
blank blank blank blank blank
blank blank blank blank blank

what i would like the idential table to show is:

blank blank blank 1 blank
blank blank blank blank 1
blank blank blank blank 1
blank blank blank blank 1
blank blank blank blank blank

does that make sense? (similar logic with more 1s through-out)

Great forum, lots of smart fellas, nice change!

My sheet shows under columns A to K data for groups of invoices, under K the $ amount of the invoice.
When I recieve the sheet, info is in groups (according to type of expense), separated by a blank row. Might be a single row, or 40 rows, with a blank row when changing accounting category.
Currently I have to manually go to a blank row, position in column K, and place sum for whatever rows above belong to that group. Then go to the next one and repeat.
Size of each group will vary from one day to the other, but there is always a blank row separating.
I'm new to this and can't figure a VBAcode that sums what is in between blanks, and stops once there is more than 1 blank row.
Beforehand, thanks for help.


I have a spreadsheet that has data that is grouped together in one column and is separated from the next group by a blank row. The problem is, I need these "groups" to be transposed into rows so I can use these data. I have generated a macro that will transpose my selection into the appropriate place, but I am hoping there is a way to do this automatically as my spreadsheet has about 4000 groups of data with 7 or 8 rows in each group. I know it would be easier if they had a standard number of rows per group, but I don't just don't know how to utilize the "separated by a blank row" part.

Any ideas?

Thanks for your help.

I need a macro to group rows based on values in a specific column.

I have a dashboard with data from row 21 to row 5000. The cells in column GM have the following values: "Hide", "Not Hide", or are blank. The data is sorted based on values in columns A, B, and C. As a result, there is no inherent order to the "Hide" or "Not Hide" values in the cells in column GM.

I need a macro to go down column GM, and group any row where the value in column GM is "Hide". In some cases, only a single row will be grouped with a "+" sign. In other cases, consecutive rows will be grouped as such.

I want to go with grouping rather than filtering because with grouping, data hidden along the range of the dashboard is more readily accessible.

Any help would be greatly appreciated.

Thank you very much.

Good Morning!

I am trying to group some people together by their age. I want to group anyone from 18-24, 25-29 etc. So I want to enter a blank row at the end of the ages (blank row after last 24, another after the last 29 etc.)
I found some code but it's putting a blank row in between each of the specified values, (row between EVERY 24, 29 etc)
Then I want to add some totals for each of those "groups" but I think I've got that part down....

Thank you in advance for any help....

Hi there,

I have a report that comes from SAP, and as usual it's a total mess.

All of the data is grouped by SKU into 3 (or more) rows, then a blank row and the next grouping.

I want to search through the data and pick out a group that has a Forecast. Then look at that forecast and see if it says 0. If it is 0 delete that 'group', otherwise keep the whole group.

If the group does not have a forecast in then delete it.

Here is a screenshot of the current file:

So in the end, you will only be left with 1 group (in this example).

I have a total of 2,000 rows to sort through, so alot of 'groups'.

Any ideas?

Many thanks,


I have a spreadsheet that requires grouping at two levels. See pasted image below.

I need a macro that on the first pass dynamically creates groups of different sizes of data based on values of one column. For example, a group is created for the RR's, a group is created for the SS's, and a group is created for TT's.

On the second pass I would like to group the groups based on the values of another column for the 2nd level of grouping. So the A's would be one group and the B's would be the other.

TotalA A ToTalRR A RR A RR A RR A ToTalSS A SS A SS A SS A TotalTT A TT A TT A TT A TotalB B TotalU B U B U B TotalV B V B V B V B V B TotalW B W B W B W B

I can have 2 tables set up for the values that I need grouped at each level.

Table 1 Table 2 Group 1 Group 2 A RR B SS C TT U V W X Y

I had recorded a macro where I manually grouped the data. However, this did not help, because I am creating a template that needs to dynamcally handle the data that comes in.

I have a large excel sheet that will take a lot of time grouping it manually, so I was wondering if there was some way somebody could help me out with creating a code in VB to automate this grouping for me. All I would need is something to read through the rows of one column and when it finds a blank row, keep scanning until the next blank row and then group the data between the two blank rows. I have attached a small portion of the excel sheet I am working with if you need to get a visual of what I am needing.

Thanks for your help!

Good afternoon everyone.

I have a spreadsheet with data grouped, and there are 2 blank rows in between each group. How can I write a macro that would delete 1 blank row where there are 2?

Thanks for any help!


hi there.
firstly apologies to any one i may annoy or offend during the course of this question, i am a first timer to this forum!

I am wanting to group random numbers into 4 different groups, "Group 0", "Group 1", "Group 2", "Group 3".

I have the random numbers in an excel spread sheet. They range from 0-36.

I would like an "automatic" system that can group the number 0 into "Group 0"..... group the numbers 1-12 into "group 1".... group the numbers 13-24 into "Group 2".... and group the numbesr 25-36 into "Group 3"..

I would like the outcome to look something like this;

Column 1 Column 2
(number) (group)
24 2
13 2
6 1
34 3
36 3
25 3
22 2
1 1
0 0
34 3
23 2
16 2
17 2
3 1
19 2

Your help would be greatly appreciated.

I am trying my best to write a pattern in VBA, however, it's pretty much telling me that I'm not even writing a

So here is my goal. I have everything in one column (A). I have 5 rows which must be kept together and in order (group them) then a blank row, then 5 rows which must be kept together and another blank get the picture. This goes on for several thousand rows.

I'm trying to get a variable to countdown until i have no more populated "5 rows".



If someone can help me start this, I can finish it. Thanks.


Hello All,

My appolgies for posting twice, but I do not know how to delete my other post.

I have been trying to create a formula for automatic list that will restart after some blank lines.
I am using Excel 2003 and and am using the List feature (Data > List > Create list...), which by default requires headings before there is the data.

This how it would look:
Column A data; Column B data (group headings start with PN); Column C ID number; Column D automatic numbering.
Row 1 -- List headings for entire list
Row 2 -- Group heading: i.e PN 2347 (Column B will always have PN [Plan Number] folllowed by some numbers. Columns A, C will be blank (No list numbering or data).
Row 3 -- Data (Automatic numbering should identify this as '1')
Row 4 -- Data (Automatic numbering should identify this as '2')
Rows 5 & 6 blank
Row 7 -- Group heading (no numbering).
Row 8 -- Data (Automatic numbering should identify this as '1')
Row 4 -- Data (Automatic numbering should identify this as '2')
And so on.....

Essentially there will be a group heading that does not require numbering.
Below that will be varying rows of data that requires automatic numbering.
Below this will be one or more blank rows.
Previous items repeat.

There will always be data in column B so I tried to use an IF function against this column. The problem I came across is that a 'list' requires a heading before my group heading i.e. my group heading in column B has a list heading on the next row above it.
The only thing in common is that each of the sub group headings will start with PN.

Thanking you all in advance and hope you enjoy the festive season.

Dave T

Hi, guys.
First time poster, long time viewer. Big fan!
im having a column of empty cells and some numbers. Like you see underIt goes on for hundreds of lines.


I want to make a shorter version og column A in column B where all the blanks are removed. Like this:
<blank> 4
<blank> 15
<blank> 8

Does anyone know witch set of formulas i can use to get this done in excel 2010?????


I have numbers in column D grouped together, seperated by blank rows. I need the average calculated for each "group" if it is followed by a blank row. The amount of numbers in each "group" varies.


I have to columns

Column H = Department
Column I = Group

Some cells in column H are blank, some cells in column I are blank. I want to test for blanks and:

1. If cell in column H not blank, show contents, else "No Department"
2. If cell in column I not blank, show contents, else "No Group"
3. If both cells in columns H and I blank, show "No Data"

I have written the formulation below, individual parts work but not this combination, ie results column J, Union. Would appreciate any help to understand what I am doing wrong when combine.

=IF(H2="","No Dept",IF(I2="","No Group",IF(H2:I2="","No Data",CONCATENATE(H2,": ",I2))))

Department Group Union
Big Small #VALUE!
Hi Low #VALUE!
Black White #VALUE!
Oil Water #VALUE!

I have a spreadsheet containing groups of rows with multiple columns in Excel 2002 Each or these groups of rows & columns contain descriptors of a certain item. Each group is also the same number of rows and columns and the data in each cell within the rows & columns for a group is structured in a similar fashion from grouping to grouping.

I'd like to be able to sort by a group of rows based on certain criteria. I know the first thing someone is going to suggest is to put this into a database, but I can't go down that path yet. So, I'm wondering if there is anyway to tell Excel how to do this sorting by groups.

Any ideas?

I've reviewed other posts and tried everything but I can't figure out how to group my data by date range. Col A lists a/c numbers, Dates are across the top and values in the data values section of the Pivot table. I've added zero values for blank cells and I've sorted by dates. There are no blank rows or cols and I'm at a loss to know how to be able to group by month/quarter.
Any help much appreciated!

Please see the below image of a spreadsheet i'm working on.
There's roughly 4000 rows of data and if there's not a quick fix with VBA then i'm going to be spending hours manually adding rows.

Column B houses the Group Code, what I need to do is add 2 blank rows between each group code making sure the data stays relevant to the code it is next to currently.

So looking at the pic below, I would want something like this:


and so on.

My question is, is there something which will do that for me instead of me manually looking for the change in Code and then inserting rows one by one?

Please if you need more info, I will happily supply it!


I am trying to group by dates (month/quarter) using a dynamic Pivot Table,

I had it up and running until a blank date field was entered.

Any ideas on how i can resolve this?

I have tried filtering the data to not include blank fields but then i get 'cannot group that selection'....frustrating!


Hi everybody!

I have several groups of which i need to calculate the averages. Each group contains several data. Between each group i have a blank row. So for example. Data from B25-32. Then blank row. Then B34-b95. etc. The amount of numbers in a group change everytime. I would like to have the averages of each group in the cell next to the first number of group. So in this case i would like to have the average in cell a25, Cell a34, etc.

Can anyone help me with this. I have 180 groups and then 11 different sheets. So i am in desperate need for some help. Could someone please help me?

Thx for the help!

I have a dataset in excel with heading in first row and repetitive data in groups next rows.
The first column contains month1 month2 month3 month4 total and total

I want to insert a blank row after last total of each group and column headings after that
so I want to insert a blank row and column headings after last total row of first group and before the row starting with month1 column.

How can I write a macro to insert a blank row and column heading in vba?

Thanks in advance