Macro to create multiple rows

Dell Optiplex 780 desktop computer - cor...
November 21, 2011 at 22:09:42
Specs: Windows 7, 2gb
I have a data like this

product1---cat1---cat2---cat3---cat4
xyz----------ab------bc------cd------fg
abc----------cv------dg-------fg------bn

now i want data like this through macro

xyz---ab
xyz---bc
xyz---cd
xyz---fg
abc---cv
abc---dg and so on in multiple rows..please help


See More: Macro to create multiple rows

Report •

#1
November 22, 2011 at 05:10:30
Are there always four and only four Catagories for each Product?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
November 22, 2011 at 05:18:11
Yes the number of categories is always four but there could be any number of rows.

Report •

#3
November 22, 2011 at 18:23:04
No need for a macro...you can do it with formula.

Assuming your Product data begins in A2, put this in F2 and drag it down. This will repeat your Product numbers 4 times each.

=INDEX($A:$A,INT((ROW()-2)/4)+2,0)

Put this in G2 and drag it down. This will pick up each category (1 - 4) and place them next to the 4 product numbers.

=INDIRECT(CHAR(65+IF(MOD(ROW()-1,4)=0,4,MOD(ROW()-1,4)))&ROUNDUP((ROW()-1)/4,0)+1)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
November 22, 2011 at 21:19:01
Thanks for the help..but i want a slight change in that

product1---cat1---cat2---cat3---cat4
xyz----------ab------bc------cd------fg
abc----------cv------dg-------fg------bn

xyz---ab--cat1
xyz---bc--cat2
xyz---cd--cat3
xyz---fg---cat4
abc---cv--cat1 and so on..cat1,cat2,cat3,cat4 are column names and are always same.


Report •

#5
November 23, 2011 at 03:49:13
If that's what you wanted, why didn't you ask for it?

I gave you the framework for the solution. Study the formulas I've offered, figure out how they work, and adapt them for the added requirement.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
November 23, 2011 at 04:27:12
I forget to mention that at that time.Actually i have not used such complex formulas in excel yet so i am not able to interpret them.Kindly send me the formula..and a big thanks for the help. :-)

Report •

#7
November 23, 2011 at 06:12:47
Have you tried to interpret them?

Why don't you use the Help files for the various nested functions and see if you can get a general idea of what the formulas do?

If you'll post back some words that show that you attempted to figure out the concept behind the formulas, I'll see what I can do about coming up with a formula for your added requirement.

For your latest request all that is required is a simplification of the 2nd formula so that it pulls data from Row 1 all the time.

It is only by studying the solutions that are offered will you increase your Excel knowledge. In fact, I did not know how to solve your problem until I did a Google search about copying and repeating data.

When I found a few suggestions, I studied how they worked and modified what I found to fit your specific situation. By studying what I have offered, you can do the same thing.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question