Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

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.

Answer
Discuss

Answers

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.

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login