Sort multiple data sets in the same sheet

[kapperson]

Hello,
I've done some searching and haven't been able to find a way to do this yet.
I have multiple data sets (formatted as tables but not true Excel tables) that have the same header in Column B and a Total column to signify the top row and the last row.
I'm trying to find a way to code a macro that goes through and sorts all of these tables independently based on the values of a different column - so in the sample file below, this would be column "4."

Table Header	1	2	3	4
A	5	J	75	375
B	10	K	500	2500
C	20	I	30	150
D	30	P	27	135
E	40	O	47	235
F	50	S	5	25
Total	155	-	684	3420
Table Header	1	2	3	4
B	100	R	7	35
C	200	S	25	125
A	300	A	13	65
F	400	Y	19	95
D	500	X	54	270
E	600	Z	658	3290
G	700	K	26	130
H	800	T	95	475
Total	2100	-	776	4485

<colgroup><col><col span="4"></colgroup><tbody>
</tbody>

The number of rows in the table will not always be the same, and there will likely be more added over time, so variable references would be ideal.
I assume what the macro will have to do is loop through the cells and find the header, the total, and then sort the columns in between, I'm just not entirely sure how to code for that.

Any help would be appreciated!

 

[hiker95]

kapperson,

Here is a macro solution for you to consider that is based on your flat text display.

You did not say how you wanted each area sorted.

The macro will sort the data (now) ascending in the column indicated by 4.

Sample raw data in the active worksheet:

Excel 2007
	A	B	C	D	E	F	G
1		Table Header	1	2	3	4
2		A	5	J	75	375
3		B	10	K	500	2500
4		C	20	I	30	150
5		D	30	P	27	135
6		E	40	O	47	235
7		F	50	S	5	25
8		Total	155	-	684	3420
9
10
11		Table Header	1	2	3	4
12		B	100	R	7	35
13		C	200	S	25	125
14		A	300	A	13	65
15		F	400	Y	19	95
16		D	500	X	54	270
17		E	600	Z	658	3290
18		G	700	K	26	130
19		H	800	T	95	475
20		Total	2100	-	776	4485
21
Sheet1

And, after the macro:

Excel 2007
	A	B	C	D	E	F	G
