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 |
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 |
All times are GMT +1. The time now is 08:45 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com