Create a Database

[tljenkin]

Hi All

I have a quick query. Please help if you can.

I have a report that has weeks going along the top right up to 244 weeks. This is adjacent to the other unique fields eg name, amount, code etc

I would like to transpose that and have weeks as one field going down the side.

So to summarise there are about 254 columns by about 300 rows of data, I would like to have 76,200 rows of data by just 11 columns, one being the newly created week field.

Any help would be much appreciated.

Currently it is

name, code ... (8 more fields) week 1 week 2 to week 244

x 300 rows or records

============

Would like

week, name, code, amount (7 more fields)

x

76200 rows or records

 

[tljenkin]

Any one out there who can help?

 

[tljenkin]

From

Seq No Unique Type Regime Cohort Heritage Prod Area Channel Week 1 Week 2 Week 3 etc to week 244



to this

Week Seq No Unique Type Regime Cohort Heritage Prod Area Channel Pro/Re Direct/CMC
1
2
3
4
5
6

 

[StuLux]

This isn't physically possible - think about it - each row of data currently holds values for a unique combination of Seq No, Unique Type, Regime etc. and then a set of values for each week. To do what you want you would have to duplicate all of the unique combinations and have a duplicate row for each week.

Have you tried using a pivot table to get what you want?

 

[Hermanito]

I am missing something I think...

You now have data in a 254x300 range of cells (=76200 cells) and you want to transpose it into a 76200x11 range... that is 11x more cells, where is all that extra data coming from?
I don't see how you can go from your current as-is to your to-be...

Also, to have 76200 rows, you need at least Excel 2007, which version do you use?

 

[tljenkin]

The data is currently 10 unique columns plus another 244 weeks ie week 1 - 244

There are 300 records.

What I want is to eliminate the 244 columns to the right and create just one column which will be the "week column"

So I will end up with 11 columns of data but with the weeks going downwards ie week1 x row 1, week 1 x row 2 etc. Repeat week 2 thru 244

Hence 11 columns (static) but 300 records duplicated 244 times (weeks 1-244) = 73,200

Yes I am using 2007 & 2010 as well

Thanks

I am missing something I think...

You now have data in a 254x300 range of cells (=76200 cells) and you want to transpose it into a 76200x11 range... that is 11x more cells, where is all that extra data coming from?
I don't see how you can go from your current as-is to your to-be...

Also, to have 76200 rows, you need at least Excel 2007, which version do you use?

 

[gregtx81]

For cells under the Week 1-244 column headers-- is the data some value (e.g. a count, average, sum, or price) that pertains to that week? You're calling it "a week" but for it to make sense, it is really some value that pertains to that week, right?

This will do what you're asking, although it doesn't identify the week. You need to set the top left cell of your data (see comment in code).

Sub abc()

    Dim r As Range, rout As Range
    
    'set r to the top left cell of your data, excluding the header
    Set r = ThisWorkbook.Sheets(1).Range("A4")
    
    
    Set rout = Workbooks.Add.Sheets(1).Range("A2")
    
    Do While r.Value <> ""
        rout.Resize(244, 1).Value = Application.Transpose(r.Offset(, 10).Resize(1, 244))
        r.Resize(1, 10).Copy Destination:=rout.Offset(, 1).Resize(244)
        Set rout = rout.Offset(244)
        Set r = r.Offset(1)
    Loop

End Sub

 

[tljenkin]

Thanks. The 244 weeks contain numbers eg number of items sold for each week 1 thru 244 weeks.

Does this change the code below in anyway?

Thanks again

For cells under the Week 1-244 column headers-- is the data some value (e.g. a count, average, sum, or price) that pertains to that week? You're calling it "a week" but for it to make sense, it is really some value that pertains to that week, right?

This will do what you're asking, although it doesn't identify the week. You need to set the top left cell of your data (see comment in code).

Sub abc()

    Dim r As Range, rout As Range
    
    'set r to the top left cell of your data, excluding the header
    Set r = ThisWorkbook.Sheets(1).Range("A4")
    
    
    Set rout = Workbooks.Add.Sheets(1).Range("A2")
    
    Do While r.Value <> ""
        rout.Resize(244, 1).Value = Application.Transpose(r.Offset(, 10).Resize(1, 244))
        r.Resize(1, 10).Copy Destination:=rout.Offset(, 1).Resize(244)
        Set rout = rout.Offset(244)
        Set r = r.Offset(1)
    Loop

End Sub

 

[gregtx81]

You tell me. Did you run the code? As is, do you have any way of ascertaining which week, any given row applies to?

 

[tljenkin]

This is not working as intended but thanks so far

Let me explain again

Imagine if you had one record of data and that record had 256 columns or fields but 244 of those fields were called week 1 through to week 244

Now I want to transpose this one record to become 244 records, each new record would now have 13 fields (including one solitary one for week)

So we start with one record that has data across to the right all the way up to week 244

But we finish with a database of 244 records, each record represents a week ie week 1, then next row is week 2 and so on.

Loop this by 300 original records to create a database 73,200 rows big

Do you see where I am going with this?

Thanks

Thanks. The 244 weeks contain numbers eg number of items sold for each week 1 thru 244 weeks.

Does this change the code below in anyway?

Thanks again