1		Table Header	1	2	3	4
2		F	50	S	5	25
3		D	30	P	27	135
4		C	20	I	30	150
5		E	40	O	47	235
6		A	5	J	75	375
7		B	10	K	500	2500
8		Total	155	-	684	3420
9
10
11		Table Header	1	2	3	4
12		B	100	R	7	35
13		A	300	A	13	65
14		F	400	Y	19	95
15		C	200	S	25	125
16		G	700	K	26	130
17		D	500	X	54	270
18		H	800	T	95	475
19		E	600	Z	658	3290
20		Total	2100	-	776	4485
21
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub kapperson()
' hiker95, 07/05/2017, ME1012860
Dim Area As Range, sr As Long, er As Long
Application.ScreenUpdating = False
With ActiveSheet
  For Each Area In Range("B1", Range("B" & .Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants).Areas
    With Area
      sr = .Row + 1
      er = sr + .Rows.Count - 3
      Range("B" & sr & ":F" & er).Sort key1:=Range("F" & sr), order1:=1
    End With
  Next Area
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the kapperson macro.

 

[kapperson]

Hi hiker95,
Thank you again for the help! I tested the macro and it worked on the test file I provided, but when I tried to implement with my full file I had some trouble.
I think this is because of the formatting in between the tables, primarily.
Below is a link to how my actual file is set up (sans data).

https://www.dropbox.com/s/9tuqyxesti585io/FullTestFile.xlsx?dl=0

Is there a way to make it work with the report headers in between some of the tables, as shown in the file?

Thanks again for your help, and sorry for the inconvenience.

 

[hiker95]

kapperson,

When I attempted to download/open your workbook, I received the following:

Excel found unreadable content in 'FullTestFile.xlsx'. Do you want to recover the content of this workbook? If you trust the source of this workbook, click Yes.

And, the file size is 6.5 MB?????

 

[kapperson]

kapperson,

When I attempted to download/open your workbook, I received the following:

Excel found unreadable content in 'FullTestFile.xlsx'. Do you want to recover the content of this workbook? If you trust the source of this workbook, click Yes.

And, the file size is 6.5 MB?????

I apologize, it looks like there was some hidden content that didn't get deleted.
Below is a link to the new file: https://www.dropbox.com/s/9m95vcw0ys6taf1/FullTestFile.xlsx?dl=0

 

[hiker95]

kapperson,

Thanks for the latest workbook.

But, there is no raw data information?????

And, the worksheet goes out to column DX?????

 

[kapperson]

kapperson,

Thanks for the latest workbook.

But, there is no raw data information?????

And, the worksheet goes out to column DX?????

Sorry, yes, this is what my full workbook will look like without the majority of the info that shouldn't matter.
It'll sort by column DW in ascending order, which has sample values in it. I was able to alter that on your code but I believe I was having trouble because of some of the information in between the tables.

The headings for each will be the same. The width of the tables may change (however, I can change column references if necessary), and the height of the tables will definitely change, in addition to more tables potentially being added.

Please let me know if that makes sense or if you have any other questions. I really appreciate your help.

 

[hiker95]

kapperson,

Here is a macro solution for you to consider, based on your latest request.

Not all rows are shown for brevity.

Sample raw data in sections:

Excel 2007
	DW
10	Sort
11	54.1
12	72.8
13	74.2
14	81.7
15	101.0
16	85.3
17	175.2
18	181.0
19	35.4
20	152.7
21	36.4
22	67.0
23	59.0
24	23.0
25
Sheet1

Excel 2007
	DW
120	Sort
121	54.1
122	72.8
123	74.2
124	81.7
125	101.0
126	85.3
127	175.2
128	181.0
129	35.4
130	152.7
131	36.4
132	67.0
133	59.0
134	23.0
135	180.0
136	96.0
137	17.2
138	25.8
139	39.7
140	27.5
141	41.6
142	35.9
143	125.1
144	107.6
145	115.2
146	104.9
147	52.8
148	77.7
149	62.5
150	10.3
151	97.4
152	100.9
153	101.5
154	125.8
155	189.7
156	130.6
157
Sheet1

And, after the new macro:

Excel 2007
	DW
10	Sort
11	23.0
12	35.4
13	36.4
14	54.1
15	59.0
16	67.0
17	72.8
18	74.2
19	81.7
20	85.3
21	101.0
22	152.7
23	175.2
24	181.0
25
Sheet1

Excel 2007
	DW
120	Sort
121	10.3
122	17.2
123	23.0
124	25.8
125	27.5
126	35.4
127	35.9
128	36.4
129	39.7
130	41.6
131	52.8
132	54.1
133	59.0
134	62.5
135	67.0
136	72.8
137	74.2
138	77.7
139	81.7
140	85.3
141	96.0
142	97.4
143	100.9
144	101.0
145	101.5
146	104.9
147	107.6
148	115.2
149	125.1
150	125.8
151	130.6
152	152.7
153	175.2
154	180.0
155	181.0
156	189.7
157
Sheet1

With the same instructions from the first macro.

Sub kapperson_V2()
' hiker95, 07/06/2017, ME1012860
Dim Area As Range, sr As Long, er As Long
Application.ScreenUpdating = False
With ActiveSheet
  For Each Area In Range("DW1", Range("DW" & .Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants).Areas
    With Area
      sr = .Row + 1
      er = sr + .Rows.Count - 2
      Range("DW" & sr & ":DW" & er).Sort key1:=Range("DW" & sr), order1:=1
    End With
  Next Area
End With
Application.ScreenUpdating = True
End Sub

With the same instructions from the first macro.

Then run the kapperson_V2 macro.

 

[kapperson]

Thank you so much, I really appreciate your help!

It looks like it's only sorting that column, so I changed the line

Range("DW" & sr & ":DW" & er).Sort key1:=Range("DW" & sr), order1:=1

to

Range("DW" & sr & ":DW" & er).EntireRow.Sort key1:=Range("DW" & sr), order1:=1

and that seems to sort the entire row.

For some reason when I apply to my spreadsheet, though, it puts one row above the header. Do you know why that might be?

 

[kapperson]

Thank you so much, I really appreciate your help!

It looks like it's only sorting that column, so I changed the line

Range("DW" & sr & ":DW" & er).Sort key1:=Range("DW" & sr), order1:=1

to

Range("DW" & sr & ":DW" & er).EntireRow.Sort key1:=Range("DW" & sr), order1:=1

and that seems to sort the entire row.

For some reason when I apply to my spreadsheet, though, it puts one row above the header. Do you know why that might be?

Hi hiker95,
It looks like the problem was that my worksheet was using formulas instead of actual values. As a workaround I coded to copy and paste values into column DZ and sorted by that, so that it could read them as true values.
The only problem I'm having now is that it's including the value in the total row in the sort, so my total row ends up in the middle of the table. Any thoughts on how to get around that?

Thank you so much!!

Here's my revised code for your reference:

Sub kapperson_V2()' hiker95, 07/06/2017, ME1012860
Columns("DZ:DZ").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("DW:DW").Copy
Range("DZ1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False


Dim Area As Range, sr As Long, er As Long
Application.ScreenUpdating = False
With ActiveSheet
  For Each Area In Range("DZ1", Range("DZ" & .Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants).Areas
    With Area
      sr = .Row + 1
      er = sr + .Rows.Count - 2
      Range("DZ" & sr & ":DZ" & er).EntireRow.Sort key1:=Range("DZ" & sr), order1:=1
    End With
  Next Area
End With
Application.ScreenUpdating = True
End Sub