Formula help...to insert rows

[Nhunter]

I am hoping to be able to insert a number of rows via formula. I am having issues coming up with anything on my own. I am hoping to duplicate(and insert) a specific row (x) number of times based on a number returned from a previous formula.

Any help would be greatly appriciated.

Best regards,
Nhunter

 

[VoG]

Hello and welcome to MrExcel.

Sorry, that isn't possible using formulas.

It may be possible using VBA. Can you be more specific about which cell contains the formula and where the rows are to be inserted.

 

[Nhunter]

Certainly...

I have a spreadsheet that contains specfic products. The sheet has numerous instances where there are multiple ITEM NAMES (these are model number) contained within a single STORE DESCRIPTION (here the model numbers are seperated by colons). I have need to create a new row for each model number based on the data from the original minus the other model numbers.

 

[VoG]

Please use Excel Jeanie to post small samples of 'before' and 'after'.

http://www.excel-jeanie-html.de/html/hlp_schnell_en.php

 

[Nhunter]

Excel Workbook
	B	C	D	F
1	Item Name/Number		Name	Store Description
81		1	PROFORM 785 TREADMILL:224021	PFTL19040 : PFTL19041
82		4	PROFORM 785 XT TREADMILL:186689	PFTL20860 : PFTL20861 : PFTL20862 : PFTL20863 : PFTL20864
83		1	PROFORM CROSSWALK CSI TREADMILL:186689	PFTL21460 : PFTL21462
84		2	PROFORM 345 TREADMILL:224019	PFTL311040 : PFTL311041 : PFTL311042
85		1	PROFORM 330i TREADMILL:224019	PFTL313040 : PFTL313041
86		2	PROFORM 495 Pi TREADMILL:227178	PFTL331050 : PFTL331051 : PFTL331052
87		3	PROFORM 595 Pi TREADMILL:227178	PFTL431050 : PFTL431051 : PFTL431052 : PFTL431053
CustomItemSearchResults212(1)

 

[Nhunter]

SO looking at the above example I have determined that for row 81 I need a duplicate row created. Additionally I would need to cocantenate Cell F, the information after the colon (first and only in this case), with cell D in the new row is possible.

 

[VoG]

I think that this will do partly what you want (not the concatenate bit).

Try this with a copy of your sheet. Press ALT + F11 to open the Visual Basic Editor, Insert Module and paste the following into the white space on the right:

Sub InsRows()
Dim LR As Long, i As Long
LR = Range("C" & Rows.Count).End(xlUp).Row
For i = LR To 2 Step -1
    With Range("C" & i)
        .Offset(1).Resize(.Value).EntireRow.Insert
        .EntireRow.Copy Destination:=.Offset(1, -2).Resize(.Value)
    End With
Next i
End Sub

Press ALT + F11 to return to your sheet, Tools > Macro > Macros, highlight InsRows and click the Run button.

 

[VoG]

Added the concatenation

Sub InsRows()
Dim LR As Long, i As Long, j As Integer, X
LR = Range("C" & Rows.Count).End(xlUp).Row
For i = LR To 2 Step -1
    With Range("C" & i)
        .Offset(1).Resize(.Value).EntireRow.Insert
        .EntireRow.Copy Destination:=.Offset(1, -2).Resize(.Value)
        X = Split(.Offset(, 3), ":")
        .Offset(, 1).Value = .Offset(, 1).Value & " " & X(0)
        For j = 1 To UBound(X)
            .Offset(j, 1).Value = .Offset(j, 1).Value & X(j)
        Next j
    End With
Next i
End Sub

 

[Nhunter]

That indeed inserted the copied rows. Thanks for helping with that. My issue now is eliminating one item and the subsequent colon from column F in each new row.

Any thoughts?

Best regards,
Norman

 

[VoG]

Here's a shot of the sheet after inserting rows. Can you show us what you would like column F to look like.

<b>Sheet5 (2)</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:367px;" /><col style="width:43px;" /><col style="width:372px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td >Item Name/Number</td><td > </td><td >Name</td><td > </td><td >Store Description</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td > </td><td > </td><td style="text-align:right; ">1</td><td >PROFORM 785 TREADMILL:224021 PFTL19040 </td><td > </td><td >PFTL19040 : PFTL19041</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td > </td><td style="text-align:right; ">1</td><td >PROFORM 785 TREADMILL:224021 PFTL19041</td><td > </td><td >PFTL19040 : PFTL19041</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td > </td><td style="text-align:right; ">4</td><td >PROFORM 785 XT TREADMILL:186689 PFTL20860 </td><td > </td><td >PFTL20860 : PFTL20861 : PFTL20862 : PFTL20863 : PFTL20864</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td > </td><td style="text-align:right; ">4</td><td >PROFORM 785 XT TREADMILL:186689 PFTL20861 </td><td > </td><td >PFTL20860 : PFTL20861 : PFTL20862 : PFTL20863 : PFTL20864</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td > </td><td style="text-align:right; ">4</td><td >PROFORM 785 XT TREADMILL:186689 PFTL20862 </td><td > </td><td >PFTL20860 : PFTL20861 : PFTL20862 : PFTL20863 : PFTL20864</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td > </td><td style="text-align:right; ">4</td><td >PROFORM 785 XT TREADMILL:186689 PFTL20863 </td><td > </td><td >PFTL20860 : PFTL20861 : PFTL20862 : PFTL20863 : PFTL20864</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td > </td><td > </td><td style="text-align:right; ">4</td><td >PROFORM 785 XT TREADMILL:186689 PFTL20864</td><td > </td><td >PFTL20860 : PFTL20861 : PFTL20862 : PFTL20863 : PFTL20864</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td > </td><td > </td><td style="text-align:right; ">1</td><td >PROFORM CROSSWALK CSI TREADMILL:186689 PFTL21460 </td><td > </td><td >PFTL21460 : PFTL21462</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td > </td><td > </td><td style="text-align:right; ">1</td><td >PROFORM CROSSWALK CSI TREADMILL:186689 PFTL21462</td><td > </td><td >PFTL21460 : PFTL21462</td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4