Excel: Structure Data for Charting

[piepmeier]

I have a column in a spreadsheet that has value(s) in it that are comma delineated. Each PL in Column B relates to an (ECO)in Column A. I want to plot the number of ECO's for each PL but I can't figure out how to structure the data to do so. For example I have:

Column A Column B
ECO#1 PL1, PL5, PL6
ECO#2 PL2, PL6
ECO#3 PL7

I have been able to parse the column with the PL's into separate columns but I think I need to get to:

Column A Column B
ECO#1 PL1
ECO#1 PL5
ECO#1 PL6
ECO#2 PL2
ECO#2 PL6
ECO#3 PL7

Any thoughts on how to do this?

 

[JVFriederick]

How many PL's will you have?
If only 7 or so, try

Point...PL1.....PL2.....PL5.....PL6.....PL7
1.......1...............1.......1..........
2...............12..............12.........
3.......................................33.

(Sorry, my line-up techinique needs major improvement!)

I assume ECO# are values ?

 

[SkipVought]

Hi, piepmeier,

Here's a way...

1. In Col B parse the coma delimited data -- Select Column, Menu Item - Data/Text to Columns Select Delimiter OptionButton, Next and then Coma CheckBox and Finish
2. use this procedure to put your data into 2 columns...

Sub DataToColumns()
    Dim rng As Range, rngCols As Range, lNextRow As Long, Row As Range, Col As Range
    Set rng = Range(Cells(1, 1), Cells(1, 1).End(xlDown))
    For Each Row In rng
        With Row
            Set rngCols = Range(Cells(.Row, 1), Cells(.Row, 1).End(xlToRight))
            lNextRow = rng.CurrentRegion.Rows.Count + 1
            For Each Col In rngCols
                If Col.Column > 2 Then
                    Cells(lNextRow, 1).Value = Row.Value
                    Cells(lNextRow, 2).Value = Col.Value
                    Col.Value = ""
                    lNextRow = lNextRow + 1
                End If
            Next
        End With
    Next
End Sub

Hope this helps ;-) Skip,
metzgsk@voughtaircraft.com

 

[AlaskanDad]

Your question was:

I want to plot the number of ECO's for each PL...

Here is a solution not too unlike JVFriederick's but with some formulas.

1. Name the ranges of your ECO#, "ECOs" and your PLs, "PLs".
2. Starting in cell B2, type PL1 and drag to the right for as many PLs as you have (they should be increasing in value).
3. Starting in cell A2, type in ECO#1 and drag down in the same manner.

4. In cell B2 use the following formula:

=MATCH("*"&B$1&"*",INDEX(PLs,MATCH($A2,ECOs,0)),0)

5. Drag this formula down to match all of your ECO#s and to the right to match all of your PLs.
6. Use the following formula at the bottom of each of your PLs to summarize the number of ECO#s per PL:

=SUMIF(B2:B4,1)

You should end up with a matrix like this:

PL1 PL2 PL3 PL4 PL5 PL6 PL7
ECO#1 1 #N/A #N/A #N/A 1 1 #N/A
ECO#2 #N/A 1 #N/A #N/A #N/A 1 #N/A
ECO#3 #N/A #N/A #N/A #N/A #N/A #N/A 1
1 1 0 0 1 2 1

Your bottom row shows how many ECO#s you have per PL.

Plot away!