Dell Optiplex 780 desktop computer - cor...

I have a data like this product1---cat1---cat2---cat3---cat4

xyz----------ab------bc------cd------fg

abc----------cv------dg-------fg------bnnow 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

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.

Yes the number of categories is always four but there could be any number of rows.

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.

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------bnxyz---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.

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.

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. :-)

Have you triedto 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.

Ask Your Question

Weekly Poll

Have you played Jackbox during the pandemic?

Discuss in The Lounge

Poll History