I'm creating a product catalog database in Excel. I have 1000 unique product lines (rows). Each of those 1000 products is offered in 120 colors -- all of those colors are the same for each product. So I need to insert the same 120 color name lines (rows) - (which are in column H if that is relevant to you) - in between each of the 1000 product lines. As it is now, I'm Copying the 120 rows and Inserting between each of the 1000 rows - at this rate it'll take me hours. After this first 1000 products, I have another 4 sets of 1000 that will have their own separate 50-100 colors. I need a macro to do it - I've started watching Lynda.com tutorials, but I'm a basic/intermediate Excel user and have never done a macro before. I'm Excel literate, so if you just explain it just as you would do it - I'll get it! Thanks so much for any assistance you can offer.
From your post it is little difficult to visualize as to how the rows containing the product name and colors are organized. It is suggested that you please upload the sample file to the following website:
There seems to be some thing inherently wrong, if one has to copy same rows again and again and more so if those rows are same. So IMHO, either excel layout is not correct or excel is not the right tool for this
Hi Trowa. Well i must confess that I did not even think of max rows.
What came to my mind was that it sound like 1 row of product followed by 120 rows for color. Since 120 rows are being repeated, I was thinking more on a line of why not have 120 rows as columns (if possible). Or may be use some other tool (Access ?) to do what is needed. Of course, all depends on requirement. Forgot to thank you about exposing "ROUNDUP" functionality to me. thanks for that :)
So I spent a few hours with a programmer on JustAnswer.com and learned that it's a VBA code I needed.
Nothing inherently wrong with it. Excel is used to import product data into our FileMakerPro based software. The this particular company is a custom tile co. They have a core 1000 products, offered in 4 different glazes (gloss, matte, crackle & old world) and there are 120 different color options available for gloss, a different 105 colors for matte, 65 for crackle, 40 for old world.
The view (visual) is 1 row of all product info in their respective columns, manufacturer, vendors, material, length, width, cost, etc. and then all color names and numbers each get their own row below that main product row. So that 1 product has 121 rows (for gloss anyway).
I thought that the repetitive: copy 120 rows, insert rows beneath each product row action, would use a macro.
However, I couldn't understand the description of the sheet so I requested for a sample sheet. I am glad to know that you have found the fix. If you feel this will help other users, please feel free to post the macro code here.
Please feel free to contact us if you need any further help.
