Split grouped items into one per line

[Alan Coleman]

Hello,

I have 2 tables on different sheets in an excel file.
One table, grouped_range, has products grouped by qty.
The other table, split_range, wants the products to take up one lin
per single item. I hope my attached image can explain this better the
me.

Thanks for reading this and thanks to anyone who considers helping me.

A

Attachment filename: excelforum.jpg
Download attachment: http://www.excelforum.com/attachment.php?postid=49292

 

[Debra Dalgleish]

It's not clear what you need help with. Are you having a problem
creating the tables?

 

[Alan Coleman]

hi,
I want to count how many items there is on a line in grouped_range.
want these to be copyed over to another table but on one per line. ie
take out all the qty. The 1 of 4 side of it is not important reall
but would be helpfull.
This is so we can enter items quickly using qty, but them print out th
split_range page for a loading list.

Thanks
Ala

 

[Debra Dalgleish]

You could do this with a macro. In the following example, items from
Sheet1 are copied to Sheet2:

'==========================
Sub InsertRows()
Dim c As Range
Dim i As Integer
Dim r As Long
Dim r2 As Long
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")

r = ws1.Cells(Rows.Count, 1).End(xlUp).Row
r2 = 2
i = 1

ws2.Cells.Clear
ws2.Cells(1, 1).Value = "Item"
For Each c In ws1.Range(ws1.Cells(2, 1), ws1.Cells(r, 1))
If c.Value > 1 Then
i = c.Value
For i = 1 To i
ws2.Cells(r2, 1) = c.Offset(0, 1).Value
ws2.Cells(r2, 2) = i
ws2.Cells(r2, 3) = "of"
ws2.Cells(r2, 4) = c.Value
r2 = r2 + 1
Next
Else
ws2.Cells(r2, 1) = c.Offset(0, 1).Value
ws2.Cells(r2, 2) = i
ws2.Cells(r2, 3) = "of"
ws2.Cells(r2, 4) = c.Value
r2 = r2 + 1
End If
Next
End Sub
'=================================

 

[Alan Coleman]

Is there any way this can be done without using a macro? i've seen i
done in reverse with countif?

Thanks loads

Ala