re-organize data in fixed blocks

[anand]

I have an array of data. Column A has a sequential, repeating numeric code.
Example
Column A
1
1
1
3
3
3
3
3
4
4
5
5
5
6

In the column, the codes are always sequential overall but can repeat up
to 9 times (anywhere from 0 to 9 X). For example, you could have 9
sequential iterations of "7" in the column before you get to "8". Or you
could just have 1 or no iterations.

Beside each code # in column A is data in columns B, C, D etc.

What I'd like to do is reset the data for each numeric code so that it
always starts on every 10th row. Which would mean that each block of data
would start for example on Row 2, 12, 22, 32,42.... But since each block of
data may have anywhere from 1 to 9 iterations, there will be blank rows where
ever necessary.
So a row sequence code above would become:
Column A
1
1
1
blank row
blank row
blank row
blank row
blank row
blank row
blank row
blank row
3
3
3
3
3
blank row
blank row
blank row
blank row
blank row
4
4
8 blank rows

You get the idea.

Any reasonably quick way to do this? I have almost 45000 rows to do so a
fast way to do would be greatly appreciated.

Thanks

anand

 

[Dave Peterson]

Is a macro ok?

Option Explicit
Sub testme()

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim HowMany As Long
Dim wks As Worksheet

Set wks = Worksheets("sheet1")

With wks
FirstRow = 2
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = LastRow - 1 To FirstRow Step -1
If .Cells(iRow, "A").Value = .Cells(iRow + 1, "A").Value Then
'in the group, do nothing
Else
HowMany = Application.CountIf(.Range("a:a"), _
.Cells(iRow, "A").Value)
If HowMany < 10 Then
.Rows(iRow + 1).Resize(10 - HowMany).Insert
End If
End If
Next iRow
End With

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[akphidelt]

Alright, make sure you create a back up before trying this, but you can do it
with vba.

Go to, tools-->macros--->visual basic editor

Click on insert---> module

Copy and paste this code in to the module word for word.

Sub TestRows()
Dim MyCell
Dim x As Integer
Dim y As Integer

x = 2

Do Until ActiveCell.Row = 65532
Range("A" & x).Activate
MyCell = ActiveCell.Value

For y = 1 To 9
If Not ActiveCell.Offset(y, 0).Value = MyCell Then
ActiveCell.Offset(y, 0).EntireRow.Insert
End If
Next y
x = x + 10
Loop
End Sub

Make sure you click on cell A2 in the sheet. Then go back to the code and
press the little play button.

The code is set to the last possible row you can go to that ends in 2. IF
you have 45000 plus rows of data the its possible you will go over the limit
of rows.

Let me know how it works

 

[anand]

thanks, worked well.

anand

Is a macro ok?

Option Explicit
Sub testme()

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim HowMany As Long
Dim wks As Worksheet

Set wks = Worksheets("sheet1")

With wks
FirstRow = 2
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = LastRow - 1 To FirstRow Step -1
If .Cells(iRow, "A").Value = .Cells(iRow + 1, "A").Value Then
'in the group, do nothing
Else
HowMany = Application.CountIf(.Range("a:a"), _
.Cells(iRow, "A").Value)
If HowMany < 10 Then
.Rows(iRow + 1).Resize(10 - HowMany).Insert
End If
End If
Next iRow
End With

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm