Grouping rows based on like data in a cell and then separating them

[nebuul]

Lets say you have a 15,000 line spreadsheet covering about 8,000
different items. Some items have multiple lines devoted to them. Each
line is a separate transaction related to that item.

After sorting the list by the item name, I now wish to have excel
insert a blank line between each different name. So basically this:

alpha
alpha
beta
beta
beta
gamma
delta
delta

would become this:

alpha
alpha

beta
beta
beta

gamma

delta
delta

Is there any way to do that other than manually inserting rows? An
automated process would save a LOT of time.

 

[Gord Dibben]

Sub InsertRow_At_Change()
Dim i As Long
With Application
.Calculation = xlManual
.ScreenUpdating = False
End With
For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
If Cells(i - 1, 1) <> Cells(i, 1) Then _
Cells(i, 1).Resize(1, 1).EntireRow.Insert
Next i
With Application
.Calculation = xlAutomatic
.ScreenUpdating = True
End With
End Sub


Gord Dibben MS Excel MVP

Lets say you have a 15,000 line spreadsheet covering about 8,000
different items. Some items have multiple lines devoted to them. Each
line is a separate transaction related to that item.

After sorting the list by the item name, I now wish to have excel
insert a blank line between each different name. So basically this:

alpha
alpha
beta
beta
beta
gamma
delta
delta

would become this:

alpha
alpha

beta
beta
beta

gamma

delta
delta

Is there any way to do that other than manually inserting rows? An
automated process would save a LOT of time.

Gord Dibben MS Excel MVP

 

[nebuul]

Awesome, thanks!

Sub InsertRow_At_Change()
Dim i As Long
With Application
.Calculation = xlManual
.ScreenUpdating = False
End With
For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
If Cells(i - 1, 1) <> Cells(i, 1) Then _
Cells(i, 1).Resize(1, 1).EntireRow.Insert
Next i
With Application
.Calculation = xlAutomatic
.ScreenUpdating = True
End With
End Sub


Gord Dibben MS Excel MVP



Gord Dibben MS Excel MVP