copy rows based on input in columns

[ssuzzie]

Hello,
I have a below table:
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Month x[/TD]
[TD]MONTH x+1[/TD]
[TD]Volume month x[/TD]
[TD]Volume month x+1[/TD]
[/TR]
[TR]
[TD]PRODUCT 1[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]100[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I would like to convert it to the following:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Month[/TD]
[TD]Volume[/TD]
[/TR]
[TR]
[TD]PRODUCT 1[/TD]
[TD]7[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]PRODUCT 1[/TD]
[TD]8[/TD]
[TD]50[/TD]
[/TR]
</tbody>[/TABLE]

Is there any easy way to do this?

Thanks a lot

 

[Joe4]

Welcome to the Board!

Are there always exactly 5 columns in your original table? Or might you have x+2, etc?
Do you want it to happen "in place" (overwriting the original data), or make this new list on another sheet?
Does the first line of data start in cell A2? If not, where?

 

[ssuzzie]

Hi Joe, thanks a lot for your reply.
I have simplified the table to post it here, so actually there r more columns but it is always a fixed number (about 20 columns), since there are also some other product characteristics in other columns. I also have month x+2 etc but also always the same amount of columns (until month +10). Making a list on another sheet will be perfect. First line always starts in A2 and I have many lines.
Thank you for your help!

 

[Joe4]

So, if there are 10, then there would be 10 Month columns (with headers), followed by the 10 Volume columns (with headers), is that right?
And if you have that many columns, will every record have an entry for every Month and Volume listed?

 

[ssuzzie]

Yes correct. Not all records will have all the columns filled in. Sometimes only one or two months with volume and the rest of months set to 0.

 

[Joe4]

So, the 0's only would appear in the Volumes column, but all the month columns should be filled out with the Month numbers (even if the volume is zero)? Is that a correct assessment of the data structure?

For those 0 volumes, do you want those listed in the results, or excluded?

We need to make sure that we get an accurate, complete picture of what we are working with. The issue with simplifying things in your example too much is that you may get an over-simplified solution that works for your test data, but not your actual data. So be sure to include all possibilities/wrinkles in your explanation.

 

[ssuzzie]

Initially the months numbers without volumes are also set to 0 but I could also change it or change the formatting in case it makes it easier for the solution. I will send tomorrow another sample just to clarify. Thanks!

 

[Joe4]

Also, please indicate if you want those "0 records" as part of the output, or you want them excluded from the output.

 

[ssuzzie]

Better to exclude them please

 

[Joe4]

Try this code:

Sub MyCopyMacro()

    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim lc As Long
    Dim lr As Long
    Dim r As Long
    Dim c As Long
    Dim num As Long
    Dim nr As Long
    
    Application.ScreenUpdating = False
    
'   Set source worksheet
    Set ws1 = Sheets("Sheet1")
'   Set desintation worksheet
    Set ws2 = Sheets("Sheet2")
    
'   Find last row with data on source sheet
    lr = ws1.Cells(Rows.Count, "A").End(xlUp).Row

'   Find last column in header row on source sheet
    lc = ws1.Cells(1, Columns.Count).End(xlToLeft).Column
    
'   Determine number of months
    num = (lc - 1) / 2
    
'   Put headers on destiantion sheet
    ws2.Range("A1") = "Product"
    ws2.Range("B1") = "Month"
    ws2.Range("C1") = "Volume"
    
'   Set first row to place data on destination sheet
    nr = 2
    
'   Loop through all data row on source sheet
    For r = 2 To lr
'       Loop through month columns
        For c = 2 To (num + 1)
'           Check to see if month is not 0
            If ws1.Cells(r, c) <> 0 Then
'               Populate product
                ws2.Cells(nr, "A") = ws1.Cells(r, "A")
'               Populate month
                ws2.Cells(nr, "B") = ws1.Cells(r, c)
'               Populate volume
                ws2.Cells(nr, "C") = ws1.Cells(r, c + num)
'               Increment row counter
                nr = nr + 1
            End If
        Next c
    Next r
    
    Application.ScreenUpdating = True
    
End Sub

You may need to change your sheet names to match your situation.