Splitting Data into Districts

[DPChristman]

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?

 

[JoeMo]

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?

 

[DPChristman]

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

 

[DPChristman]

...sorry, obviously the spacing didn't take, but the bold numbers are rows and columns

 

[JoeMo]

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.

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

 

[DPChristman]

Not sure how to implement the above steps. Do I create it as a macro?

Also, how can I provide an easily transferable version?

 

[DPChristman]

Actually, I just created it a s a macro, and it seems to work perfectly - Thanks!

 

[JoeMo]

Actually, I just created it a s a macro, and it seems to work perfectly - Thanks!

You are welcome - thanks for the reply.

 

[DPChristman]

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

 

[JoeMo]

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.