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

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?

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

-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
on the far left Edit Go To Special Select Visible cells only
OK Insert Rows Remove the AutoFilter and Delete the helper column


"Greensky Young" wrote:

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?

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

Oops, Should be B2 instead of B3

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

"Teethless mama" wrote:

-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
on the far left Edit Go To Special Select Visible cells only
OK Insert Rows Remove the AutoFilter and Delete the helper column


"Greensky Young" wrote:

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?

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

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
wrote:

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?

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

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

Thanks again.

"Teethless mama" wrote:

Oops, Should be B2 instead of B3

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

"Teethless mama" wrote:

-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
on the far left Edit Go To Special Select Visible cells only
OK Insert Rows Remove the AutoFilter and Delete the helper column


"Greensky Young" wrote:

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?

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

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

"Gord Dibben" wrote:

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
wrote:

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?