Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Transposing | Excel Discussion (Misc queries) | |||
Forming a trend line | Excel Worksheet Functions | |||
Forming a binary column from a more complicated column of diagnostic codes | Excel Discussion (Misc queries) | |||
conditional forming causing linking errors | Excel Worksheet Functions | |||
forming a new column by using the other column | Excel Worksheet Functions |