Splitting Data into Districts

DPChristman

Board Regular
Joined
Sep 4, 2012
Messages
171
Office Version
  1. 365
Platform
  1. Windows
I have a spreadsheet with approximately 1,000 rows.

Each row represents a store location

These 1,000 rows are divided into approximately 310 numbered districts (3 digit numbers).

At the end of each district I need to have a blank row, followed by a row with the district totals, followed by another blank row.

I am not worried about the math part for the districts, I have that figured out.

What I am looking for is a way to enter three blank rows between each district, without having to do it manually 300+ times.

Is there a macro or script that can accomplish this?
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
What column are the district numbers in and starting in what row? Is the data sorted on that column so that stores in the same district are grouped together?
 
Upvote 0
Currently, that data exists in a generic worksheet. Once I get the districts split by three blank rows, I will convert to a static text, and copy the fields into the formatted report. So:

Row 1 contains the header information (REGION, REGIONAL MANAGER, DISTRICT, DISTRICT MANAGER, STORE NUMBER, LOCATION)

(I also need blank rows between each region, but there are only 30 of those, so doing them Manually is not an issue)

Row two starts the store listing

So essentially, the data looks something like this:

A B C D E F
1 Reg # Reg MGR Dist # Dist MGR Store # Location
2 10 J Smith 123 M Jones 12345 Smith Haven
3 10 J Smith 123 M Jones 12354 Bayshore
4 10 J Smith 123 M Jones 14526 Coram
5 10 J Smith 456 A Lewis 14562 Brooklyn
6 10 J Smith 456 A Lewis 18545 Harlem
7 10 J Smith 456 A Lewis 19545 Chelsea


...etc
 
Last edited:
Upvote 0
...sorry, obviously the spacing didn't take, but the bold numbers are rows and columns
 
Upvote 0
Currently, that data exists in a generic worksheet. Once I get the districts split by three blank rows, I will convert to a static text, and copy the fields into the formatted report. So:

Row 1 contains the header information (REGION, REGIONAL MANAGER, DISTRICT, DISTRICT MANAGER, STORE NUMBER, LOCATION)

(I also need blank rows between each region, but there are only 30 of those, so doing them Manually is not an issue)

Row two starts the store listing

So essentially, the data looks something like this:

A B C D E F
1 Reg # Reg MGR Dist # Dist MGR Store # Location
2 10 J Smith 123 M Jones 12345 Smith Haven
3 10 J Smith 123 M Jones 12354 Bayshore
4 10 J Smith 123 M Jones 14526 Coram
5 10 J Smith 456 A Lewis 14562 Brooklyn
6 10 J Smith 456 A Lewis 18545 Harlem
7 10 J Smith 456 A Lewis 19545 Chelsea


...etc
You have not provided your sample data in a form easily transferable to Excel so this is untested. It assumes your three-digit district numbers are in col C starting in C2 and produces the three blank rows between districts after a sort on col C.
Code:
Sub ThreeRowsBetween()
Dim i As Long
Application.ScreenUpdating = False
Range("A1").CurrentRegion.Sort key1:=[C2], order1:=xlAscending, Header:=xlYes
For i = Range("C1:C" & Cells(Rows.Count, "C").End(xlUp).Row).Rows.Count To 3 Step -1
    If Cells(i, "C") <> Cells(i - 1, "C") Then
        Cells(i, "C").Resize(3, 1).EntireRow.Insert
    End If
Next i
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Not sure how to implement the above steps. Do I create it as a macro?

Also, how can I provide an easily transferable version?
 
Upvote 0
Actually, I just created it a s a macro, and it seems to work perfectly - Thanks!
 
Upvote 0
Just a quick thought...

My final (formatted) version of this report actually begins the data on Row 8, after header information (some of which includes merged cells).

How could I alter the script to work in the finished document?

If that is not possible, I can just paste the finished product into formatted report
 
Upvote 0
Just a quick thought...

My final (formatted) version of this report actually begins the data on Row 8, after header information (some of which includes merged cells).

How could I alter the script to work in the finished document?

If that is not possible, I can just paste the finished product into formatted report
That's the kind of information that would be nice to have in your original post. Post your layout so we can see what it looks like.
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,176
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top