The code below is self explanitory. The easist way is to create a new sheet
with all the order numbers and codes. Then go through a 2nd loop putting in
the quantities. the new sheet has all the codes so there are more rows in
the new sheet than the old sheet. Because I sorted both sheets I can simply
compares the codes and order from both sheets and fill in the quanties when
they match.


Sub MakeOrders()

codes = Array("a1", "a2", "a3", "a4", "a5", "a6", "a7", "a8", "a9", "a10")

Set Oldsht = Sheets("Sheet1")
Set Newsht = Sheets.Add(after:=Sheets(Sheets.Count))

'create sheet with oders and codes without quantities
With Oldsht
'copy header row
.Rows(1).Copy Destination:=Newsht.Rows(1)
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
'sort original sheet
.Rows("1:" & LastRow).Sort _
header:=xlYes, _
key1:=.Range("A1"), _
order1:=xlAscending, _
key2:=.Range("B1"), _
order2:=xlAscending

OldOrder = ""
NewRowCount = 2
For OldRowCount = 2 To LastRow
Order = .Range("A" & OldRowCount)
If OldOrder < Order Then
'add the order number and 10 codes to new sheet
For Each itm In codes
With Newsht
.Range("A" & NewRowCount) = Order
.Range("B" & NewRowCount) = itm
NewRowCount = NewRowCount + 1
End With
Next itm
OldOrder = Order
End If
Next OldRowCount

End With

With Newsht

'now sort new sheet to get order numbers in same order and old sheet
NewLastRow = .Range("A" & Rows.Count).End(xlUp).Row
.Rows("1:" & NewLastRow).Sort _
header:=xlYes, _
key1:=.Range("A1"), _
order1:=xlAscending, _
key2:=.Range("B1"), _
order2:=xlAscending

'now put quantities in new sheet
OldRowCount = 2
For NewRowCount = 2 To NewLastRow
NewOrder = .Range("A" & NewRowCount)
NewCode = .Range("B" & NewRowCount)
OldOrder = Oldsht.Range("A" & OldRowCount)
OldCode = Oldsht.Range("B" & OldRowCount)
If OldOrder = NewOrder And _
OldCode = NewCode Then

Qty = Oldsht.Range("c" & OldRowCount)
.Range("C" & NewRowCount) = Qty
OldRowCount = OldRowCount + 1
End If
Next NewRowCount
End With

End Sub


"tina" wrote:

Hi
I need some advice of best way to do following
I have a sheet with a number of orders where each order can have between 1
and 10 codes . I need each order to have 10 codes (rows) with blank rows ( in
sequence)where no order for specific code
example
order code qty
100000 a1 2
100000 a3 5
100000 a6 5
200000 a2 4
200000 a3 4

need to look like
order code qty
100000 a1 2
100000 a2
100000 a3 5
100000 a4
100000 a5
100000 a6 5
100000 a7
100000 a8
100000 a9
100000 a10
200000 a1
200000 a2 4
200000 a3 4
200000 a4
etc
I have tried for counter anf if statements but getting very complicated and
think there must be better way any tips would be great
Thank you
Tina