# Excel Data Issue

0

I have a spreadsheet that contains size information for the sale of a product over a long period of time.  The data is listed in three columns, Quantity, Width, & Height.  I sm trying to create seperate Histograms for the width and height.  For example, here is a snap shot of the data:

Qty Width Height 4 71.625 71.000

I am use to creating Histograms that use individual pieces of data, for example, following is the example above listed as individual pieces of data:

Qty Width Height 1 71.625 71.000 1 71.625 71.000 1 71.625 71.000 1 71.625 71.000

My question, how in the world do I convert the first example to the second example?  I have over 5,000 lines of data, so manually inserting rows is not feasible.

0

Provided I did understand your problem correctly, there shouldn't be an easier solution to it than VBA. The attached workbook contains the code below in module TeachExcel. Make the appropriate amendments to the code as indicated in the remarks.

``````Sub SplitEntries()

Const QtyClm As String = "A"        ' change as appropriate
Const FirstDataRow As Long = 2      ' change as appropriate
' row 1 might contain captions

Dim Fun() As Variant
Dim Rng As Range
Dim Arr As Variant
Dim R As Long, C As Long
Dim q As Long, i As Long

' Qty is presumed in column 'QtyClm'
' Width & Height in the two columns adjcent to that.
' No blank rows in the data block.
With Worksheets("Sheet1")           ' change as appropriate
Set Rng = .Range(.Cells(FirstDataRow, QtyClm), _
.Cells(.Rows.Count, QtyClm).End(xlUp))
ReDim Fun(1 To WorksheetFunction.Sum(Rng), 1 To 3)
Arr = Rng.Resize(, 3).Value

For R = 1 To UBound(Arr)
For q = 1 To Arr(R, 1)
i = i + 1
Fun(i, 1) = 1
For C = 2 To 3
Fun(i, C) = Arr(R, C)
Next C
Next q
Next R
Set Rng = .Cells(FirstDataRow, QtyClm).Resize(UBound(Fun), 3)
Rng.Value = Fun
End With
End Sub``````

Place the cursor within the procedure SplitEntries and press F5, or run the same sub in any other way supported by Excel. Note that the original data will be irretrievably over-written. The attached workbook therefore has a copy of the original data in its Sheet2.