Pivotting? Transposing? Normal Forming? Crosstabbing?
 

Hi!

If there is a more appropriate forum for this question, please let me know:

I am trying to reformat some data I have from this kind of layout:

Volvo Ford Vauxhall Toyota Honda Jaguar Lotus Citreon
London 3 8 867 6 678 10 2 3
Liverpool 6 7 8 0 67 8 4 23
Leeds 0 74 7 0 4 4 67 4
Manchester 0 45 9 0 4 7 43 34
Birmingham 7 45 6 56 54 43 5 2
Glasgow 9 8 4 67 4 4 2 45
Edinburgh 4 67 5 9 4 4 698 54
Newcastle 567 4 568 4 4 89 4 54

to this kind of layout:

Location Make Qty
London Volvo 3
Liverpool Volvo 6
Leeds Volvo 0
Manchester Volvo 0
Birmingham Volvo 7
Glasgow Volvo 9
Edinburgh Volvo 4
Newcastle Volvo 567
London Ford 8
Liverpool Ford 7
Leeds Ford 74
Manchester Ford 45
Birmingham Ford 45
Glasgow Ford 8
Edinburgh Ford 67
Newcastle Ford 4
London Vauxhall 867
Liverpool Vauxhall 8
Leeds Vauxhall 7
Manchester Vauxhall 9
Birmingham Vauxhall 6
Glasgow Vauxhall 4
etc.....

In this example this is easy enough to do manually, but I was wondering what
the best/right/easiest way of doing this is. For example if I had a much much
larger dataset.

With a bit of effort I think I could write some VB code to solve this
problem, but Im sure some software/ code must already exist to do this.

What is the technical name of what Im trying to do?
Does any software / code already exist to do this?
If manually is the best way of doing this? which way is quickest? (I have
access 2003 /excel/vb and Im fairly familiar with them)

Many Thanks

Pivotting? Transposing? Normal Forming? Crosstabbing?
 

This little macro does the job, it places the new table in a sheet named
Listsheet, create it before run the macro!

Sub test()
norows = ActiveSheet.UsedRange.Rows.Count
nocols = ActiveSheet.UsedRange.Columns.Count
destrow = 0
For r = 2 To norows
For c = 2 To nocols
destrow = destrow + 1
Worksheets("Listsheet").Cells(destrow, 1).Value = Cells(r, 1)
Worksheets("Listsheet").Cells(destrow, 2).Value = Cells(1, c)
Worksheets("Listsheet").Cells(destrow, 3).Value = Cells(r, c)
Next c
Next r
End Sub

Regards,
Stefi

€žRowell657€� ezt Ã*rta:

Hi!

If there is a more appropriate forum for this question, please let me know:

I am trying to reformat some data I have from this kind of layout:

Volvo Ford Vauxhall Toyota Honda Jaguar Lotus Citreon
London 3 8 867 6 678 10 2 3
Liverpool 6 7 8 0 67 8 4 23
Leeds 0 74 7 0 4 4 67 4
Manchester 0 45 9 0 4 7 43 34
Birmingham 7 45 6 56 54 43 5 2
Glasgow 9 8 4 67 4 4 2 45
Edinburgh 4 67 5 9 4 4 698 54
Newcastle 567 4 568 4 4 89 4 54

to this kind of layout:

Location Make Qty
London Volvo 3
Liverpool Volvo 6
Leeds Volvo 0
Manchester Volvo 0
Birmingham Volvo 7
Glasgow Volvo 9
Edinburgh Volvo 4
Newcastle Volvo 567
London Ford 8
Liverpool Ford 7
Leeds Ford 74
Manchester Ford 45
Birmingham Ford 45
Glasgow Ford 8
Edinburgh Ford 67
Newcastle Ford 4
London Vauxhall 867
Liverpool Vauxhall 8
Leeds Vauxhall 7
Manchester Vauxhall 9
Birmingham Vauxhall 6
Glasgow Vauxhall 4
etc.....

In this example this is easy enough to do manually, but I was wondering what
the best/right/easiest way of doing this is. For example if I had a much much
larger dataset.

With a bit of effort I think I could write some VB code to solve this
problem, but Im sure some software/ code must already exist to do this.

What is the technical name of what Im trying to do?
Does any software / code already exist to do this?
If manually is the best way of doing this? which way is quickest? (I have
access 2003 /excel/vb and Im fairly familiar with them)

Many Thanks

Pivotting? Transposing? Normal Forming? Crosstabbing?
 

Thats fantastic! Exactly what I wanted. It would've taken me ages to write
that code and it would have been much less efficient/versatile!

Many Thanks for your time!

Pivotting? Transposing? Normal Forming? Crosstabbing?
 

You are welcome! Thanks for the feedback!
Stefi

€žRowell657€� ezt Ã*rta:

Thats fantastic! Exactly what I wanted. It would've taken me ages to write
that code and it would have been much less efficient/versatile!

Many Thanks for your time!