# splitting entries

#### BBQKING3

##### Board Regular
I’m looking for help on splitting entries.

I have an excel file with three columns and entries that look like this:

Part number / Date / Qty
XYZ / 2005-11-21 / 5

I need a macro that will look down this list, copy it to
a new sheet, splitting it into multiple entries of the same part number
with single quantities. In other words, 5 individual lines of quantity 1 for the same date.

Part number / Date / Qty
XYZ / 2005-11-21 / 1
XYZ / 2005-11-21 / 1
XYZ / 2005-11-21 / 1
XYZ / 2005-11-21 / 1
XYZ / 2005-11-21 / 1

Anyone have any ideas????

### Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Hi

assuming the source data is sheet1
display results in Sheet2
Code:
``````Sub test()
Dim a, i As Long, n As Long
a = Sheets("sheet1").Range("a1").CurrentRegion _
.Resize(, 3).Value
ReDim b(1 To 3, 1 To 1)
For i = 1 To 3
b(i, 1) = a(1, i)
Next
For i = 2 To UBound(a, 1)
If IsNumeric(a(i, 3)) And a(i, 3) > 0 Then
n = UBound(b, 2) + 1
ReDim Preserve b(1 To 3, 1 To n + a(i, 3) - 1)
For ii = n To n + a(i, 3) - 1
For iii = 1 To 3
b(iii, ii) = a(i, iii)
If iii = 3 Then b(iii, ii) = 1
Next
Next
End If
Next
With Sheets("sheet2").Range("a1")
.CurrentRegion.ClearContents
.Resize(UBound(b, 2), UBound(b, 1)) _
= Application.Transpose(b)
End With
Erase a, b
End Sub``````

Thanks

It works just fine.
Thanks and have a great day

Replies
8
Views
222
Replies
6
Views
117
Replies
3
Views
262
Replies
5
Views
149
Replies
10
Views
261

1,196,506
Messages
6,015,592
Members
441,903
Latest member
MG12345

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back