Move data from multiple columns copying 1st column each time

nalina · Apr 9th 2012

Hi All,

The data are arranged into 4 columns, 1st - product. 2nd - 2010 sales, 3 - 2011 sales, 4 - 2012 sales. How to re-arrange the table so that I have only these columns - product, year and units/revenue. Do you know macros or/and formula?

jproffer · Apr 9th 2012

Re: Move data from multiple columns copying 1st column each time

Could you post your actual workbook. All the numbers in that one seem to be had numbers (as opposed to formulas).

Also, post an example of what you want after the macro is ran.

The description you gave of what you want is hard to follow.

nalina · Apr 9th 2012

Re: Move data from multiple columns copying 1st column each time

Oh sorry
Just a moment

nalina · Apr 9th 2012

Re: Move data from multiple columns copying 1st column each time

If possible, to move only units >0, so that zero sales do not display at output page

Actually, I have searched e-net a lot. What I found is
formula
=INDEX($B$5:$D$26,MOD(ROWS(F$2:F3)-1,ROWS($B$5:$D$26))+1,INT((ROWS(F$2:F3)-1)/ROWS($B$5:$D$26))+1)

Here is a sample

nalina · Apr 9th 2012

Re: Move data from multiple columns copying 1st column each time

or macros

Code

Sub Kwijibo()
 
application.screenupdating = false
sheets("results").activate
range("a1").activate
sheets("input").activate
range("a1").activate
 
while activecell.value <> ""
 
n=1
label = activecell.value
 
 
while selection.offset(0,n) <> ""
item = selection.offset(0,n)
sheets("results").activate
activecell.value = label
selection.offset(0,1).value = item
selection.offset(1,0).select
sheets("input").activate
n=n+1
wend
 
selection.offset(1,0).select
 
wend
 
application.screenupdating = true
 
end sub

Display More

Both of them do not put year into separate column

trunten · Apr 9th 2012

Re: Move data from multiple columns copying 1st column each time

Try:

Code

Sub test()
    Dim a(), b(), c(), i As Long, j As Long, k As Long, n As Long
    With Sheets("input")
        With .Range("A2", .Cells(.Rows.Count, 1).End(xlUp)(1, 4))
            If .Cells(1, 1).Row < 2 Then Exit Sub
            a = .Value
            c = .Offset(-1, 0).Resize(1).Value
            n = Application.CountIf(.Offset(0, 1).Resize(, 3), "<>0") + 1
            ReDim b(1 To n, 1 To 3)
            b(1, 1) = "Product": b(1, 2) = "Year": b(1, 3) = "Units"
            k = 2
            For j = 2 To 4
                For i = 1 To UBound(a)
                    If a(i, j) <> 0 Then
                        b(k, 1) = a(i, 1)
                        b(k, 2) = c(1, j)
                        b(k, 3) = a(i, j)
                        k = k + 1
                    End If
                Next i
            Next j
        End With
    End With
    With Sheets("output")
        .UsedRange.ClearContents
        .Range("A1:C1").Resize(n).Value = b
    End With
End Sub

Display More

nalina · Apr 9th 2012

Re: Move data from multiple columns copying 1st column each time

trunten
Thank you!
Is there an automated way to get rid of zero-values? Not the filter ))

trunten · Apr 9th 2012

Re: Move data from multiple columns copying 1st column each time

Quote from nalina;603713

Is there an automated way to get rid of zero-values? Not the filter ))

I've edited my post. See the macro i posted.

nalina · Apr 9th 2012

Re: Move data from multiple columns copying 1st column each time

It works! Thank you so much!

trunten · Apr 9th 2012

Re: Move data from multiple columns copying 1st column each time

np

Move data from multiple columns copying 1st column each time

Participate now!

Share