when the column text is different, how to insert one row below eac

[Guest]

I have a spreadsheet with column, Date, Name, Amount. only a few columns, but
a few thousand rows with same Name, different Date and different Amount.
Sample below:
Date Name Amount
4/1/07 John 100
4/1/07 Michael 200
4/2/07 John 110
4/2/07 Michael 220
4/4/07 John 130
4/4/07 Michael 230
First I will sort the above by Name then by Date, then I insert one blank
row just below John.
When I have a hundred different Names, How to automatically insert one blank
row when the Name is different?

 

[Guest]

-First sort the above by Name then by Date
-Create a helper column in (column c)

In C2: =COUNTIF($B$3:B3,B3)=1
Copy all the way down
AutoFilter all the TRUE >> select all the filter rows using the row numbers

 

[Guest]

Oops, Should be B2 instead of B3

In C2: =COUNTIF($B$2:B2,B2)=1

 

[Gord Dibben]

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


Gord Dibben MS Excel MVP


On Mon, 23 Apr 2007 15:02:04 -0700, Greensky Young <Greensky

 

[Guest]

Thanks so much. It works. If the name, John only appears once. John will not
separate itself.

Thanks again.

 

[Guest]

Thanks.
Please tell me how to use this "Sub" in my spreadsheet.
Is this a macro?
Thanks again.