0

I have a spreadsheet that lists medical equipment (identified by JSN) within a room. A single piece of equipment (JSN) may have multiple quantities within a room. I'd like to separate all rows that have a quantity greater than 1 into multiple rows with the same data while simultaneously changing the quantity to 1 EA. Here is an example of what the existing spreadsheet looks like (minus other columns):

Nomenclature           JSN          Wayfinding Rm #        QTY         Installed
Shelving, Solid       M2090             40-179              3           5/5/15
Waste Can, Swing      F2010             11-087              2           9/9/15
Stand, Mayo, Screw    M8810             11-078              1           8/1/15

Here is what I would need it to look like:

Nomenclature          JSN          Wayfinding Rm #         QTY       Installed
Shelving, Solid       M2090             40-179              1           5/5/15
Shelving, Solid       M2090             40-179              1           5/5/15
Shelving, Solid       M2090             40-179              1           5/5/15
Waste Can, Swing      F2010             11-087              1           9/9/15
Waste Can, Swing      F2010             11-087              1           9/9/15
Stand, Mayo, Screw    M8810             11-078              1           8/1/15

Any help would be greatly appreciate. Please make note that I just found out about VBAs and Macros TODAY! Trying to learn. Thank you so much to anyone that can assist this struggling but eager novice!

1 Answer 1

1

Something like this should work, assuming this data starts in cell A1

Actually - here, they will stay in order

Sub test()
Dim lastrow As Integer
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
Dim howmany As Integer
For i = lastrow To 1 Step -1
    If Cells(i, 4) > 1 Then
       howmany = Cells(i, 4)
       For j = 1 To howmany - 1
       Rows(i + 1).Insert (xlShiftDown)
       Cells(i, 4) = 1
       Cells(i + 1, 1) = Cells(i, 1)
       Cells(i + 1, 2) = Cells(i, 2)
       Cells(i + 1, 3) = Cells(i, 3)
       Cells(i + 1, 4) = Cells(i, 4)
       Cells(i + 1, 5) = Cells(i, 5)
       Next
    End If
Next

End Sub

This one puts them at the end:

Sub test()
Dim lastrow As Integer
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
Dim nextrow As Integer
nextrow = lastrow + 1
Dim howmany As Integer
For i = 1 To lastrow
    If Cells(i, 4) > 1 Then
       howmany = Cells(i, 4)
       For j = 1 To howmany - 1
       Cells(i, 4) = 1
       Cells(nextrow, 1) = Cells(i, 1)
       Cells(nextrow, 2) = Cells(i, 2)
       Cells(nextrow, 3) = Cells(i, 3)
       Cells(nextrow, 4) = Cells(i, 4)
       Cells(nextrow, 5) = Cells(i, 5)
       nextrow = nextrow + 1
       Next
    End If
Next

End Sub

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .