Itemising individual items from a totals table

[SuzB17]

Hi,
We have a table that a list of furniture types in it and the total number of that type in each room in a building. I want to be able to create a list that itemises every piece of furniture individually. (Although furniture may be the same type they may have different finishes and therefore need to be listed out individually). This is sort of the opposite of COUNT / COUNTIFS. I ideally need to get from this:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Level 00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]type[/TD]
[TD]description[/TD]
[TD]code[/TD]
[TD]00A[/TD]
[TD]00B[/TD]
[TD]00C[/TD]
[TD]00D[/TD]
[TD]00E[/TD]
[TD]00F[/TD]
[/TR]
[TR]
[TD]seating[/TD]
[TD]lounge chair[/TD]
[TD]CH-003[/TD]
[TD]8[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]10[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]seating[/TD]
[TD]high back stool[/TD]
[TD]CH-007[/TD]
[TD]0[/TD]
[TD]4[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]seating[/TD]
[TD]stool[/TD]
[TD]CH-010[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

to this

[TABLE="width: 500"]
<tbody>[TR]
[TD]type[/TD]
[TD]description[/TD]
[TD]code[/TD]
[TD]level[/TD]
[TD]zone[/TD]
[/TR]
[TR]
[TD]seating[/TD]
[TD]lounge chair[/TD]
[TD]CH-003[/TD]
[TD]00[/TD]
[TD]00A[/TD]
[/TR]
[TR]
[TD]seating[/TD]
[TD]lounge chair[/TD]
[TD]CH-003[/TD]
[TD]00[/TD]
[TD]00A[/TD]
[/TR]
[TR]
[TD]seating[/TD]
[TD]lounge chair[/TD]
[TD]CH-003[/TD]
[TD]00[/TD]
[TD]00A[/TD]
[/TR]
[TR]
[TD]seating[/TD]
[TD]lounge chair[/TD]
[TD]CH-003[/TD]
[TD]00[/TD]
[TD]00A[/TD]
[/TR]
[TR]
[TD]seating[/TD]
[TD]lounge chair[/TD]
[TD]CH-003[/TD]
[TD]00[/TD]
[TD]00A[/TD]
[/TR]
[TR]
[TD]seating[/TD]
[TD]lounge chair[/TD]
[TD]CH-003[/TD]
[TD]00[/TD]
[TD]00A[/TD]
[/TR]
[TR]
[TD]seating[/TD]
[TD]lounge chair[/TD]
[TD]CH-003[/TD]
[TD]00[/TD]
[TD]00A[/TD]
[/TR]
</tbody>[/TABLE]


Effectively we have been given a list of furniture requirements from a client and we now need to be able to expand this list out to add further detail to each individual item. Is there an easy way to do this usuing formulas or am I looking at VBA? I've used VBA before but a while ago and I am still pretty much a newbie to it. We're trying to avoid listing this out manually as there is a risk of error and there is quite a list.

Thanks in advance.

 

[Rijnsent]

Hi SuzB17,
something like this should work, you might want to change the ranges etc.
Cheers,
Koen

Sub ItemizeList()

Set Sht = Worksheets("ABC")
Set ValueRange = Sht.Range("D3:I5")
Set StartWriteRng = Worksheets("Result").Range("A2")
ResultNr = 0

'Loop through all values in the range
For Each Cl In ValueRange
    'Only do something if there is a value > 0
    If Cl.Value > 0 Then
        ClRow = Cl.Row
        ClCol = Cl.Column
        NrRws = Cl.Value
        
        StartWriteRng.Offset(ResultNr, 0).Resize(NrRws, 1).Value = Sht.Cells(ClRow, 1).Value
        StartWriteRng.Offset(ResultNr, 1).Resize(NrRws, 1).Value = Sht.Cells(ClRow, 2).Value
        StartWriteRng.Offset(ResultNr, 2).Resize(NrRws, 1).Value = Sht.Cells(ClRow, 3).Value
        StartWriteRng.Offset(ResultNr, 3).Resize(NrRws, 1).Value = Sht.Range("C1").Value
        StartWriteRng.Offset(ResultNr, 4).Resize(NrRws, 1).Value = Sht.Cells(2, ClCol).Value
        
        ResultNr = ResultNr + NrRws
    End If
Next Cl

'Clean up variables
Set Sht = Nothing

End Sub

 

[SuzB17]

Thanks so much Koen - I'll give this a go and see how I get on.
Thanks
Suzanne