finding a value >1 in a column, and insert row above or below and copy content and change the value to 1

[sekar_r24]

Hi.,

01. I am having range of data, would like to find the value greater than in the column P (say 3) of the particular cell, and insert the row above or below (number (say 3) times - 1) (=3-1), and copy & paste the entire row, in the inserted rows and the change the value of the P column for the pasted values as 1. Also change the value of three into one.

02. Also the values in the column Q has to be separated, with the delimiter comma (, ) and the same has to be pasted in the Q column of the inserted rows, with each unique value.

Attached here with the sample data and expected output.

Could anyone help in this regard.

sample01.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
4	LEFT_SIDE	600 mm	295 mm	18 mm	14142- Auburn Kaos Oak	White laminate	448P	414P	414P	414P	650 mm	345 mm	SG	HG	MINIFIX SET	1	LEFT SIDE_WCD2D_800.0
5	LEFT_SIDE	600 mm	295 mm	18 mm	White laminate	White laminate	448 P	414P	414P	414P	650 mm	345 mm	SG	HG	0	1	LEFT SIDE_WCD2D_800.0
6	LEFT_SIDE	605 mm	550 mm	18 mm	White laminate	White laminate	448P	414P	414P	414P	655 mm	600 mm	SG	HG	0	3	LEFT SIDE PANEL_BC1D_200.0, LEFT SIDE_BC3DRW_600.0, LEFT SIDE_BCSCLH_960
7	LEFT_SIDE	605 mm	600 mm	18 mm	White laminate	White laminate	448P	414P	414P	414P	655 mm	650 mm	SG	HG	0	1	LEFT_SIDE_BC2DS_700.0
8	LEFT_SIDE	705 mm	570 mm	18 mm	White laminate	White laminate	448P	414P	414P	414P	755 mm	620 mm	SG	HG	0	1	LEFT SIDE_BC1DCY_600.0
9	Ledge_01	200 mm	1072 mm	18 mm	14142- Auburn Kaos Oak	14142- Auburn Kaos Oak	448P	448P	448P	448P	250 mm	1122 mm	SG			1	LEDGE
10	Ledge_02	200 mm	1072 mm	18 mm	14142- Auburn Kaos Oak	14142- Auburn Kaos Oak	448P	448P	448P	448P	250 mm	1122 mm	SG			1	LEDGE
11	Ledge_03	300 mm	640 mm	25 mm	14142- Auburn Kaos Oak	14142- Auburn Kaos Oak	448P	448P	448P	448P	350 mm	690 mm	SG			1	Exposed
12	Ledge_04	300 mm	640 mm	25 mm	14142- Auburn Kaos Oak	14142- Auburn Kaos Oak	448P	448P	448P	448P	350 mm	690 mm	SG			1	Exposed
13	Ledge_05	300 mm	640 mm	25 mm	14142- Auburn Kaos Oak	14142- Auburn Kaos Oak	448P	448P	448P	448P	350 mm	690 mm	SG			1	Exposed
14	Ledge_06	300 mm	640 mm	25 mm	14142- Auburn Kaos Oak	14142- Auburn Kaos Oak	448P	448P	448P	448P	350 mm	690 mm	SG			1	Exposed
15	Ledge_07	300 mm	640 mm	25 mm	14142- Auburn Kaos Oak	14142- Auburn Kaos Oak	448P	448P	448P	448P	350 mm	690 mm	SG			1	Exposed
16	RAIL_A	564 mm	100 mm	18 mm	White laminate	White laminate	448P	414P	414P	414P	614 mm	150 mm	SG		0	2	FRONT RAIL_BC3DRW_600.0, RAIL FRONT_BC1DCY_600.0
17	RAIL_A	664 mm	100 mm	18 mm	White laminate	White laminate	448P	414P	414P	414P	714 mm	150 mm	SG		0	1	RAIL FRONT_BC2DS_700.0
18	RAIL_A	924 mm	100 mm	18 mm	White laminate	White laminate	448P	414P	414P	414P	974 mm	150 mm	SG		0	1	RAIL A_BCSCLH_960
19	RAIL_B	164 mm	550 mm	18 mm	White laminate	White laminate	448P	414P	414P	414P	214 mm	600 mm	SG	HG	0	1	TOP PANEL_BC1D_200.0
20	RAIL_B	564 mm	100 mm	18 mm	White laminate	White laminate	448P	414P	414P	414P	614 mm	150 mm	SG	HG	0	1	TOP PANEL_BC1DCY_600.0
21	RAIL_B	564 mm	550 mm	18 mm	White laminate	White laminate	448P	414P	414P	414P	614 mm	600 mm	SG	HG	0	1	TOP PANEL_BC3DRW_600.0
22	RAIL_B	664 mm	100 mm	18 mm	White laminate	White laminate	448P	414P	414P	414P	714 mm	150 mm	SG	HG	0	1	TOP PANEL_BC2DS_700.0
23	RAIL_B	764 mm	295 mm	18 mm	White laminate	White laminate	448 P	414P	414P	414P	814 mm	345 mm	SG	HG	MINIFIX SET	1	TOP PANEL_WCD2D_800.0
24	RAIL_B	764 mm	295 mm	18 mm	White laminate	White laminate	448P	414P	414P	414P	814 mm	345 mm	SG	HG	MINIFIX SET	1	TOP PANEL_WCD2D_800.0
25	RAIL_B	924 mm	100 mm	18 mm	White laminate	White laminate	448P	414P	414P	414P	974 mm	150 mm	SG	HG	0	1	RAIL B_BCSCLH_960
26	RIGHT_SIDE	600 mm	295 mm	18 mm	14142- Auburn Kaos Oak	White laminate	448 P	414P	414P	414P	650 mm	345 mm	SG	HG	MINIFIX SET	1	RIGHT SIDE_WCD2D_800.0
27	RIGHT_SIDE	600 mm	295 mm	18 mm	14142- Auburn Kaos Oak	White laminate	448P	414P	414P	414P	650 mm	345 mm	SG	HG	MINIFIX SET	1	RIGHT SIDE_WCD2D_800.0
28	RIGHT_SIDE	605 mm	550 mm	18 mm	White laminate	White laminate	448P	414P	414P	414P	655 mm	600 mm	SG	HG	0	3	RIGHT SIDE_BC1D_200.0, RIGHT SIDE_BC3DRW_600.0, RIGHT SIDE_BCSCLH_960
29	RIGHT_SIDE	605 mm	600 mm	18 mm	White laminate	White laminate	448P	414P	414P	414P	655 mm	650 mm	SG	HG	0	1	RIGHT_SIDE_BC2DS_700.0
30	RIGHT_SIDE	705 mm	570 mm	18 mm	White laminate	White laminate	448P	414P	414P	414P	755 mm	620 mm	SG	HG	0	1	RIGHT SIDE_BC1DCY_600.0
Sheet1

