Duplicate rows into new rows based on row value

[Tom]

Hi,
I am trying to breakout multiple rows into individual rows based on a value
in the row.... sounds confusing. Here's an example
a b c etc....
1 2 ABC 5.5
2 3 DEF 2.3

I need to break this out into individual rows like this...

a b c
1 2 ABC 5.5
2 2 ABC 5.5
3 3 DEF 2.3
4 3 DEF 2.3
5 3 DEF 2.3

Is this possible using a function or macro? Please let me know.

Thanks in advance!

 

[Wehrmacher]

I am not sure I understand the question. It looks like the example is a
sorting of a long list of rows that contain similar identical data in some
fields. If this is what you are looking for, you can look in the Data menu,
select Sort and chose the column priorities and orders you wish.

You may also want to take a look at Pivot Tables. These things organize your
data according to the content of row and column information. I doubt this is
what you are talking about as pivot tables are used to compress data and your
question seems to talk about expanding it.

Hope this helps.

 

[Tom]

That's right I am looking to expand the data. Referencing the above example
the spreadsheet has say two rows. I need a solution that takes these two
rows and breaks them into 5 rows (according to the value in column a). So
the first row (2 ABC) will eventually be two individual rows because of the
value in column a and the second row will be changed into 3 rows. I almost
think of it as a reverse subtotal. I hope this clarifies things.
Thanks in advance!

 

[ryguy7272]

Assuming:
Col A has numbers (such as 2 and 3) and Col B has data beginning with the
letters (such as ABC...)
A B
2 ABC 5.5
3 DEF 2.3

I think this will pretty much do what you want; you may have to modify it a
tad:
Sub InsertAnyRows()

Dim insertNumber As Range
Dim insertStart As Range
Dim redRng As Range
Dim i As Integer

Set insertNumber = Application.InputBox _
(Prompt:="Select a point to begin inserting rows. For instance, choose first
non blank cell in Column A", Title:="Add a row", Type:=8)
insertNumber.Select
If insertNumber <= 0 Then
MsgBox ("Invalid Number Entered")
Exit Sub
End If
Dim myRow As Long

lastcell = Cells(Rows.Count, "A").End(xlUp).Row
myRow = 1
Do Until myRow = lastcell
For i = 1 To Cells(myRow, 1)

If Cells(myRow, 1) <> "" Then
Cells(myRow + 1, 1).Select
Selection.EntireRow.Insert Shift:=xlDown
End If

Next
lastcell = Cells(Rows.Count, "A").End(xlUp).Row
myRow = myRow + 1
Loop


Range("a2").Select
Range(Selection, Selection.End(xlDown)).Select
Range("a2:B100").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C"



End Sub


Regards,
Ryan--

 

[Tom]

Hi Ryan,
However I'm still having some difficulty. The are being broken out and
increasing by the number in column a however it doesn't consider the original
column in the macro so is a row is supposed to be broken out for a total of
two rows I get a total of three. Can this be adjusted? Please advise.
Thanks and best regards!

Assuming:
Col A has numbers (such as 2 and 3) and Col B has data beginning with the
letters (such as ABC...)
A B
2 ABC 5.5
3 DEF 2.3

I think this will pretty much do what you want; you may have to modify it a
tad:
Sub InsertAnyRows()

Dim insertNumber As Range
Dim insertStart As Range
Dim redRng As Range
Dim i As Integer

Set insertNumber = Application.InputBox _
(Prompt:="Select a point to begin inserting rows. For instance, choose first
non blank cell in Column A", Title:="Add a row", Type:=8)
insertNumber.Select
If insertNumber <= 0 Then
MsgBox ("Invalid Number Entered")
Exit Sub
End If
Dim myRow As Long

lastcell = Cells(Rows.Count, "A").End(xlUp).Row
myRow = 1
Do Until myRow = lastcell
For i = 1 To Cells(myRow, 1)

If Cells(myRow, 1) <> "" Then
Cells(myRow + 1, 1).Select
Selection.EntireRow.Insert Shift:=xlDown
End If

Next
lastcell = Cells(Rows.Count, "A").End(xlUp).Row
myRow = myRow + 1
Loop


Range("a2").Select
Range(Selection, Selection.End(xlDown)).Select
Range("a2:B100").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C"



End Sub


Regards,
Ryan--


--
RyGuy

I am not sure I understand the question. It looks like the example is a
sorting of a long list of rows that contain similar identical data in some
fields. If this is what you are looking for, you can look in the Data menu,
select Sort and chose the column priorities and orders you wish.

You may also want to take a look at Pivot Tables. These things organize your
data according to the content of row and column information. I doubt this is
what you are talking about as pivot tables are used to compress data and your
question seems to talk about expanding it.

Hope this helps.

 

[ryguy7272]

If you send me the workbook I'll try to do it for you:
(e-mail address removed)
take out the REMOVE part...



--
RyGuy

Hi Ryan,
However I'm still having some difficulty. The are being broken out and
increasing by the number in column a however it doesn't consider the original
column in the macro so is a row is supposed to be broken out for a total of
two rows I get a total of three. Can this be adjusted? Please advise.
Thanks and best regards!

Assuming:
Col A has numbers (such as 2 and 3) and Col B has data beginning with the
letters (such as ABC...)
A B
2 ABC 5.5
3 DEF 2.3

I think this will pretty much do what you want; you may have to modify it a
tad:
Sub InsertAnyRows()

Dim insertNumber As Range
Dim insertStart As Range
Dim redRng As Range
Dim i As Integer

Set insertNumber = Application.InputBox _
(Prompt:="Select a point to begin inserting rows. For instance, choose first
non blank cell in Column A", Title:="Add a row", Type:=8)
insertNumber.Select
If insertNumber <= 0 Then
MsgBox ("Invalid Number Entered")
Exit Sub
End If
Dim myRow As Long

lastcell = Cells(Rows.Count, "A").End(xlUp).Row
myRow = 1
Do Until myRow = lastcell
For i = 1 To Cells(myRow, 1)

If Cells(myRow, 1) <> "" Then
Cells(myRow + 1, 1).Select
Selection.EntireRow.Insert Shift:=xlDown
End If

Next
lastcell = Cells(Rows.Count, "A").End(xlUp).Row
myRow = myRow + 1
Loop


Range("a2").Select
Range(Selection, Selection.End(xlDown)).Select
Range("a2:B100").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C"



End Sub


Regards,
Ryan--


--
RyGuy

I am not sure I understand the question. It looks like the example is a
sorting of a long list of rows that contain similar identical data in some
fields. If this is what you are looking for, you can look in the Data menu,
select Sort and chose the column priorities and orders you wish.

You may also want to take a look at Pivot Tables. These things organize your
data according to the content of row and column information. I doubt this is
what you are talking about as pivot tables are used to compress data and your
question seems to talk about expanding it.

Hope this helps.
--
Bill Wehrmacher


:

Hi,
I am trying to breakout multiple rows into individual rows based on a value
in the row.... sounds confusing. Here's an example
a b c etc....
1 2 ABC 5.5
2 3 DEF 2.3

I need to break this out into individual rows like this...

a b c
1 2 ABC 5.5
2 2 ABC 5.5
3 3 DEF 2.3
4 3 DEF 2.3
5 3 DEF 2.3

Is this possible using a function or macro? Please let me know.

Thanks in advance!