Email:      Pass:    Pass?
Close Window   
TE
Subscribe for Email Updates!
Excel tips, help, and more!
E-mail:


Advertisements


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

e.g

A B C
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:

A B C
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.

Thanks.
/Comf


Similar Excel Video Tutorials

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 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
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
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
1
2
blank row
55
3
blank row
2
987
blank row
3
-4
blank row
blank row
67
67
blank row
2
^5
blank row
etc....


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!


Hello,

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!
Jason


Hello.
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?

Thanks,
Ray


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.


Hello!

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.


Hi!

I have an excel sheet with 180 groups.. Between each group i have a blank row. I would like to double that blank row. So i get 2 blank rows between each group. Does someone have a macro for this?


If anybody can help me with a slightly complicated problem regarding pivot tables it would be GREATLY appreciated!!!

I am trying to find a way to have my pivot table display data shared by multiple groups without displaying a "blank" for the groups that do not have data (because the data is already being shown for the group?

Example:



Data sheet view
% Spent

86.71%

27.34%





47.16%








93.69%


99.75%

0.00%

49.52%


20.66%



0.00%


0.00%

0.00%


22.30%

35.08%


100%

*Note the difference in spacing is due to the fact that there are multiple groups represented (bigger spacing = group or 2 or more)

Pivot table view

% Spent
86.71%


0.00%
(blank)

35.08%
(blank)

100%

22.30%


93.69%
(blank)

20.66%
(blank)

0.00%

0.00%

99.75%

0.00%
(blank)

(blank)
49.52%


27.34%
(blank)

(blank)
47.16%
(blank)
(blank)
(blank)

*Note the blanks represent where there is not data due to the fact the data is already represented on one line for the entire grouping causing the remainder of the group to show as blanks.

I have tried merging cells into one for each group but had no success. Help!!


UPDATE

I managed to find somewhat of a workaround to the problem by adding quotes in the blank cells. It adds dashes ex. -- , but looks much better than those nasty little "(blank)s" that were showing up before. If anbody can think of something else I'd be more than happy to hear.....


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.

THANKS!


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,

James


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.


Need help with a Macro for Excel 2003. I have a chart with 10 columns and hundreds of rows. Each time I import the file, the values change. I know how to create a macro to sort the rows based on one of the columns values - lets say column A - in ascending order. So now the chart has groups of rows where the value for column A is the same (in each group). After the chart is grouped, I want to use a macro to insert a # of blank rows beneath each section of similar column A names, then total one of the columns. In effect, breaking up the chart into sections where the column A name identifies each section. Thanx.
-Ken


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 a spreadsheet with groups of data:

Name1 135
Name2 35
Name3 476
Name4 794
Name5 235
Name6 635
Name7 478
Name8 294

Name9 135
Name10 35
Name11 476
Name12 794

Name13 135
Name14 35

Name15 135
Name16 35
Name17 476

etc. through about 4500 rows

At each blank row, I need to put a formula in column "C" which will sum the first 4 values in each group of column "B" (but not getting values from the next group if the group only has 1 or 2 entries).

If I can get the formula, copying down is not a problem as the spreadsheet is grouped and I can collapse it to do the copy/paste.

Any help would be greatly appreciated!


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.
--blank--
JDoe, MSmith
JDoe
TDavidson, MSmith, JDoe
MSmith

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
--blank--
Yes
Yes
Yes
--blank--
...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,
Jenna


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.

---a---------b------------c-------
11122....Cheese.......13
11122....Milk............49
11356....Cereal........08
BLANK ROW
26956....Grapes........16
11569....Peaches.......49
11333....Oranges.......22
11323....Avocadoes...08
BLANK ROW
29160....Bread..........05
11232....Wheat Bread...16

Thanks!!


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


I have an instance where I have a list of numbers in different rows with different amounts and I want to add up the last 6 numbers, they're all in the same column however they're not all in consecutive rows.

Also, the gaps between the rows is not consistent.

The example below explains what I mean (I've add numbers in between so you can see the varying gaps, but they're actually blank cells)

Quote:

(B1)ADD ME = 2
(B2)**blank cell**
(B3)**blank cell**
(B4)**blank cell**
(B5)**blank cell**
(B6)ADD ME = 5
(B7)**blank cell**
(B8)**blank cell**
(B9)**blank cell**
(B10)**blank cell**
(B11)**blank cell**
(B12)**blank cell**
(B13)**blank cell**
(B14)**blank cell**
(B15)**blank cell**
(B16)**blank cell**
(B17)**blank cell**
(B18)ADD ME = 2
(B19)**blank cell**
(B20)**blank cell**
(B21)**blank cell**
(B22)**blank cell**
(B23)**blank cell**
(B24)**blank cell**
(B25)**blank cell**
(B26)**blank cell**
(B27)**blank cell**
(B28)**blank cell**
(B29)**blank cell**
(B30)**blank cell**
(B31)**blank cell**
(B32)ADD ME = 5
(B33)ADD ME = 3
(B34)**blank cell**
(B35)**blank cell**
(B36)**blank cell**
(B37)**blank cell**
(B38)**blank cell**
(B39)**blank cell**
(B40)**blank cell**
(B41)**blank cell**
(B42)**blank cell**
(B43)**blank cell**
(B44)**blank cell**
(B45)**blank cell**
(B46)ADD ME = 0
(B47)**blank cell**
(B48)**blank cell**
(B49)**blank cell**
(B50)**blank cell**
(B51)**blank cell**
(B52)**blank cell**
(B53)**blank cell**
(B54)**blank cell**
(B55)**blank cell**
(B56)**blank cell**
(B57)**blank cell**
(B58)**blank cell**
(B59)**blank cell**
(B60)ADD ME = 1
(B61)**blank cell**
(B62)**blank cell**
(B63)**blank cell**
(B64)**blank cell**
(B65)**blank cell**
(B66)**blank cell**
(B67)ADD ME = 5

What code can I add to say "add up the last 6 numbers containing values"?


hi all,

What I would ideally like is to have some code that can look through a entire column of data and highlight all values that are grouped together. When I say grouped together I have a w/sheet with data in and it is broken up by rows to group data. There are 1000's of groups/rows of data which can be 1 line, 2 line, 3lines+. Each group of data is broken up with a blank row each side (top, bottom) to ensure it stands out, and for further calculation.

So what the macro needs to do is look at the data and determine when there is a group of data with more than one line, when it finds a group of data with more than one line before a clear line then the group/rows of data shold be highlighted with a colour.

I understand that this may be a big ask, becasue it sounds very complex but I would be very greatful for all help/advice given because this wold save me many hours each month.


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!

-scott


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.