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>

 

[Gary McMaster]

Is the data you have shown in red and blue ("before example") contained in individual cells at the top of each column?

 

[Gary McMaster]

Like this?

Sheet1

	A	B	C	D	E	F	G	H
1	Category	cat. 1	cat. 1	cat. 2	cat. 2	cat. 3	cat. 3	cat. 4
2	Number	#1	#2	#1	#2	#1	#2	#1
3	Name
4	Al		x				x
5	Ben	x			x
6	Fred							x

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[howiechiang]

Yes they are in separate lines. I'm still pretty new to this. I appreciate the help.

 

[Gary McMaster]

A few more questions and I'm sure someone will offer a solution.

What are the maximum number of rows and columns, and do they vary?

Should the output overwrite the existing data, place the new data in an empty area of the same worksheet or write the new data to a new sheet?

Is the first cell that might contain the X always "B4"

Gary

 

[howiechiang]

If the X is removed, the output should be removed as well. If a new X is put in, it should output the new data in an empty area on a new sheet.

 

[Gary McMaster]

Here's a quick and inflexible sample that will work with the data exactly as I showed it in my post at 01:18pm

There must be an existing sheet named "Sheet2" to try this sample.

Hope it helps.

Gary

In a standard module:

Public Sub Test()

Dim oTarget As Range
Dim oXed As Range
Dim oCell As Range
Dim oDest As Range

Set oDest = ThisWorkbook.Worksheets("Sheet2").Range("A1")
Set oTarget = ActiveSheet.Range("B3:H6")

'Select only the cells in B3:H6 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(1, oCell.Column).Value
    oDest.Offset(0, 2).Value = Cells(2, oCell.Column).Value
    Set oDest = oDest.Offset(1, 0)
Next oCell

End Sub

 

[Gary McMaster]

If a new X is put in, it should output the new data in an empty area on a new sheet.

Unless there are thousands of rows / columns, I would be inclined to just completely clear the output sheet and regenerate the entire output data set every time any X was added or removed from the grid. You could call your procedure from the "worksheet change" event.

Gary

 

[howiechiang]

what if the categories were merged? Since excel doesn't assign a value for all the merged cells, but only the top-left one. What would you do then?

 

[Gary McMaster]

what if the categories were merged? Since excel doesn't assign a value for all the merged cells, but only the top-left one. What would you do then?

Merged areas can be a pain in the butt with VBA so I would probably un-merge the cells and use the previous code sample as is. The merged area could also be left alone. You could "split" the data in the first cell into the array and use it from within the array but I prefer to get rid of the merged area so the sample below does it that way.

I assume you mean cells B1 through H1 are merged and "B1" (or the first cell in the merged area) contains the single string "cat. 1 cat. 1 cat. 2 cat. 2 cat. 3 cat. 3 cat. 4" (Please see my "Excel Jeanie" repost of your data on 1/30/14 at 1:18pm). Splitting the merged string apart will be a little more difficult than it should be because the blank spaces are located on both sides of the numbers.

Hope it helps.

Gary

In a standard module:

Test conditions for below sample:
Cells B1 thru H1 merged and contains the single text string: "cat. 1 cat. 1 cat. 2 cat. 2 cat. 3 cat. 3 cat. 4"

Public Sub Unmerge()

Dim oCurrCell As Range
Dim oMergeArea As Range
Dim vSplit As Variant
Dim lCounter As Long

Set oMergeArea = ActiveSheet.Range("B1")

'Some properties of the merged area
Debug.Print oMergeArea.MergeArea.Address
Debug.Print oMergeArea.MergeArea.Cells.Count
Debug.Print oMergeArea.MergeArea.Cells(1).Value

Set oCurrCell = oMergeArea.MergeArea.Cells(1)

'Preserve the contents of the first cell of the merged area
vSplit = oMergeArea.MergeArea.Cells(1).Value

oMergeArea.MergeArea.Cells.ClearContents

oMergeArea.Unmerge

'Burst the string using " c" (<space> c)as the delimiter because there are also spaces preceding the numbers
vSplit = Split(vSplit, " c")

'Show the contents of the array. Note that the leading "c" is missing from all
'elements of the array except the first because it was used as part of the delimiter
For lCounter = LBound(vSplit) To UBound(vSplit)
    Debug.Print vSplit(lCounter)
Next lCounter

'Replace the missing "c" starting at the second array element
For lCounter = LBound(vSplit) + 1 To UBound(vSplit)
    vSplit(lCounter) = "c" & vSplit(lCounter)
Next lCounter
'Could leave cells merged and use array data (immediately above) to transpose "X" grid

'Dump the contents of the array again. Note that the missing leading "c" has been replaced
For lCounter = LBound(vSplit) To UBound(vSplit)
    Debug.Print vSplit(lCounter)
Next lCounter

'Place the contents of the array back into the unmerged cells
Debug.Print oCurrCell.Address
For lCounter = LBound(vSplit) To UBound(vSplit)
    oCurrCell.Value = vSplit(lCounter)
    Set oCurrCell = oCurrCell.Offset(0, 1)
Next lCounter

'Run transpose "X" code as though the cells were never merged

End Sub

</space>