Formatting - VBA

mmdionisio · Nov 8th 2013

Hi Good Evening,
What I'm trying to do is to copy my data to another work sheet but with the different format like column to row..

I'll just post it here the sample expected result for further visualization..

thanks

*because my net is too slow, ozgrid always failed to attach my excel file so i'll just paste it here

[TABLE="width: 1273"]

[tr]

[td]

item CODE

[/td]

[td]

item DESCRIPTION

[/td]

[td]

Cost

[/td]

[td]

store 1

[/td]

[td]

store2

[/td]

[td]

store3

[/td]

[td]

store4

[/td]

[td]

store5

[/td]

[td]

store6

[/td]

[td]

store7

[/td]

[td]

store8

[/td]

[td][/td]

[/tr]

[tr]

[td][/td]

[td]

qty

[/td]

[td]

qty

[/td]

[td]

qty

[/td]

[td]

qty

[/td]

[td]

qty

[/td]

[td]

qty

[/td]

[td]

qty

[/td]

[td]

qty

[/td]

[td][/td]

[/tr]

[tr]

[td]

0003

[/td]

[td]

item1

[/td]

[td]

46.80

[/td]

[td]

3.00

[/td]

[td][/td]

[td]

2.00

[/td]

[td][/td]

[td]

1.00

[/td]

[td][/td]

[/tr]

[tr]

[td]

0117

[/td]

[td]

item 2

[/td]

[TD="align: right"]61.13[/TD]

[td]

7.00

[/td]

[td]

26.00

[/td]

[td]

46.00

[/td]

[td]

19.00

[/td]

[td]

11.00

[/td]

[td]

26.00

[/td]

[td]

4.00

[/td]

[td]

11.00

[/td]

[td][/td]

[/tr]

[tr]

[td]

etc..

[/td]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[TD="colspan: 3"]assuming this is another worksheet[/TD]

[td][/td]

