Generating a line for each 'x' that appears in that row.

[howiechiang]

|Category| |cat. 1 cat. 1| | cat. 2 cat. 2| |cat. 3 cat. 3| |cat. 4| |Number| | #1 | | #2 | | #1 | | #2 | | #1 | | #2 | | #1 | |Name| |Al| x x |Ben| x x |Fred| x <tbody> </tbody>

<tbody>
</tbody>

I need a chart that will generate a line for each 'x' in a box in that row. That new chart will call out what 'category' and 'number' that 'x' is in.

The solution should come out to this:

Name	Category	Number
Al	cat. 1	#2
Al	cat. 3	#2
Ben	cat. 1	#1
Ben	cat. 2	#2
Fred	cat. 4	#1

<tbody>
</tbody>

 

[howiechiang]

Dude that's so much. Thank you.

 

[howiechiang]

There is also a Task Function Column in my 'oTarget' spread sheet. There is only one task function per name, but the output may create more than one line per name due to multiple 'x' marks. Is there a way to create a Task Function column in my output, the multiple rows of the same name are merged.
This is what I have at the moment.

Public Sub RetrieveData()

'Clear current data on List Sheet'
Worksheets("List Sheet1").Activate
Range("A2:F500").Select
Selection.SpecialCells(xlCellTypeConstants, 23).Select
Selection.ClearContents


'Set Ranges'
Dim oTarget As Range
Dim oXed As Range
Dim oCell As Range
Dim oDest As Range


'Set up target & destination'
Worksheets("Chart Sheet").Activate
Set oDest = ThisWorkbook.Worksheets("List Sheet1").Range("A1")
Set oTarget = ActiveSheet.Range("B5:AR999")


'Select only the cells in B5:AR999 that contain an X'
Set oXed = oTarget.SpecialCells(xlCellTypeConstants)
oDest.Value = "Name"
oDest.Offset(0, 1).Value = "Category"
oDest.Offset(0, 2).Value = "Number"
Set oDest = oDest.Offset(1, 0)


'Transpose the data starting in A2 of Sheet2'
For Each oCell In oXed
oDest.Value = Cells(oCell.Row, 1).Value
oDest.Offset(0, 1).Value = Cells(2, oCell.Column).Value
oDest.Offset(0, 2).Value = Cells(3, oCell.Column).Value
Set oDest = oDest.Offset(1, 0)
Next oCell


'Go to List Sheet'
Worksheets("List Sheet1").Activate


End Sub

 

[howiechiang]

Don't worry about that post i made at 2:03pm. I was able to figure it out myself after a little bit of grunt work.
This is actually my first experience working with VBA (and second time working with any sort of programming language), and I'm catching on.
Thank you for your time and effort Gary.