Hi,

I have an excel file which goes like this in my first 2 columns:

PRODUCT PUBLICATION
NAME

Product 1 publication 1
publication 1
publication 2
Product 2 publication 1
publication 1
publication 2
Product 3 publication 3
Product 4 publication 2

This file runs in 20k + rows.....and I need to insert a new row whenever I
encounter a new product or publication. The end result as I want is below:

PRODUCT PUBLICATION
NAME

Product 1 publication 1
publication 1

publication 2

Product 2 publication 1
publication 1

publication 2

Product 3 publication 3

Product 4 publication 2

Notice that there's a row between new products and new publications. Can
someone tell me an easier way to do it , or than doing it manually for
20,000+ rows?

Thanks :)

P

copy this macro into a module.

Sub insertspaces()
For i = Cells(Rows.Count, "b").End(xlUp).Row To 2 Step -1
If Cells(i - 1, "b") < Cells(i, "b") Then Rows(i).Insert
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Pman" wrote in message
...
Hi,

I have an excel file which goes like this in my first 2 columns:

PRODUCT PUBLICATION
NAME

Product 1 publication 1
publication 1
publication 2
Product 2 publication 1
publication 1
publication 2
Product 3 publication 3
Product 4 publication 2

This file runs in 20k + rows.....and I need to insert a new row whenever I
encounter a new product or publication. The end result as I want is below:

PRODUCT PUBLICATION
NAME

Product 1 publication 1
publication 1

publication 2

Product 2 publication 1
publication 1

publication 2

Product 3 publication 3

Product 4 publication 2

Notice that there's a row between new products and new publications. Can
someone tell me an easier way to do it , or than doing it manually for
20,000+ rows?

Thanks :)

P

Thanks a lot Don :)

Also if I want to repeat it for other columns, I just have to run the macro
again but replace the "b" in the code with the corresponding column name
right?

Thanks again, You made my day :)

-Pman

"Don Guillett" wrote:

copy this macro into a module.

Sub insertspaces()
For i = Cells(Rows.Count, "b").End(xlUp).Row To 2 Step -1
If Cells(i - 1, "b") < Cells(i, "b") Then Rows(i).Insert
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Pman" wrote in message
...
Hi,

I have an excel file which goes like this in my first 2 columns:

PRODUCT PUBLICATION
NAME

Product 1 publication 1
publication 1
publication 2
Product 2 publication 1
publication 1
publication 2
Product 3 publication 3
Product 4 publication 2

This file runs in 20k + rows.....and I need to insert a new row whenever I
encounter a new product or publication. The end result as I want is below:

PRODUCT PUBLICATION
NAME

Product 1 publication 1
publication 1

publication 2

Product 2 publication 1
publication 1

publication 2

Product 3 publication 3

Product 4 publication 2

Notice that there's a row between new products and new publications. Can
someone tell me an easier way to do it , or than doing it manually for
20,000+ rows?

Thanks :)

P

Yes. Or you could have an input box asking the user which column.
And, if you want to run for multiple columns that can be done to make one
mouse click.
But, are you sure you want to do that cuz you might get rows inserted that
you don't want.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Pman" wrote in message
...
Thanks a lot Don :)

Also if I want to repeat it for other columns, I just have to run the
macro
again but replace the "b" in the code with the corresponding column name
right?

Thanks again, You made my day :)

-Pman

"Don Guillett" wrote:

copy this macro into a module.

Sub insertspaces()
For i = Cells(Rows.Count, "b").End(xlUp).Row To 2 Step -1
If Cells(i - 1, "b") < Cells(i, "b") Then Rows(i).Insert
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Pman" wrote in message
...
Hi,

I have an excel file which goes like this in my first 2 columns:

PRODUCT PUBLICATION
NAME

Product 1 publication 1
publication 1
publication 2
Product 2 publication 1
publication 1
publication 2
Product 3 publication 3
Product 4 publication 2

This file runs in 20k + rows.....and I need to insert a new row
whenever I
encounter a new product or publication. The end result as I want is
below:

PRODUCT PUBLICATION
NAME

Product 1 publication 1
publication 1

publication 2

Product 2 publication 1
publication 1

publication 2

Product 3 publication 3

Product 4 publication 2

Notice that there's a row between new products and new publications.
Can
someone tell me an easier way to do it , or than doing it manually for
20,000+ rows?

Thanks :)

P

Hi Don,

