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

howiechiang

New Member
Joined
Jan 27, 2014
Messages
12
|Category||cat. 1cat. 1|| cat. 2 cat. 2||cat. 3cat. 3||cat. 4|
|Number|| #1 || #2 || #1 || #2 || #1 || #2 || #1 |
|Name|
|Al|xx
|Ben|xx
|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:
NameCategoryNumber
Alcat. 1#2
Alcat. 3#2
Bencat. 1#1
Bencat. 2#2
Fredcat. 4#1

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Gary McMaster

Well-known Member
Joined
Feb 8, 2009
Messages
1,977
Is the data you have shown in red and blue ("before example") contained in individual cells at the top of each column?
 

Gary McMaster

Well-known Member
Joined
Feb 8, 2009
Messages
1,977
Like this?

Sheet1

ABCDEFGH
1Categorycat. 1cat. 1 cat. 2cat. 2cat. 3cat. 3cat. 4
2Number #1 #2 #1 #2 #1 #2 #1
3Name
4Alxx
5Benxx
6Fredx

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
 

Gary McMaster

Well-known Member
Joined
Feb 8, 2009
Messages
1,977

ADVERTISEMENT

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

New Member
Joined
Jan 27, 2014
Messages
12
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

Well-known Member
Joined
Feb 8, 2009
Messages
1,977

ADVERTISEMENT

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()

Code:
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

Well-known Member
Joined
Feb 8, 2009
Messages
1,977
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

New Member
Joined
Jan 27, 2014
Messages
12
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

Well-known Member
Joined
Feb 8, 2009
Messages
1,977
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"

Code:
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>
 

Watch MrExcel Video

Forum statistics

Threads
1,122,518
Messages
5,596,621
Members
414,081
Latest member
Subaru_Steve

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top