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

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

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
>