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

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
AVERAGES 2,665$79,705.23 9,037$340,357.57($10,166.43) 38($1,241.45) 441 256 307 98 6($7,548.34)
THRESHOLD30%16.4%-3.9% 49($1,613.88)-2.0% 5736.3% 332 4004.4% 1281.4% 80.1%-2.9%
TOTALS 2,019,994$60,416,565.68 6,850,235$257,991,036.5523.4%($7,706,151.12)-3.0% 28,550($941,015.42)-1.6% 334,0754.9% 193,715 232,9843.4% 74,3991.1% 4,8610.1%($5,721,638.32)-2.2%
WEEK ENDING12/31/15TOTAL STORES 931
ACTIVE STORES 758
CASH SALESALL SALESALL DISCOUNT INFORMATIONCASH RETURN INFORMATIONNO SALESITEM CORRECT TRANSACTIONSVOID TRANSACTIONSPOST VOIDSDAMAGES
REGDISTSTORELOCATIONSTATE Count DollarsCountDollarsCash Sales %Discount $% to Sales $ Count DollarsCash Return %Count% to SalesADMIN 60Count% to Sales #Count% to Sales #Count% to Sales #Dollars% to Sales $RLPMSTATUS
1010112345McKnight RdPA 3,211$96,195.41 13,597$537,663.9817.9%($16,412.85)-3.1% 39($1,081.13)-1.1% 5754.2% 379 3712.7% 870.6% 40.0%($2,379.72)-0.4%Baker
1010112346Ross ParkPA$0.00 - $0.00#DIV/0!$0.00#DIV/0! - $0.00#DIV/0! - #DIV/0! - - #DIV/0! - #DIV/0! - #DIV/0!$0.00#DIV/0!BakerNEW
1010112347Butler PAPA 3,469$97,928.52 9,128$311,696.8431.4%($8,121.31)-2.6% 41($1,204.08)-1.2% 2863.1% 214 1571.7% 1001.1% 10.0%($8,188.43)-2.6%Baker
1010212348PtsbrghMillsPA 4,141$120,205.39 13,017$467,723.8925.7%($14,396.33)-3.1% 52($1,504.66)-1.3% 3482.7% 265 2361.8% 240.2% - 0.0%($1,433.93)-0.3%Baker
1010212349Indiana PAPA 2,623$67,289.28 8,627$272,781.5924.7%($7,942.74)-2.9% 23($725.94)-1.1% 5055.9% 371 3163.7% 680.8% 50.1%($2,439.97)-0.9%Baker
1010212350Somerset PAPA 1,619$43,584.74 4,393$149,478.9529.2%($4,097.48)-2.7% 20($553.75)-1.3% 2916.6% 241 791.8% 400.9% 20.0%($653.37)-0.4%Baker
1010212351JohnstownPA - $0.00 - $0.00#DIV/0!$0.00#DIV/0! - $0.00#DIV/0! - #DIV/0! - - #DIV/0! - #DIV/0! - #DIV/0!$0.00#DIV/0!BakerNEW
1010312352SteubenvilleOH 3,073$106,542.63 7,943$314,985.7133.8%($6,730.72)-2.1% 55($1,742.45)-1.6% 6498.2% 289 1251.6% 660.8% 20.0%($3,788.08)-1.2%Baker
1010312353OHValleyTENTOH 4,059$116,968.69 12,169$436,457.5826.8%($12,639.57)-2.9% 62($2,155.66)-1.8% 4994.1% 332 2872.4% 990.8% 30.0%($3,346.40)-0.8%Baker
1010312354Chauvet DrPA 2,807$81,826.16 7,666$280,887.5129.1%($11,729.53)-4.2% 37($1,004.43)-1.2% 2863.7% 152 2713.5% 921.2% 140.2%($5,431.41)-1.9%Baker
1010412355West MifflinPA 4,001$123,838.75 12,562$464,652.9726.7%($17,502.56)-3.8% 56($1,512.79)-1.2% 3572.8% 242 2942.3% 1020.8% - 0.0%($1,765.86)-0.4%Baker
1010412356MorgantownWVWV 2,525$80,271.74 9,507$348,147.6523.1%($7,567.85)-2.2% 22($978.48)-1.2% 4594.8% 287 1972.1% 600.6% - 0.0%($4,171.68)-1.2%Baker
1010412357Homestead PAPA 2,817$76,374.24 9,894$331,065.7223.1%($9,766.68)-3.0% 45($1,274.27)-1.7% 4094.1% 278 3703.7% 1261.3% 110.1%($4,402.20)-1.3%Baker
1010412358WashingtonPAPA 3,068$102,335.93 9,547$394,321.6926.0%($13,999.66)-3.6% 37($1,053.36)-1.0% 5685.9% 309 3213.4% 720.8% - 0.0%($8,079.77)-2.0%Baker
1010512359Altoona PAPA 3,628$112,010.19 10,355$397,862.5528.2%($11,643.05)-2.9% 45($1,727.40)-1.5% 3513.4% 310 2882.8% 790.8% 240.2%($3,923.61)-1.0%Baker
1010512360StateCollegePA 1,714$53,131.33 6,594$261,284.9620.3%($11,292.52)-4.3% 27($880.09)-1.7% 4757.2% 270 1372.1% 671.0% 130.2%($2,656.22)-1.0%Baker
1010512361Logan VlyPA 1,751$45,978.40 4,731$149,939.4230.7%($3,613.12)-2.4% 26($781.19)-1.7% 3487.4% 232 992.1% 521.1% 10.0%($810.33)-0.5%Baker
1010612362HermitagePAPA 3,054$79,715.50 12,214$376,144.7421.2%($6,883.26)-1.8% 26($752.72)-0.9% 2892.4% 146 2682.2% 1030.8% 110.1%($3,347.32)-0.9%Baker
1010612363Dubois PAPA 4,418$145,310.83 11,569$462,828.9731.4%($10,169.31)-2.2% 62($2,551.51)-1.8% 8187.1% 352 2802.4% 930.8% 20.0%($10,687.74)-2.3%Baker
1010612364Beaver VlyPA 2,437$66,562.88 6,766$235,097.6128.3%($6,327.47)-2.7% 48($1,580.08)-2.4% 4446.6% 222 1522.2% 540.8% 70.1%($259.79)-0.1%Baker
1010612365E LiverpoolOH 3,156$88,705.63 8,681$308,880.5428.7%($10,792.12)-3.5% 44($1,208.50)-1.4% 5025.8% 374 1641.9% 510.6% 10.0%($2,436.73)-0.8%Baker

