excel replicate rows

Microsoft Microsoft excel 2007 full vers...
April 15, 2010 at 03:50:24
Specs: Windows XP
I have a spreadsheet that has apparel with
columns for sizes and colours. The sizes and
colours are separated by commas. Is there an
automatic way to create a new row for each
colour/size combination. I would then also
want to append the colour and size to the sku
so as to kee them unique. Any ideas?

See More: excel replicate rows

Report •

April 15, 2010 at 05:21:34
You might be able to use Data...Text to Columns...Delimited by Comma and then Copy...Paste Special...Transpose.

It would help if you posted some examples of your input data and what you want the output to look like.

You can use the pre tags above the comments box to line up your data to make it easier to read. e.g.

     A       B       C       D
1 Data1    Data2   Data3   Data4
2 Data11   Data22  Data33  Data44
3  etc.

Report •

April 15, 2010 at 06:05:42
The text to columns idea is some help. The data looks like this

Style sizes colors
style 1 s, m, l white, red, black
style2 6,7,8 green, red, yellow

Report •

April 15, 2010 at 07:30:26
I asked that you use the pre tags (found above the Comments box) to line up your data.

When you don't use the pre tags it makes it difficult to see how your data is laid out since it all gets left justified.

I think it is supposed to look like this:

           A        B              C
  1     Style     Sizes         Colors
  2     style 1  s, m, l    white, red, black
  3     style 2  6, 7, 8    green, red, yellow

What I'm still not sure of is what you want you output to look like.

I can guess, and work up a solution based on that guess, but I'm lazy and don't want to solve your problem twice.

Please provide an example of the output that you are looking for.

Report •

Related Solutions

April 16, 2010 at 18:00:00

If your source data is on a Worksheet named "Sheet1" with a style name in column A, Sizes separated by commas in column B and colors, separated by commas in column C,
with headings on Row 1
and data starting on row 2,
the following macro will take this data and create a series of items on a Worksheet named "Sheet2" (This must exist and should be empty.
This data:

Style	Size	Color
style 1	s, m, l	white, red, black
style2 	6,7,8	green, red, yellow
wil become:
Style	Size	Color
style 1	s	white
style 1	s	red
style 1	s	black
style 1	m	white
style 1	m	red
style 1	m	black
style 1	l	white
style 1	l	red
style 1	l	black
style2 	6	green
style2 	6	red
style2 	6	yellow
style2 	7	green
style2 	7	red
style2 	7	yellow
style2 	8	green
style2 	8	red
style2 	8	yellow

To run the macro, I suggest you add a button to your source worksheet ("Sheet1").
From the Ribbon select Developer (If it's not visible go to the Office Button, select Excel options at the bottom and select the Popular tab and check the 'Show Developer tab in the Ribbon' box)

In Developer - Controls select Insert and choose the button icon.
Draw the button on the worksheet
In the 'Assign Macro' dialog box select 'New'

In the code window that opens enter this:

Option Explicit
Sub Button1_Click()
Dim rngStart As Range
Dim rngEnd As Range
Dim rngCell As Range
Dim rngDestStart As Range
Dim intDestOffst As Integer
Dim intSizes As Integer
Dim varSizeArry As Variant
Dim intColors As Integer
Dim varColorArry As Variant
Dim n As Integer
Dim o As Integer

On Error GoTo ErrHnd

'turn screen updating off
Application.ScreenUpdating = False

'set start of data in column A
Set rngStart = Worksheets("Sheet1").Range("A2")
'find end of data in column A
Set rngEnd = Worksheets("Sheet1"). _
            Range("A" & CStr(Application.Rows.Count)).End(xlUp)
'set start of destination data on Sheet2
Set rngDestStart = Worksheets("Sheet2").Range("A2")
'set destination offset counter
intDestOffst = 0

'loop through each row of source data
For Each rngCell In Worksheets("Sheet1").Range(rngStart, rngEnd)
    'split size text from column B into an Array
    varSizeArry = Split(rngCell.Offset(0, 1).Text, ",")
    'get the number of sizes
    intSizes = UBound(varSizeArry, 1)
    'split color text in column C into an Array
    varColorArry = Split(rngCell.Offset(0, 2).Text, ",")
    'get the number of colors
    intColors = UBound(varColorArry, 1)
    'create rows
    For n = 0 To intSizes
        For o = 0 To intColors
            rngDestStart.Offset(intDestOffst, 0).Value = rngCell.Text
            rngDestStart.Offset(intDestOffst, 1).Value = Trim(varSizeArry(n))
            rngDestStart.Offset(intDestOffst, 2).Value = Trim(varColorArry(o))
            'increment destination row offset
            intDestOffst = intDestOffst + 1
        Next o
    Next n
Next rngCell
'turn screen updating on again
Application.ScreenUpdating = True
Exit Sub

'error handler
'turn screen updating on again
Application.ScreenUpdating = True
End Sub

Note that Sub Button1_Click() and End sub will already be present, so don't duplicate them. Option explicit goes before Sub Button1_Click().

Click Save from the Visual Basic Menu.

Alt+f11 takes you back to the main Excel window.

Right click the button and Edit the name to something meaningful

As Macros cannot be undone with the Undo button, test this macro on a copy of your data. Always make a backup of your Workbook before running this macro. This code has only been tested on sample data, and it has not been tested in your environment, so test it on copies of your data to ensure that it works 'as expected'

After selecting any cell, the new command button should now respond to a click and run the macro.


Report •

April 26, 2010 at 07:18:22
Thanks for your detailed reply

Report •

April 26, 2010 at 08:42:34
You're welcome.

I hope that it was not only 'detailed', but that it actually worked!



Report •

Ask Question