[TD="colspan: 7"]Hi, what Im trying to do is to format my data into that using vba[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[TD="colspan: 6"]*it includes formatting of column into row (store name,qty)[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[td]

Store Name

[/td]

[td]

Item Code

[/td]

[td]

Item Description

[/td]

[td]

Quantity

[/td]

[td]

Price

[/td]

[td]

Total Amount

[/td]

[td][/td]

[TD="colspan: 4"]* automatic it has total amount[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[td]

store1

[/td]

[td]

0003

[/td]

[td]

item1

[/td]

[TD="align: right"]3[/TD]

[td]

46.80

[/td]

[TD="align: right"]140.4[/TD]

[td][/td]

[TD="colspan: 9"]*it will skip the column that has no value(empty cell) and will not transfer it into row[/TD]

[/tr]

[tr]

[td][/td]

[td]

store5

[/td]

[td]

0003

[/td]

[td]

item1

[/td]

[TD="align: right"]2[/TD]

[td]

46.80

[/td]

[TD="align: right"]93.6[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[td]

store8

[/td]

[td]

0003

[/td]

[td]

item1

[/td]

[TD="align: right"]1[/TD]

[td]

46.80

[/td]

[TD="align: right"]46.8[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[td]

store1

[/td]

[td]

0117

[/td]

[td]

item2

[/td]

[TD="align: right"]7[/TD]
[TD="align: right"]61.13[/TD]
[TD="align: right"]427.9433[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[td]

store2

[/td]

[td]

0117

[/td]

[td]

item2

[/td]

[TD="align: right"]26[/TD]
[TD="align: right"]61.13[/TD]
[TD="align: right"]1589.504[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[td]

store3

[/td]

[td]

0117

[/td]

[td]

item2

[/td]

[TD="align: right"]46[/TD]
[TD="align: right"]61.13[/TD]
[TD="align: right"]2812.199[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[td]

store4

[/td]

[td]

0117

[/td]

[td]

item2

[/td]

[TD="align: right"]19[/TD]
[TD="align: right"]61.13[/TD]
[TD="align: right"]1161.56[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[td]

store5

[/td]

[td]

0117

[/td]

[td]

item2

[/td]

[TD="align: right"]11[/TD]
[TD="align: right"]61.13[/TD]
[TD="align: right"]672.4823[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[td]

store6

[/td]

[td]

0117

[/td]

[td]

item2

[/td]

[TD="align: right"]26[/TD]
[TD="align: right"]61.13[/TD]
[TD="align: right"]1589.504[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[td]

store7

[/td]

[td]

0117

[/td]

[td]

item2

[/td]

[TD="align: right"]4[/TD]
[TD="align: right"]61.13[/TD]
[TD="align: right"]244.539[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[td]

store8

[/td]

[td]

0117

[/td]

[td]

item2

[/td]

[TD="align: right"]11[/TD]
[TD="align: right"]61.13[/TD]
[TD="align: right"]672.4823[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[td]

etc…

[/td]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[/TABLE]

Mumps · Nov 8th 2013

Re: Formatting - VBA

Hello D. Try:

Code

Sub Test()
    Application.ScreenUpdating = False
    Dim bottomB As Long
    bottomB = Range("B" & Rows.Count).End(xlUp).Row
    Dim bottomB2 As Long
    Dim lCol As Long
    lCol = Range("IV1").End(xlToLeft).Column
    Dim rngR As Range
    Dim rngC As Range
    For Each rngR In Range("B3:B" & bottomB)
        For Each rngC In Range(Cells(rngR.Row, "D"), Cells(rngR.Row, lCol))
            If rngC <> "" Then
                rngR.Copy Sheets("Sheet2").Cells(Rows.Count, "C").End(xlUp).Offset(1, 0)
                Cells(1, rngC.Column).Copy Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
                Cells(rngC.Row, 1).Copy Sheets("Sheet2").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
                rngC.Copy Sheets("Sheet2").Cells(Rows.Count, "D").End(xlUp).Offset(1, 0)
                rngR.Offset(0, 1).Copy Sheets("Sheet2").Cells(Rows.Count, "E").End(xlUp).Offset(1, 0)
            End If
        Next rngC
    Next rngR
    bottomB2 = Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp).Row
    Sheets("Sheet2").Cells(Rows.Count, "F").End(xlUp).Offset(1, 0).Formula = "=(D2*E2)"
    Sheets("Sheet2").Range("F2").Copy Sheets("Sheet2").Range("F3:F" & bottomB2)
    Application.ScreenUpdating = True
End Sub

Display More

Herbds7 · Nov 8th 2013

Re: Formatting - VBA

Excel 2010, Table, PivotTable
No code, no formulas needed.
http://www.mediafire.com/downl…5fco8ygkgg/11_08_13a.xlsx

mmdionisio · Nov 9th 2013

Re: Formatting - VBA

hi mumps thanks it works

mmdionisio · Nov 9th 2013

Re: Formatting - VBA

hi mumps one more thing..what if i only want to get the store1-store6 not the entire column of stores(store1-store7)

Thanks

jindon · Nov 9th 2013

Re: Formatting - VBA

Quote from mmdionisio;690830

what if i only want to get the store1-store6 not the entire column of stores(store1-store7)

Code

Sub test()
    Dim a, b, i As Long, ii As Long, iii As Long, n As Long
    a = Sheets("sheet1").Cells(1).CurrentRegion.Value
    ReDim b(1 To UBound(a, 1) * UBound(a, 2), 1 To 6)
    For i = 3 To UBound(a, 1)
        For ii = 4 To UBound(a, 2)
            If (a(i, ii) <> "") * (Replace(a(1, ii), " ", "") Like "store[1-6]") Then
                n = n + 1: b(n, 1) = a(1, ii)
                For iii = 1 To 3
                    b(n, iii + 1) = a(i, iii)
                Next
                b(n, 5) = a(i, ii): b(n, 6) = b(n, 4) * b(n, 5)
            End If
        Next
    Next
    Sheets("sheet2").Cells(1).Resize(n, 6).Value = b
End Sub

Display More

mmdionisio · Nov 9th 2013

Re: Formatting - VBA

hi jindon, actually on my original data I have different store names(not limited only to the store 1-7), so I think the condition will not work out.
How about I don't like to get the last 2 columns to become the rows (I just check all my original data and only column that I dont want to get is the last 2)

or the column who has a value = "TOTAL"

Thanks

jindon · Nov 9th 2013

Re: Formatting - VBA

You need to upload a sample workbook with before/after format in order to avoid any misunderstanding and not to waste the time for both of us.

mmdionisio · Nov 9th 2013

Re: Formatting - VBA

Hi jindon..

Is it ok I'll just copy it here, I got a slower net my file can't attach

[TABLE="width: 976"]

[tr]

[td]

CODE

[/td]

[td]

DESCRIPTION

[/td]

[td]

Cost

[/td]

[td]

Store Price

[/td]

[td]

store Bacolod

[/td]

[td]

store location4

[/td]

[td]

other name location3

[/td]

[td]

store name123 location2

[/td]

[td]

store name location1

[/td]

[td]

TOTAL

[/td]

[td]

TOTAL

[/td]

[td][/td]

[/tr]

[tr]

[td][/td]

[td]

Aug-12

[/td]

[td]

Aug-12

[/td]

[td]

OFFTAKE

[/td]

[td]

OFFTAKE

[/td]

[td]

OFFTAKE

[/td]

[td]

OFFTAKE

[/td]

[td]

OFFTAKE

[/td]

[td]

OFFTAKE

[/td]

[td]

VALUE

[/td]

[td][/td]

[/tr]

[tr]

[td]

111

[/td]

[td]

prod1

[/td]

[td]

46.80

[/td]

[td]

84.75

[/td]

[td]

3.00

[/td]

[td][/td]

[td]

2.00

[/td]

[td]

53,453.00

[/td]

[td]

453.00

[/td]

[td][/td]

[/tr]

[tr]

[td]

222

[/td]

[td]

prod2

[/td]

[TD="align: right"]61.13[/TD]
[TD="align: right"]109.75[/TD]

[td]

7.00

[/td]

[td]

26.00

[/td]

[td]

46.00

[/td]

[td]

19.00

[/td]

[td]

11.00

[/td]

[td]

453.00

[/td]

[td]

4,534.00

[/td]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"]* I ve got a long list of rows and columns so I'll just shorten it[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"]* I cant show the real data, a bit confidential :D[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[TD="colspan: 3"]assume this is a new worksheet using vba[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[td]

Store Code

[/td]

[td]

Store Name

[/td]

[td]

Area

[/td]

[td]

Material ID

[/td]

[td]

Comp Item Desc

[/td]

[td]

Division

[/td]

[td]

Item Code

[/td]

[td]

Item Desc

[/td]

[td]

Quantity

[/td]

[td]

Price

[/td]

[TD="colspan: 2"]Total Amount[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[td]

storebacolod

[/td]

[td]

bacolod

[/td]

[td][/td]

[TD="align: right"]111[/TD]

[td]

prod1

[/td]

[TD="align: right"]3[/TD]
[TD="align: right"]46.8[/TD]
[TD="align: right"]140.4[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[td]

store name location1

[/td]

[td]

location1

[/td]

[td][/td]

[TD="align: right"]111[/TD]

[td]

prod1

[/td]

[TD="align: right"]2[/TD]
[TD="align: right"]46.8[/TD]
[TD="align: right"]93.6[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[td]

store Bacolod

[/td]

[td]

bacolod

[/td]

[td][/td]

[TD="align: right"]222[/TD]

[td]

prod2

[/td]

[TD="align: right"]7[/TD]
[TD="align: right"]61.13[/TD]
[TD="align: right"]427.91[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[td]

store location4

[/td]

[td]

location4

[/td]

[td][/td]

[TD="align: right"]222[/TD]

[td]

prod2

[/td]

[TD="align: right"]26[/TD]
[TD="align: right"]61.13[/TD]
[TD="align: right"]1589.38[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[td]

other name location3

[/td]

[td]

location3

[/td]

[td][/td]

[TD="align: right"]222[/TD]

[td]

prod2

[/td]

[TD="align: right"]46[/TD]
[TD="align: right"]61.13[/TD]
[TD="align: right"]2811.98[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[td]

store name123 location2

[/td]

[td]

location2

[/td]

[td][/td]

[TD="align: right"]222[/TD]

[td]

prod2

[/td]

[TD="align: right"]19[/TD]
[TD="align: right"]61.13[/TD]
[TD="align: right"]1161.47[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"]* here's the correct format[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 5"]* store code, item, desc material id, div are naturally blank cells[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[/TABLE]
THANKS

jindon · Nov 9th 2013

Re: Formatting - VBA

No, I can not make a proper table from your post.

It's really messy here.

mmdionisio · Nov 9th 2013

Re: Formatting - VBA

or can I get your email, maybe i can send it to you there..

thanks

mmdionisio · Nov 10th 2013

Re: Formatting - VBA

Hi the codes of mumps are already ok, I just wanted to add what if I don't like to include the last 2 columns to become the rows..

anyone?

Thanks

jindon · Nov 10th 2013

Re: Formatting - VBA

Code

Sub test()
    Dim a, b, i As Long, ii As Long, iii As Long, n As Long
    a = Sheets("sheet1").Cells(1).CurrentRegion.Value
    ReDim b(1 To UBound(a, 1) * UBound(a, 2), 1 To 6)
    For i = 3 To UBound(a, 1)
        For ii = 4 To UBound(a, 2) - 2
            n = n + 1: b(n, 1) = a(1, ii)
            For iii = 1 To 3
                b(n, iii + 1) = a(i, iii)
            Next
            b(n, 5) = a(i, ii): b(n, 6) = b(n, 4) * b(n, 5)
        Next
    Next
    Sheets("sheet2").Cells(1).Resize(n, 6).Value = b
End Sub

Display More

mmdionisio · Nov 10th 2013

Re: Formatting - VBA

Hi Jindon,

Your first code is already ok like mumps.. now you're 2nd code don't select the last 2 columns to become rows which is ok..

but my first criteria which is to skip the column that has no value(see my first post) didn't follow..

Thanks

mmdionisio · Nov 10th 2013

Re: Formatting - VBA

Hi this is solved

I just add modify mumps code
from

Code

lCol = wb.Worksheets("Sheet4").Range("IV1").End(xlToLeft).Column

to

Code

lCol = wb.Worksheets("Sheet4").Range("IV1").End(xlToLeft).Column - 2

Thanks

jindon · Nov 10th 2013

Re: Formatting - VBA

Code

Sub test() 
    Dim a, b, i As Long, ii As Long, iii As Long, n As Long 
    a = Sheets("sheet1").Cells(1).CurrentRegion.Value 
    Redim b(1 To UBound(a, 1) * UBound(a, 2), 1 To 6) 
    For i = 3 To UBound(a, 1) 
        if a(i,ii)<>"" then
        For ii = 4 To UBound(a, 2) - 2 
            n = n + 1: b(n, 1) = a(1, ii) 
            For iii = 1 To 3 
                b(n, iii + 1) = a(i, iii) 
            Next 
            b(n, 5) = a(i, ii): b(n, 6) = b(n, 4) * b(n, 5) 
        Next 
end if
    Next 
    Sheets("sheet2").Cells(1).Resize(n, 6).Value = b 
End Sub

Display More

Formatting - VBA

Participate now!

Share

Users Viewing This Thread