CODEWITHSUNDEEP
excel
Stephen Hellyar
Stephen Hellyar

Reputation: 11

Create Multiple Rows Based on Columns

I need help.

I have an Excel file w/ the approximate following column structure:

In this initial construct each row is it's own Program Name (i.e., there is only one row for each program). Each budget year has its own column where budget data for a given year is entered.

I need to replace the individual year columns with a single YEAR column:

In this setup, there would be separate row for each of Program budget - so each program would have multiple rows for each year.

I have 3,000 lines in the original construct that I need to switch to the new format in order to feed my data visualization tool - otherwise it will not recognize each column as a single dimension (year).

Thanks

Upvotes: 1

Views: 3889

Answers (1)

satyrFrost
satyrFrost

Reputation: 413

If I understand this right, you have:

enter image description here

and you want

enter image description here

Here is really rough but tested VBA code to get you started. I am not sure if you have had experience with VBA. 

    Sub xxx()

    Sheets("Sheet1").Select

    Dim x As Integer
    x = 2

    For a = 2 To 3000

        Sheets("Sheet1").Select
        org = Range("A" & a).Value
        prog = Range("B" & a).Value
        y1 = Range("C" & a).Value
        y2 = Range("D" & a).Value
        y3 = Range("E" & a).Value

        Sheets("Sheet2").Select

        Range("A" & x).Value = org
        Range("B" & x).Value = prog
        Range("C" & x).Value = y1
        Range("C" & x + 1).Value = y2
        Range("C" & x + 2).Value = y3

        x = x + 3

    Next a

End Sub

Let me know if you need more explanation or better code.

Upvotes: 3

Related Questions