Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to create a list from a table
I have a 2 x 2 table:
C D A 1 2 B 3 4 I would like to tranform it into a list as follows: Row Column Value A C 1 A D 2 B C 3 B D 4 How do I do this in Excel. Of course my tables are much bigger than this and they are not Pivot Tables. thanks Joaquim |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to create a list from a table
Select your entire table and run this code...
Sub test() Dim rng As Range Dim rngToSearch As Range Dim rngSelection As Range Dim wksNew As Worksheet Dim rngPaste As Range Set rngSelection = Selection With rngSelection Set rngToSearch = Range(.Cells(2, 2), .Cells(.Cells.Count)) End With Set wksNew = Worksheets.Add Set rngPaste = wksNew.Range("A1") For Each rng In rngToSearch rngPaste.Value = Intersect(rng.EntireRow, _ rngSelection.Columns(1).EntireColumn) rngPaste.Offset(0, 1).Value = Intersect(rng.EntireColumn, _ rngSelection.Rows(1).EntireRow) rngPaste.Offset(0, 2).Value = rng.Value Set rngPaste = rngPaste.Offset(1, 0) Next rng End Sub -- HTH... Jim Thomlinson "Joaquim" wrote: I have a 2 x 2 table: C D A 1 2 B 3 4 I would like to tranform it into a list as follows: Row Column Value A C 1 A D 2 B C 3 B D 4 How do I do this in Excel. Of course my tables are much bigger than this and they are not Pivot Tables. thanks Joaquim |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create List from a Table | Excel Discussion (Misc queries) | |||
Create list based on data table | Excel Discussion (Misc queries) | |||
How-to create a list from a summary table? (Office 2007) | Excel Worksheet Functions | |||
How to create list and count from a table? | New Users to Excel | |||
How to create a data list from a pivot table | Excel Discussion (Misc queries) |