table format

[Rick1001]

hi Guys

wondering if anyone can help me in changing to tale formatt
attached file will have all the details

The data will be big and will change everyweek, months will change as well, so automated is desired

 

[bosco_yip]

Hi,

See attachment.

Regards

 

[Nebu]

Since you asked for automation

Sub test()
Dim MyArray As Variant, MyArray1 As Variant

i& = Me.Cells(Rows.Count, 1).End(xlUp).Row

MyArray = Me.Range("A1:F" & i).Value
c& = Application.WorksheetFunction.Count(MyArray)

ReDim MyArray1(1 To c, 1 To 3)

For j& = 2 To UBound(MyArray)
    For m& = 2 To 6
        k = k + 1
        MyArray1(k, 1) = MyArray(j, 1)
        MyArray1(k, 2) = MyArray(1, m)
        MyArray1(k, 3) = MyArray(j, m)
    Next
Next

Sheet2.Range("A2").Resize(c, 3).Value = MyArray1

End Sub

Note output in sheet2.

 

[Rick1001]

thanks bosco

Nebu . thankyou - couldnt ask more perfect than this.but can we we use text "
January 2017 (month name)
"
like in the example instead 01/01/17 displaying as month name?

can we return item number in text plz

 

[Nebu]

Hi:

Use the following code:

Sub test()
Dim MyArray As Variant, MyArray1 As Variant

i& = Me.Cells(Rows.Count, 1).End(xlUp).Row

MyArray = Me.Range("A1:F" & i).Value
c& = Application.WorksheetFunction.Count(MyArray)

ReDim MyArray1(1 To c, 1 To 3)

For j& = 2 To UBound(MyArray)
    For m& = 2 To 6
        k = k + 1
        MyArray1(k, 1) = MyArray(j, 1)
        MyArray1(k, 2) = Format(MyArray(1, m), "mmmmm yyyy")
        MyArray1(k, 3) = MyArray(j, m)
    Next
Next

Sheet2.Range("A2").Resize(c, 3).Value = MyArray1

End Sub

Thanks

 

[Rick1001]

Hi nebu

now it giving in below fomat
February2 2017
March3 2017
April4 2017
May5 2017
January1 2017
February2 2017
March3 2017

and can it return item in text format

 

[Nebu]

Hi:

Use this file.

The code is fine you have to customise your date to get the format you need.
I have also converted your item into text. But I am not sure why you want to do this, as long as your original data is in text or numeric the code will pick up the data in the respective formats.

Thanks

 

[Rick1001]

Hi:

Use this file.

The code is fine you have to customise your date to get the format you need.
I have also converted your item into text. But I am not sure why you want to do this, as long as your original data is in text or numeric the code will pick up the data in the respective formats.

Thanks

Thanks Nebu. it works great