sample01.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
38	LEFT_SIDE	600 mm	295 mm	18 mm	14142- Auburn Kaos Oak	White laminate	448P	414P	414P	414P	650 mm	345 mm	SG	HG	MINIFIX SET	1	LEFT SIDE_WCD2D_800.0
39	LEFT_SIDE	600 mm	295 mm	18 mm	White laminate	White laminate	448 P	414P	414P	414P	650 mm	345 mm	SG	HG	0	1	LEFT SIDE_WCD2D_800.0
40	LEFT_SIDE	605 mm	550 mm	18 mm	White laminate	White laminate	448P	414P	414P	414P	655 mm	600 mm	SG	HG	0	1	LEFT SIDE PANEL_BC1D_200.0
41	LEFT_SIDE	605 mm	550 mm	18 mm	White laminate	White laminate	448P	414P	414P	414P	655 mm	600 mm	SG	HG	0	1	LEFT SIDE_BC3DRW_600.0
42	LEFT_SIDE	605 mm	550 mm	18 mm	White laminate	White laminate	448P	414P	414P	414P	655 mm	600 mm	SG	HG	0	1	LEFT SIDE_BCSCLH_960
43	LEFT_SIDE	605 mm	600 mm	18 mm	White laminate	White laminate	448P	414P	414P	414P	655 mm	650 mm	SG	HG	0	1	LEFT_SIDE_BC2DS_700.0
44	LEFT_SIDE	705 mm	570 mm	18 mm	White laminate	White laminate	448P	414P	414P	414P	755 mm	620 mm	SG	HG	0	1	LEFT SIDE_BC1DCY_600.0
45	Ledge_01	200 mm	1072 mm	18 mm	14142- Auburn Kaos Oak	14142- Auburn Kaos Oak	448P	448P	448P	448P	250 mm	1122 mm	SG			1	LEDGE
46	Ledge_02	200 mm	1072 mm	18 mm	14142- Auburn Kaos Oak	14142- Auburn Kaos Oak	448P	448P	448P	448P	250 mm	1122 mm	SG			1	LEDGE
47	Ledge_03	300 mm	640 mm	25 mm	14142- Auburn Kaos Oak	14142- Auburn Kaos Oak	448P	448P	448P	448P	350 mm	690 mm	SG			1	Exposed
48	Ledge_04	300 mm	640 mm	25 mm	14142- Auburn Kaos Oak	14142- Auburn Kaos Oak	448P	448P	448P	448P	350 mm	690 mm	SG			1	Exposed
49	Ledge_05	300 mm	640 mm	25 mm	14142- Auburn Kaos Oak	14142- Auburn Kaos Oak	448P	448P	448P	448P	350 mm	690 mm	SG			1	Exposed
50	Ledge_06	300 mm	640 mm	25 mm	14142- Auburn Kaos Oak	14142- Auburn Kaos Oak	448P	448P	448P	448P	350 mm	690 mm	SG			1	Exposed
51	Ledge_07	300 mm	640 mm	25 mm	14142- Auburn Kaos Oak	14142- Auburn Kaos Oak	448P	448P	448P	448P	350 mm	690 mm	SG			1	Exposed
52	RAIL_A	564 mm	100 mm	18 mm	White laminate	White laminate	448P	414P	414P	414P	614 mm	150 mm	SG		0	1	FRONT RAIL_BC3DRW_600.0
53	RAIL_A	564 mm	100 mm	18 mm	White laminate	White laminate	448P	414P	414P	414P	614 mm	150 mm	SG		0	1	RAIL FRONT_BC1DCY_600.0
54	RAIL_A	664 mm	100 mm	18 mm	White laminate	White laminate	448P	414P	414P	414P	714 mm	150 mm	SG		0	1	RAIL FRONT_BC2DS_700.0
55	RAIL_A	924 mm	100 mm	18 mm	White laminate	White laminate	448P	414P	414P	414P	974 mm	150 mm	SG		0	1	RAIL A_BCSCLH_960
56	RAIL_B	164 mm	550 mm	18 mm	White laminate	White laminate	448P	414P	414P	414P	214 mm	600 mm	SG	HG	0	1	TOP PANEL_BC1D_200.0
57	RAIL_B	564 mm	100 mm	18 mm	White laminate	White laminate	448P	414P	414P	414P	614 mm	150 mm	SG	HG	0	1	TOP PANEL_BC1DCY_600.0
58	RAIL_B	564 mm	550 mm	18 mm	White laminate	White laminate	448P	414P	414P	414P	614 mm	600 mm	SG	HG	0	1	TOP PANEL_BC3DRW_600.0
59	RAIL_B	664 mm	100 mm	18 mm	White laminate	White laminate	448P	414P	414P	414P	714 mm	150 mm	SG	HG	0	1	TOP PANEL_BC2DS_700.0
60	RAIL_B	764 mm	295 mm	18 mm	White laminate	White laminate	448 P	414P	414P	414P	814 mm	345 mm	SG	HG	MINIFIX SET	1	TOP PANEL_WCD2D_800.0
61	RAIL_B	764 mm	295 mm	18 mm	White laminate	White laminate	448P	414P	414P	414P	814 mm	345 mm	SG	HG	MINIFIX SET	1	TOP PANEL_WCD2D_800.0
62	RAIL_B	924 mm	100 mm	18 mm	White laminate	White laminate	448P	414P	414P	414P	974 mm	150 mm	SG	HG	0	1	RAIL B_BCSCLH_960
63	RIGHT_SIDE	600 mm	295 mm	18 mm	14142- Auburn Kaos Oak	White laminate	448 P	414P	414P	414P	650 mm	345 mm	SG	HG	MINIFIX SET	1	RIGHT SIDE_WCD2D_800.0
64	RIGHT_SIDE	600 mm	295 mm	18 mm	14142- Auburn Kaos Oak	White laminate	448P	414P	414P	414P	650 mm	345 mm	SG	HG	MINIFIX SET	1	RIGHT SIDE_WCD2D_800.0
65	RIGHT_SIDE	605 mm	550 mm	18 mm	White laminate	White laminate	448P	414P	414P	414P	655 mm	600 mm	SG	HG	0	1	RIGHT SIDE_BC1D_200.0
66	RIGHT_SIDE	605 mm	550 mm	18 mm	White laminate	White laminate	448P	414P	414P	414P	655 mm	600 mm	SG	HG	0	1	RIGHT SIDE_BC3DRW_600.0
67	RIGHT_SIDE	605 mm	550 mm	18 mm	White laminate	White laminate	448P	414P	414P	414P	655 mm	600 mm	SG	HG	0	1	RIGHT SIDE_BCSCLH_960
68	RIGHT_SIDE	605 mm	600 mm	18 mm	White laminate	White laminate	448P	414P	414P	414P	655 mm	650 mm	SG	HG	0	1	RIGHT_SIDE_BC2DS_700.0
69	RIGHT_SIDE	705 mm	570 mm	18 mm	White laminate	White laminate	448P	414P	414P	414P	755 mm	620 mm	SG	HG	0	1	RIGHT SIDE_BC1DCY_600.0
Sheet1

 

