How to transpose with multiple conditions

Hi, i am new to VBA and can't get my head work to figure out simple things for VBA.

i have a data sheet from which i want to show the Data and $$ for Same Product with Same Color in one column rather than in different rows. So, what i have now is below table:

FYI, i have attached the original File here if you could spare some time for the whole file.

[TABLE="width: 609"]

[tr]

[td][/td]

[td][/td]

[td]

Sold_On/ Amount

[/td]

[td]

Sold_On/ Amount

[/td]

[td]

Sold_On/ Amount

[/td]

[td]

Sold_On/ Amount

[/td]

[/tr]

[tr]

[td]

Color

[/td]

[td]

Product

[/td]

[TD="align: right"]1/1/2014[/TD]
[TD="align: right"]2/1/2014[/TD]
[TD="align: right"]3/1/2014[/TD]
[TD="align: right"]4/1/2014[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]$14,108[/TD]
[TD="align: right"]$19,375[/TD]
[TD="align: right"]$18,319[/TD]
[TD="align: right"]$11,370[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]$19,992[/TD]
[TD="align: right"]$11,263[/TD]
[TD="align: right"]$11,632[/TD]
[TD="align: right"]$5,545[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Socks

[/td]

[TD="align: right"]$13,614[/TD]
[TD="align: right"]$18,986[/TD]
[TD="align: right"]$17,273[/TD]
[TD="align: right"]$17,886[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Bandpipe

[/td]

[TD="align: right"]$6,936[/TD]
[TD="align: right"]$1,818[/TD]
[TD="align: right"]$13,509[/TD]
[TD="align: right"]$3,038[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Wagon

[/td]

[TD="align: right"]$17,146[/TD]
[TD="align: right"]$8,041[/TD]
[TD="align: right"]$15,384[/TD]
[TD="align: right"]$15,725[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Train

[/td]

[TD="align: right"]$1,812[/TD]
[TD="align: right"]$12,081[/TD]
[TD="align: right"]$9,921[/TD]
[TD="align: right"]$8,938[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Horn

[/td]

[TD="align: right"]$5,200[/TD]
[TD="align: right"]$3,778[/TD]
[TD="align: right"]$13,833[/TD]
[TD="align: right"]$3,309[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Sweater

[/td]

[TD="align: right"]$2,766[/TD]
[TD="align: right"]$12,916[/TD]
[TD="align: right"]$16,832[/TD]
[TD="align: right"]$6,103[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Wafer

[/td]

[TD="align: right"]$2,109[/TD]
[TD="align: right"]$10,388[/TD]
[TD="align: right"]$8,722[/TD]
[TD="align: right"]$10,043[/TD]

[/tr]

[tr]

[td]

Blue

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]$18,017[/TD]
[TD="align: right"]$18,779[/TD]
[TD="align: right"]$13,261[/TD]
[TD="align: right"]$15,591[/TD]

[/tr]

[tr]

[td]

Blue

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]$17,144[/TD]
[TD="align: right"]$16,396[/TD]
[TD="align: right"]$8,211[/TD]
[TD="align: right"]$7,476[/TD]

[/tr]

[tr]

[td]

Blue

[/td]

[td]

Socks

[/td]

[TD="align: right"]$7,237[/TD]
[TD="align: right"]$6,707[/TD]
[TD="align: right"]$1,358[/TD]
[TD="align: right"]$10,067[/TD]

[/tr]

[tr]

[td]

Blue

[/td]

[td]

Bandpipe

[/td]

[TD="align: right"]$4,693[/TD]
[TD="align: right"]$19,866[/TD]
[TD="align: right"]$8,730[/TD]
[TD="align: right"]$5,143[/TD]

[/tr]

[tr]

[td]

Blue

[/td]

[td]

Wagon

[/td]

[TD="align: right"]$4,647[/TD]
[TD="align: right"]$7,614[/TD]
[TD="align: right"]$2,572[/TD]
[TD="align: right"]$1,812[/TD]

[/tr]

[tr]

[td]

Blue

[/td]

[td]

Train

[/td]

[TD="align: right"]$17,675[/TD]
[TD="align: right"]$217[/TD]
[TD="align: right"]$148[/TD]
[TD="align: right"]$4,965[/TD]

[/tr]

[tr]

[td]

Blue

[/td]

[td]

Horn

[/td]

[TD="align: right"]$15,810[/TD]
[TD="align: right"]$324[/TD]
[TD="align: right"]$12,806[/TD]
[TD="align: right"]$8,502[/TD]

[/tr]

[tr]

[td]

Blue

[/td]

[td]

Sweater

[/td]

[TD="align: right"]$17,516[/TD]
[TD="align: right"]$14,160[/TD]
[TD="align: right"]$19,169[/TD]
[TD="align: right"]$18,934[/TD]

[/tr]

[tr]

[td]

Blue

[/td]

[td]

Wafer

[/td]

[TD="align: right"]$12,210[/TD]
[TD="align: right"]$10,408[/TD]
[TD="align: right"]$19,003[/TD]
[TD="align: right"]$16,886[/TD]

[/tr]

[/TABLE]

What i wan to do is below (i have done it manually, silly me, as I can't figure out the VBA to do it):

[TABLE="width: 384"]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

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

[/tr]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]2/1/2014[/TD]
[TD="align: right"]19375[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]3/1/2014[/TD]
[TD="align: right"]18319
[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]4/1/2014[/TD]
[TD="align: right"]11370[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]5/1/2014[/TD]
[TD="align: right"]3025[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]6/1/2014[/TD]
[TD="align: right"]17140[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]7/1/2014[/TD]
[TD="align: right"]8688[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]8/1/2014[/TD]
[TD="align: right"]15518[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]9/1/2014[/TD]
[TD="align: right"]13092[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]10/1/2014[/TD]
[TD="align: right"]7811[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]11/1/2014[/TD]
[TD="align: right"]19962[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]12/1/2014[/TD]
[TD="align: right"]2687[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

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

[/tr]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]2/1/2015[/TD]
[TD="align: right"]13660[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]3/1/2015[/TD]
[TD="align: right"]6092[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]4/1/2015[/TD]
[TD="align: right"]2307[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]5/1/2015[/TD]
[TD="align: right"]19864[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]6/1/2015[/TD]
[TD="align: right"]15256[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]7/1/2015[/TD]
[TD="align: right"]16101[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]8/1/2015[/TD]
[TD="align: right"]18928[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]9/1/2015[/TD]
[TD="align: right"]6507[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]10/1/2015[/TD]
[TD="align: right"]1807[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]11/1/2015[/TD]
[TD="align: right"]5512[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]12/1/2015[/TD]
[TD="align: right"]17524[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

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

[/tr]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]2/1/2016[/TD]
[TD="align: right"]10616[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]3/1/2016[/TD]
[TD="align: right"]19571[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]4/1/2016[/TD]
[TD="align: right"]7746[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]5/1/2016[/TD]
[TD="align: right"]18545[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]6/1/2016[/TD]
[TD="align: right"]9065[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]7/1/2016[/TD]
[TD="align: right"]15921[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]8/1/2016[/TD]
[TD="align: right"]11860[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]9/1/2016[/TD]
[TD="align: right"]2991[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]10/1/2016[/TD]
[TD="align: right"]7884[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]11/1/2016[/TD]
[TD="align: right"]8290[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Chocolate

[/td]

[TD="align: right"]12/1/2016[/TD]
[TD="align: right"]9462[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

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

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]2/1/2014[/TD]
[TD="align: right"]11263[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]3/1/2014[/TD]
[TD="align: right"]11632[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]4/1/2014[/TD]
[TD="align: right"]5545[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]5/1/2014[/TD]
[TD="align: right"]12444[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]6/1/2014[/TD]
[TD="align: right"]8074[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]7/1/2014[/TD]
[TD="align: right"]7390[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]8/1/2014[/TD]
[TD="align: right"]18958[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]9/1/2014[/TD]
[TD="align: right"]6413[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]10/1/2014[/TD]
[TD="align: right"]7494[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]11/1/2014[/TD]
[TD="align: right"]14962[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]12/1/2014[/TD]
[TD="align: right"]13126[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

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

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]2/1/2015[/TD]
[TD="align: right"]7933[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]3/1/2015[/TD]
[TD="align: right"]8224[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]4/1/2015[/TD]
[TD="align: right"]4454[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]5/1/2015[/TD]
[TD="align: right"]7900[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]6/1/2015[/TD]
[TD="align: right"]19197[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]7/1/2015[/TD]
[TD="align: right"]15770[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]8/1/2015[/TD]
[TD="align: right"]10173[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]9/1/2015[/TD]
[TD="align: right"]6322[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]10/1/2015[/TD]
[TD="align: right"]1380[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]11/1/2015[/TD]
[TD="align: right"]19965[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]12/1/2015[/TD]
[TD="align: right"]3163[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

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

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]2/1/2016[/TD]
[TD="align: right"]17032[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]3/1/2016[/TD]
[TD="align: right"]16448[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]4/1/2016[/TD]
[TD="align: right"]1686[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]5/1/2016[/TD]
[TD="align: right"]3470[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]6/1/2016[/TD]
[TD="align: right"]4732[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]7/1/2016[/TD]
[TD="align: right"]2336[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]8/1/2016[/TD]
[TD="align: right"]14799[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]9/1/2016[/TD]
[TD="align: right"]9654[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]10/1/2016[/TD]
[TD="align: right"]1856[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]11/1/2016[/TD]
[TD="align: right"]17276[/TD]

[/tr]

[tr]

[td]

Green

[/td]

[td]

Biscuit

[/td]

[TD="align: right"]12/1/2016[/TD]
[TD="align: right"]18381[/TD]

[/tr]

[/TABLE]
and so on for all the products.

Would you guys be help me at all?
FYI, i have attached the original File here if you could spare some time for the whole file.

Re: How to transpose with multiple conditions

Hello AlanSidman

Many thnaks for your quick and kind help. Let me run this and i will come back with the result.

Re: How to transpose with multiple conditions

Hello AlanSidman

It is working! I have to make some changes to fit to real data but i think i can handle it. Many thanks again for your help!!

Re: How to transpose with multiple conditions

Hi

Is there any easy way to write these codes, i mean to say without any fancy command like formatting, etc. As i am new to VBA World, i just want to have the codes that are required to finish the job, nothing more, the original code that i got does the job beautifully but i hardly understand it. is it possible to get an easy VBA "Sub-End Sub" Code to complete this job.

Quote from AlanSidman;780292

Ok. give this a try

Code

Option Explicit




Sub transp()
    Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = Sheets("Raw_Data")
    Set s2 = Sheets("Template_VBA")
    Dim lr As Long
    lr = s1.Range("A" & Rows.Count).End(xlUp).Row
    Dim lrT As Long, i As Long
    Dim rng As Range
    Set rng = s1.Range("C3:AL3")
    Application.ScreenUpdating = False
    With s1
        For i = 4 To lr
            lrT = s2.Range("D" & Rows.Count).End(xlUp).Row
            .Range("A" & i & ":B" & i).Copy s2.Range("A" & lrT + 1)
            rng.Copy
            s2.Range("C" & lrT + 1).PasteSpecial xlPasteValues, , , True
            .Range("C" & i & ":AL" & i).Copy
            s2.Range("D" & lrT + 1).PasteSpecial xlPasteValues, , , True
        Next i
    End With
    Application.CutCopyMode = False




    With s2
        lrT = .Range("D" & Rows.Count).End(xlUp).Row
        For i = 3 To lrT
            .Range("C2:C" & lrT).NumberFormat = "mm/dd/yyyy"
            .Range("D2:D" & lrT).NumberFormat = "$#,##0."
            If .Range("A" & i) = "" Then
                .Range("A" & i) = .Range("A" & i - 1)
                .Range("B" & i) = .Range("B" & i - 1)
            End If
        Next i
    End With
    Application.ScreenUpdating = True
    MsgBox "complete"
End Sub

Display More

Re: How to transpose with multiple conditions

Quote from AlanSidman;780373

Simple answer is NO. VBA is a logical step by step programming language. Of course you can always record a macro for simple issues, but this one involves looping through a spreadsheet, copying data and then pasting special transposed. It then does a loop to fill the blank spaces and formats certain columns. If you are interested in learning VBA, then I would start with some simple tasks, record the macro and then modify it for more advanced features.

Hi AlanSidman

Okay, got it, will try again to see if i can understand it, wish there were easy codes for people like me : )

Re: How to transpose with multiple conditions

Quote from AlanSidman;780419

below I have added comments to the code to explain exactly what is happening.

Hi AlanSidman

Many thanks, this really helps, easy to understand (at least for me). have a great day, Sir!