Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extracting values from a table
I have a table which shows a tick in the cells where a combination of both
the column and row headings is valid. I want to extract from this table one record for each valid combination of by concatenating the column heading and row heading e.g. Table looks like this: A B C 1 x x 2 x 3 x x Result required: A1 B2 B3 C1 C3 Can anyone out there help as I am completely stuck as to how to go about this? Many thanks in anticipation |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extracting values from a table
One way is to use a macro:
Option Explicit Sub testme() Dim CurWks As Worksheet Dim NewWks As Worksheet Dim LastRow As Long Dim LastCol As Long Dim iCol As Long Dim iRow As Long Dim oRow As Long Set CurWks = Worksheets("sheet1") Set NewWks = Worksheets.Add With CurWks LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row oRow = 0 For iCol = 2 To LastCol For iRow = 2 To LastRow If IsEmpty(.Cells(iRow, iCol).Value) Then 'do nothing Else oRow = oRow + 1 NewWks.Cells(oRow, "A").Value _ = .Cells(1, iCol).Value & .Cells(iRow, 1).Value End If Next iRow Next iCol End With End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Rob Cherry wrote: I have a table which shows a tick in the cells where a combination of both the column and row headings is valid. I want to extract from this table one record for each valid combination of by concatenating the column heading and row heading e.g. Table looks like this: A B C 1 x x 2 x 3 x x Result required: A1 B2 B3 C1 C3 Can anyone out there help as I am completely stuck as to how to go about this? Many thanks in anticipation -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extracting values from a table
Dave
Many thanks that worked a treat -- Rob Cherry "Dave Peterson" wrote: One way is to use a macro: Option Explicit Sub testme() Dim CurWks As Worksheet Dim NewWks As Worksheet Dim LastRow As Long Dim LastCol As Long Dim iCol As Long Dim iRow As Long Dim oRow As Long Set CurWks = Worksheets("sheet1") Set NewWks = Worksheets.Add With CurWks LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row oRow = 0 For iCol = 2 To LastCol For iRow = 2 To LastRow If IsEmpty(.Cells(iRow, iCol).Value) Then 'do nothing Else oRow = oRow + 1 NewWks.Cells(oRow, "A").Value _ = .Cells(1, iCol).Value & .Cells(iRow, 1).Value End If Next iRow Next iCol End With End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Rob Cherry wrote: I have a table which shows a tick in the cells where a combination of both the column and row headings is valid. I want to extract from this table one record for each valid combination of by concatenating the column heading and row heading e.g. Table looks like this: A B C 1 x x 2 x 3 x x Result required: A1 B2 B3 C1 C3 Can anyone out there help as I am completely stuck as to how to go about this? Many thanks in anticipation -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot table page filter not accepting multiple values. Workaround? | Excel Worksheet Functions | |||
Why won't pasted values from a formula appear in a pivot table | Excel Discussion (Misc queries) | |||
pivot table with selection values not included in the base data | Charts and Charting in Excel | |||
Pivot Table - Extracting specific data | Excel Worksheet Functions | |||
Pivot Table - Extracting specific data | Excel Worksheet Functions |