| Home | Forums | Reviews | Articles | Register |
 |
| Thread Tools | Rate Thread |
|
Guest
Posts: n/a
|
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 |
|
|
|
|||
|
|
| |
|
Guest
Posts: n/a
|
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 (E-Mail Removed) "Pman" <(E-Mail Removed)> wrote in message news:8E19B5A0-7AF5-4978-A57D-(E-Mail Removed)... > 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 |
|
|
|
|
|||
|
Guest
Posts: n/a
|
A formula will not do this.
Mike F "Pman" <(E-Mail Removed)> wrote in message news:8E19B5A0-7AF5-4978-A57D-(E-Mail Removed)... > 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 |
|
|
|
|
|||
|
Guest
Posts: n/a
|
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 > (E-Mail Removed) > "Pman" <(E-Mail Removed)> wrote in message > news:8E19B5A0-7AF5-4978-A57D-(E-Mail Removed)... > > 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 > > |
|
|
|
|
|||
|
Guest
Posts: n/a
|
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 (E-Mail Removed) "Pman" <(E-Mail Removed)> wrote in message news:3287BA1B-250C-487A-B5C7-(E-Mail Removed)... > 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 >> (E-Mail Removed) >> "Pman" <(E-Mail Removed)> wrote in message >> news:8E19B5A0-7AF5-4978-A57D-(E-Mail Removed)... >> > 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 >> >> |
|
|
|
|
|||
|
Guest
Posts: n/a
|
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 > (E-Mail Removed) > "Pman" <(E-Mail Removed)> wrote in message > news:3287BA1B-250C-487A-B5C7-(E-Mail Removed)... > > 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 > >> (E-Mail Removed) > >> "Pman" <(E-Mail Removed)> wrote in message > >> news:8E19B5A0-7AF5-4978-A57D-(E-Mail Removed)... > >> > 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 > >> > >> > > |
|
|
|
|
|||
|
Guest
Posts: n/a
|
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 > (E-Mail Removed) > "Pman" <(E-Mail Removed)> wrote in message > news:8E19B5A0-7AF5-4978-A57D-(E-Mail Removed)... > > 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 > > |
|
|
|
|
|||
|
Guest
Posts: n/a
|
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 (E-Mail Removed) "Pman" <(E-Mail Removed)> wrote in message news:FB957D7F-7AD8-4679-9E8D-(E-Mail Removed)... > 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 >> (E-Mail Removed) >> "Pman" <(E-Mail Removed)> wrote in message >> news:8E19B5A0-7AF5-4978-A57D-(E-Mail Removed)... >> > 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 >> >> |
|
|
|
|
|||
|
Guest
Posts: n/a
|
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 > (E-Mail Removed) > "Pman" <(E-Mail Removed)> wrote in message > news:FB957D7F-7AD8-4679-9E8D-(E-Mail Removed)... > > 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 > >> (E-Mail Removed) > >> "Pman" <(E-Mail Removed)> wrote in message > >> news:8E19B5A0-7AF5-4978-A57D-(E-Mail Removed)... > >> > 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 > >> > >> > > |
|
|
|
|
|||
|
Guest
Posts: n/a
|
???
-- Don Guillett Microsoft MVP Excel SalesAid Software (E-Mail Removed) "Pman" <(E-Mail Removed)> wrote in message news:846A8F53-08A1-4FB3-8D5D-(E-Mail Removed)... > 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 >> (E-Mail Removed) >> "Pman" <(E-Mail Removed)> wrote in message >> news:FB957D7F-7AD8-4679-9E8D-(E-Mail Removed)... >> > 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 >> >> (E-Mail Removed) >> >> "Pman" <(E-Mail Removed)> wrote in message >> >> news:8E19B5A0-7AF5-4978-A57D-(E-Mail Removed)... >> >> > 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 >> >> >> >> >> >> |
|
|
|
|
|||
|
|
| |
|
| Thread Tools | |
| Rate This Thread | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| How do I set up a formula to insert text in excel? | =?Utf-8?B?bmlra2k4MzI3?= | Microsoft Excel Misc | 5 | 10th Feb 2005 07:18 AM |
| How do I insert the result of an Excel formula.... | =?Utf-8?B?SG93YXJk?= | Microsoft Powerpoint | 1 | 8th Oct 2004 12:17 PM |
| How do I insert text into a formula within Excel? | =?Utf-8?B?REI=?= | Microsoft Excel Worksheet Functions | 2 | 24th Sep 2004 06:12 PM |
| Re: Insert Formula To End of Excel Sheet | Frank Kabel | Microsoft Excel Programming | 0 | 16th Mar 2004 06:54 AM |
| Insert Formula To End of Excel Sheet | Larry R Harrison Jr | Microsoft Excel Programming | 0 | 16th Mar 2004 05:44 AM |