[Peter_SSs]

Give this a try with a copy of your data.

Sub Split_Rows()
  Dim r As Long, num As Long
  
  Application.ScreenUpdating = False
  For r = Range("P" & Rows.Count).End(xlUp).Row To 4 Step -1
    num = Range("P" & r).Value
    If num > 1 Then
      Rows(r).Copy
      Rows(r + 1).Resize(num - 1).Insert
      Range("P" & r).Resize(num).Value = 1
      Range("Q" & r).Resize(num).Value = Application.Transpose(Split(Replace(Range("Q" & r).Value, ", ", ","), ","))
    End If
  Next r
  Application.CutCopyMode = False
  Application.ScreenUpdating = True
End Sub

 

[Fluff]

Cross posted Finding a value greater than one in particular column, insert row, paste and change the value - OzGrid Free Excel/VBA Help Forum

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[sekar_r24]

Hi.,
Regret for the inconvenience. Defintely will do henceforth.

Cross posted Finding a value greater than one in particular column, insert row, paste and change the value - OzGrid Free Excel/VBA Help Forum

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[sekar_r24]

Sub Split_Rows() Dim r As Long, num As Long Application.ScreenUpdating = False For r = Range("P" & Rows.Count).End(xlUp).Row To 4 Step -1 num = Range("P" & r).Value If num > 1 Then Rows(r).Copy Rows(r + 1).Resize(num - 1).Insert Range("P" & r).Resize(num).Value = 1 Range("Q" & r).Resize(num).Value = Application.Transpose(Split(Replace(Range("Q" & r).Value, ", ", ","), ",")) End If Next r Application.CutCopyMode = False Application.ScreenUpdating = True End Sub

Hi.,

Thanks for your support. Works good. Great!

Could you explain why the for loop is upto 4 ?

For r = Range("P" & Rows.Count).End(xlUp).Row To 4 Step -1

 

[sekar_r24]

Hi.,

Thanks for your support. Works good. Great!

Could you explain why the for loop is upto 4 ?

For r = Range("P" & Rows.Count).End(xlUp).Row To 4 Step -1

Got it. it is for the no of rows left empty on the top of the data!

 

[Peter_SSs]

You're welcome. Glad to help.

Just to clarify, the 4 should be the top row that needs to be checked. I used 4 because that was the top row as far as knew from your XL2BB sheet in post 1.