<colgroup><col span="2"><col><col><col><col><col><col><col><col><col span="2"><col><col><col><col span="2"><col><col span="2"><col span="2"><col span="2"><col><col><col span="2"></colgroup><tbody>
</tbody>
 
Upvote 0
Unfortunately, my company restricts installs of 3rd party software. I copied to my personal computer and will try it there, but here is the file, saved as HTML through excel (2010)
 
Upvote 0
Excel Workbook
ABCDEFGHIJKLMNOPQRSTUVWXYZ
1AVERAGES 2,665$79,705.23 9,037$340,357.57($10,166.43) 38($1,241.45) 441 256 307 98 6($7,548.34)
2THRESHOLD30%16.4%-3.9% 49($1,613.88)-2.0% 5736.3% 332 4004.4% 1281.4% 80.1%-2.9%
3TOTALS 2,019,994$60,416,565.68 6,850,235$257,991,036.5523.4%($7,706,151.12)-3.0% 28,550($941,015.42)-1.6% 334,0754.9% 193,715 232,9843.4% 74,3991.1% 4,8610.1%($5,721,638.32)-2.2%
4WEEK ENDING12/31/15TOTAL STORES 931
5ACTIVE STORES 758
6CASH SALESALL SALESALL DISCOUNT INFORMATIONCASH RETURN INFORMATIONNO SALESITEM CORRECT TRANSACTIONSVOID TRANSACTIONSPOST VOIDSDAMAGES
7REGDISTSTORELOCATIONSTATECountDollarsCountDollarsCash Sales %Discount $% to Sales $CountDollarsCash Return %Count% to SalesADMIN 60Count% to Sales #Count% to Sales #Count% to Sales #Dollars% to Sales $
81010112345McKnight RdPA 3,211$96,195.41 13,597$537,663.9817.9%($16,412.85)-3.1% 39($1,081.13)-1.1% 5754.2% 379 3712.7% 870.6% 40.0%($2,379.72)-0.4%
91010112346Ross ParkPA$0.00 - $0.00#DIV/0!$0.00#DIV/0! - $0.00#DIV/0! - #DIV/0! - - #DIV/0! - #DIV/0! - #DIV/0!$0.00#DIV/0!
101010112347Butler PAPA 3,469$97,928.52 9,128$311,696.8431.4%($8,121.31)-2.6% 41($1,204.08)-1.2% 2863.1% 214 1571.7% 1001.1% 10.0%($8,188.43)-2.6%
111010212348PtsbrghMillsPA 4,141$120,205.39 13,017$467,723.8925.7%($14,396.33)-3.1% 52($1,504.66)-1.3% 3482.7% 265 2361.8% 240.2% - 0.0%($1,433.93)-0.3%
121010212349Indiana PAPA 2,623$67,289.28 8,627$272,781.5924.7%($7,942.74)-2.9% 23($725.94)-1.1% 5055.9% 371 3163.7% 680.8% 50.1%($2,439.97)-0.9%
131010212350Somerset PAPA 1,619$43,584.74 4,393$149,478.9529.2%($4,097.48)-2.7% 20($553.75)-1.3% 2916.6% 241 791.8% 400.9% 20.0%($653.37)-0.4%
141010212351JohnstownPA - $0.00 - $0.00#DIV/0!$0.00#DIV/0! - $0.00#DIV/0! - #DIV/0! - - #DIV/0! - #DIV/0! - #DIV/0!$0.00#DIV/0!
151010312352SteubenvilleOH 3,073$106,542.63 7,943$314,985.7133.8%($6,730.72)-2.1% 55($1,742.45)-1.6% 6498.2% 289 1251.6% 660.8% 20.0%($3,788.08)-1.2%
161010312353OHValleyTENTOH 4,059$116,968.69 12,169$436,457.5826.8%($12,639.57)-2.9% 62($2,155.66)-1.8% 4994.1% 332 2872.4% 990.8% 30.0%($3,346.40)-0.8%
171010312354Chauvet DrPA 2,807$81,826.16 7,666$280,887.5129.1%($11,729.53)-4.2% 37($1,004.43)-1.2% 2863.7% 152 2713.5% 921.2% 140.2%($5,431.41)-1.9%
Sheet1
 
Upvote 0
Ok, I think I got it to work from home. Is there a way to delete the massive HTML cut and past I did earlier?
 
Upvote 0
What is the address of the cell that has "REG" in it? Is there anything in the row immediately above that cell (entire row)? If yes, can you add a blank row above that cell? And is the 3-digit DIST code in the column immediately adjacent to the REG cell column?

EDIT: just saw your post from home. Now I can see the column and row identifiers. Ignore my questions. Need to go offline now - will look at this later.
 
Last edited:
Upvote 0
Well may be a quick fix. See if this works for you.
Code:
Sub ThreeRowsBetween()
Dim i As Long
Application.ScreenUpdating = False
With Rows(7)
    .Insert
    .Offset(-1, 0).RowHeight = 1
End With
Range("A8").CurrentRegion.Sort key1:=[b2], order1:=xlAscending, Header:=xlYes
For i = Range("B8:B" & Cells(Rows.Count, "B").End(xlUp).Row).Rows.Count To 10 Step -1
    If Cells(i, "B") <> Cells(i - 1, "B") Then
        Cells(i, "B").Resize(3, 1).EntireRow.Insert
    End If
Next i
Rows(7).Delete
Application.ScreenUpdating = True
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

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