I just want to insert rows for data in column A and B. I tried out the macro
you gave me and modified it to run for column A as well, however it gave me
addition rows that I didn't need (as you said might happen in your last
reply). Is there a way to do this, since further formatting of the data
depends on this step?

Thanks again Don,
P

"Don Guillett" wrote:


Yes. Or you could have an input box asking the user which column.
And, if you want to run for multiple columns that can be done to make one
mouse click.
But, are you sure you want to do that cuz you might get rows inserted that
you don't want.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Pman" wrote in message
...
Thanks a lot Don :)

Also if I want to repeat it for other columns, I just have to run the
macro
again but replace the "b" in the code with the corresponding column name
right?

Thanks again, You made my day :)

-Pman

"Don Guillett" wrote:

copy this macro into a module.

Sub insertspaces()
For i = Cells(Rows.Count, "b").End(xlUp).Row To 2 Step -1
If Cells(i - 1, "b") < Cells(i, "b") Then Rows(i).Insert
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Pman" wrote in message
...
Hi,

I have an excel file which goes like this in my first 2 columns:

PRODUCT PUBLICATION
NAME

Product 1 publication 1
publication 1
publication 2
Product 2 publication 1
publication 1
publication 2
Product 3 publication 3
Product 4 publication 2

This file runs in 20k + rows.....and I need to insert a new row
whenever I
encounter a new product or publication. The end result as I want is
below:

PRODUCT PUBLICATION
NAME

Product 1 publication 1
publication 1

publication 2

Product 2 publication 1
publication 1

publication 2

Product 3 publication 3

Product 4 publication 2

Notice that there's a row between new products and new publications.
Can
someone tell me an easier way to do it , or than doing it manually for
20,000+ rows?

Thanks :)

P

Hi Don,

Is it possible to insert a line/ border whenever I insert the row?

Thanks again :)

-Pman

"Don Guillett" wrote:

copy this macro into a module.

Sub insertspaces()
For i = Cells(Rows.Count, "b").End(xlUp).Row To 2 Step -1
If Cells(i - 1, "b") < Cells(i, "b") Then Rows(i).Insert
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Pman" wrote in message
...
Hi,

I have an excel file which goes like this in my first 2 columns:

PRODUCT PUBLICATION
NAME

Product 1 publication 1
publication 1
publication 2
Product 2 publication 1
publication 1
publication 2
Product 3 publication 3
Product 4 publication 2

This file runs in 20k + rows.....and I need to insert a new row whenever I
encounter a new product or publication. The end result as I want is below:

PRODUCT PUBLICATION
NAME

Product 1 publication 1
publication 1

publication 2

Product 2 publication 1
publication 1

publication 2

Product 3 publication 3

Product 4 publication 2

Notice that there's a row between new products and new publications. Can
someone tell me an easier way to do it , or than doing it manually for
20,000+ rows?

Thanks :)

P

Sub insertspacesandline()
For i = Cells(Rows.Count, "d").End(xlUp).Row To 2 Step -1
If Cells(i - 1, "d") < Cells(i, "d") Then
Rows(i).Insert
Cells(i - 1, "d").Borders(xlEdgeBottom).LineStyle = xlContinuous
End If
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Pman" wrote in message
...
Hi Don,

Is it possible to insert a line/ border whenever I insert the row?

Thanks again :)

-Pman

"Don Guillett" wrote:

copy this macro into a module.

Sub insertspaces()
For i = Cells(Rows.Count, "b").End(xlUp).Row To 2 Step -1
If Cells(i - 1, "b") < Cells(i, "b") Then Rows(i).Insert
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Pman" wrote in message
...
Hi,

I have an excel file which goes like this in my first 2 columns:

PRODUCT PUBLICATION
NAME

Product 1 publication 1
publication 1
publication 2
Product 2 publication 1
publication 1
publication 2
Product 3 publication 3
Product 4 publication 2

This file runs in 20k + rows.....and I need to insert a new row
whenever I
encounter a new product or publication. The end result as I want is
below:

PRODUCT PUBLICATION
NAME

Product 1 publication 1
publication 1

publication 2

Product 2 publication 1
publication 1

publication 2

Product 3 publication 3

Product 4 publication 2

Notice that there's a row between new products and new publications.
Can
someone tell me an easier way to do it , or than doing it manually for
20,000+ rows?

Thanks :)

P

Thanks again Don :)

