Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!

Subscribe for Free Excel tips & more!

Free Excel Forum

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

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 ...
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 ...
Ignore Blanks in a Data Validation List in Excel
I will show you 3 ways to remove the blanks from a Data Validation dropdown menu in Excel. All 3 methods are multi ...
Sort Data Alphabetically or Numerically in Excel 2007 and Later
This Excel tip shows you how to Sort Data Alphabetically and Numerically in Excel 2007. This is a great feature to ...

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.

Is there a way I can have Excel sort a group of columns with another group and add blank feilds as needed?

Here is what I am doing:
I have one worksheet that has two groups of columns the first group (A-G) has store name, store number, address, city, sate, zip, and phone number.

The second column has store number, and area manager.

I want to be able to sort the columns so the area manager names line up with their associated store number addresses and leave blank cells where there is no area manager for a store, and blank cells where I have no store information.

I have thousands of rows of data to do.

Please see the attached file.


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.

Got two columns one with group name and other with value. given below is the data which i have

Retail group 256 350 120 30 Technology group 245 205 20 245 Wholesale group 243 340 24 450
the green cells are blank , i want them to fill up by the group name i.e first four rows belong to retail group, so i want retail group to be filled in the blank cells upto technology group from thereon technology group to be filled in till whole sale group. hope am able to explain you the situation.

thanx in advance for your help

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!

I have been trying to figure this out for a few days now and need some help! What I want to do is look at one column of data and based on the data in that column, I want to populate another column with a "Yes" I had determined that I need to use the VBA function SPLIT but cannot figure out exactly how to use it in my case.

Data Mapping info
JDoe is in Group 1
MSmith is in Group 2
TDavidson is in Group 3

In column A I have the following types of data which are based on a vlookup to another tab of the spreadsheet.
JDoe, MSmith
TDavidson, MSmith, JDoe

In column B I want a Yes in the column if ANY (only need one) person in column A belongs to Group 1
In column C I want a Yes in the column if any person in column A belongs to Group 2
In column D I want a Yes in the column if any person in column A belongs to Group 3

So column B for the above would
...since JDoe appears in those three rows

I need to split out the contents of cell A. I cannot do text to columns because this is a formula and also I want to keep the spreadsheet dynamic as I am being asked to continually change it.

Any thoughts out there? Thanks,

Is there a way to sort by a column in descending order, but leave the blank rows inbetween categories of items?
Example: Let's say this is a 3 Column spreadsheet. I want to sort by the "C" Column in Descending order but leave the grouping of items and the blank rows where they are. Is there a way to do this without having to highlight each grouping and then sor by the third column. I have a HUGE spreadsheet and don't have a lot of time to be sorting each group.

11232....Wheat Bread...16


Can a macro be written to search a column for blank cells, then in each blank cell, subtract the last cell of the group of data just above the blank cell from the first cell of that same group (there will be a blank cell above that cell as well), keeping in mind that the data groups vary in size (number of rows), and have it do this for each blank cell in the column

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?