Extracting values from a table

[Guest]

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]

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

 

[Guest]

Dave

Many thanks that worked a treat