And to extend the border to a range, I just copy-paste the code " Cells(i -
1, "d").Borders(xlEdgeBottom).LineStyle = xlContinuous" multiple times and
change the cells reference right? Or let me just try it out :)

Thanks again, and Have a great weekend Don :)

"Don Guillett" wrote:

Sub insertspacesandline()
For i = Cells(Rows.Count, "d").End(xlUp).Row To 2 Step -1
If Cells(i - 1, "d") < Cells(i, "d") Then
Rows(i).Insert
Cells(i - 1, "d").Borders(xlEdgeBottom).LineStyle = xlContinuous
End If
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Pman" wrote in message
...
Hi Don,

Is it possible to insert a line/ border whenever I insert the row?

Thanks again :)

-Pman

"Don Guillett" wrote:

copy this macro into a module.

Sub insertspaces()
For i = Cells(Rows.Count, "b").End(xlUp).Row To 2 Step -1
If Cells(i - 1, "b") < Cells(i, "b") Then Rows(i).Insert
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Pman" wrote in message
...
Hi,

I have an excel file which goes like this in my first 2 columns:

PRODUCT PUBLICATION
NAME

Product 1 publication 1
publication 1
publication 2
Product 2 publication 1
publication 1
publication 2
Product 3 publication 3
Product 4 publication 2

This file runs in 20k + rows.....and I need to insert a new row
whenever I
encounter a new product or publication. The end result as I want is
below:

PRODUCT PUBLICATION
NAME

Product 1 publication 1
publication 1

publication 2

Product 2 publication 1
publication 1

publication 2

Product 3 publication 3

Product 4 publication 2

Notice that there's a row between new products and new publications.
Can
someone tell me an easier way to do it , or than doing it manually for
20,000+ rows?

Thanks :)

P

???

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Pman" wrote in message
...
Thanks again Don :)

And to extend the border to a range, I just copy-paste the code "
Cells(i -
1, "d").Borders(xlEdgeBottom).LineStyle = xlContinuous" multiple times and
change the cells reference right? Or let me just try it out :)

Thanks again, and Have a great weekend Don :)

"Don Guillett" wrote:

Sub insertspacesandline()
For i = Cells(Rows.Count, "d").End(xlUp).Row To 2 Step -1
If Cells(i - 1, "d") < Cells(i, "d") Then
Rows(i).Insert
Cells(i - 1, "d").Borders(xlEdgeBottom).LineStyle = xlContinuous
End If
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Pman" wrote in message
...
Hi Don,

Is it possible to insert a line/ border whenever I insert the row?

Thanks again :)

-Pman

"Don Guillett" wrote:

copy this macro into a module.

Sub insertspaces()
For i = Cells(Rows.Count, "b").End(xlUp).Row To 2 Step -1
If Cells(i - 1, "b") < Cells(i, "b") Then Rows(i).Insert
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Pman" wrote in message
...
Hi,

I have an excel file which goes like this in my first 2 columns:

PRODUCT PUBLICATION
NAME

Product 1 publication 1
publication 1
publication 2
Product 2 publication 1
publication 1
publication 2
Product 3 publication 3
Product 4 publication 2

This file runs in 20k + rows.....and I need to insert a new row
whenever I
encounter a new product or publication. The end result as I want is
below:

PRODUCT PUBLICATION
NAME

Product 1 publication 1
publication 1

publication 2

Product 2 publication 1
publication 1

publication 2

Product 3 publication 3

Product 4 publication 2

Notice that there's a row between new products and new publications.
Can
someone tell me an easier way to do it , or than doing it manually
for
20,000+ rows?

Thanks :)

P

A formula will not do this.

Mike F
"Pman" wrote in message
...
Hi,

I have an excel file which goes like this in my first 2 columns:

PRODUCT PUBLICATION
NAME

Product 1 publication 1
publication 1
publication 2
Product 2 publication 1
publication 1
publication 2
Product 3 publication 3
Product 4 publication 2

This file runs in 20k + rows.....and I need to insert a new row whenever I
encounter a new product or publication. The end result as I want is below:

PRODUCT PUBLICATION
NAME

Product 1 publication 1
publication 1

publication 2

Product 2 publication 1
publication 1

publication 2

Product 3 publication 3

Product 4 publication 2

Notice that there's a row between new products and new publications. Can
someone tell me an easier way to do it , or than doing it manually for
20,000+ rows?

Thanks :)

P