The same can be done using code. Try the below which will insert a new sheet
next to the active sheet and generate what you want. Try and feedback.


Sub Expand()

Dim rng1 As Range, rng2 As Variant, lngRow As Long
Dim ws1 As Worksheet, ws2 As Worksheet

Set ws1 = ActiveSheet
Set ws2 = Worksheets.Add(after:=ActiveSheet)
ws1.Columns("A:A").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=ws2.Range("A1"), Unique:=True
lngRow = 2
Set rng1 = ws2.Range("A2:A" & ws2.Cells(Rows.Count, "A").End(xlUp).Row)
rng2 = Array("a1", "a2", "a3", "a4", "a5", "a6", "a7", "a8", "a9", "a10")

For Each cell In rng1
ws2.Range("B" & lngRow).Resize(10).Value = cell.Text
ws2.Range("C" & lngRow).Resize(10).Value = WorksheetFunction.Transpose(rng2)
lngRow = lngRow + 10
Next

ws2.Range("D2:D" & ws2.Cells(Rows.Count, "C").End(xlUp).Row) = _
"=SUMPRODUCT((Sheet1!$A$1:$A$1000=B2)*(Sheet1!$B$1 :$B$1000=C2)," & _
"(Sheet1!$C$1:$C$1000))"
ws2.Range("B1:D1").Value = ws1.Range("A1:C1").Value: ws2.Columns(1).Delete

End Sub


If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

Suppose you have your data in Sheet1.

--In Sheet2 create a list of order numbers and codes as below. If you dont
have a list of orders; generate a list in ColA using DataFilterAdvanced
Filter'Unique records'. ColB will have the 10 codes.

order code
100000 a1
200000 a2
300001 a3
300002 a4
300003 a5
300004 a6
300005 a7
300006 a8
300007 a9
300008 a10
300009
300010
300011
300012
300013

--Run the below macro which will generate a list as you mentioned in Col C
and D

Sub Reproduce()
Dim rng1 As Range, rng2 As Range, lngRow As Long

lngRow = 2
Set rng1 = Range("A2:A16")
Set rng2 = Range("B2:B11")

For Each cell In rng1
Range("C" & lngRow).Resize(rng2.Rows.Count).Value = cell.Text
Range("D" & lngRow).Resize(rng2.Rows.Count).Value = rng2.Value
lngRow = lngRow + rng2.Rows.Count
Next
End Sub

--Now in column E cell E2 use the below formula which will bring the
relevant values. Copy the formula down as required.

=SUMPRODUCT((Sheet1!$A$1:$A$1000=C2)*(Sheet1!$B$1: $B$1000=D2),(Sheet1!$C$1:$C$1000))



If this post helps click Yes
---------------
Jacob Skaria


"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