Vlookup Shift Down Paste

[eddiegb1]

Hi,

Is there a formula or a code that can do the following?

1. Find the J1 string in the REFDES column (B).
2. Shift the J1 row / columns H, I, J and K one row down.
3. Repeat the process until all the J1 string are accounted for.
4. Still using J1 as lookup, copy the string (SDPS005F for example) from the PIN_NAME column(D).
5. Then Paste the string (SDPS005) in column K.
6. Repeat the process until all PIN_NAME strings are copied and pasted in Col L.

(see > After edit.. data)

=====From this raw DATA====
A B C D E F G H I J K (Col) L(PIN_NAME)

Sort	REFDES	PIN_NUMBER	PIN_NAME	PIN_X	PIN_Y	NET_NAME	Sort	REF_DES	DUT_FET_PIN#	TC_FET_PIN#
1	J01	1011	1011	410.31	543.3	D1_VDD-A__AF8	1	Q001-A	Q001.1	Q001.6
2	J01	1401	1401	600.24	437.95	D1_VDD-A__AF8	2	Q001-B	Q001.4	Q001.3
3	J01	1481	1481	296.36	437.95	D1_VDD-A__AF8	3	Q002-A	Q002.1	Q002.6
4	J01	1921	1921	600.24	391.18	D1_VDD-A__AF8	4	Q002-B	Q002.4	Q002.3
5	J01	2011	2011	258.38	449.76	D1_VDD-A__AF8	5	Q003-A	Q003.1	Q003.6
6	J01	2451	2451	562.25	402.99	D1_VDD-A__AF8	6	Q003-B	Q003.4	Q003.3
7	J01	2971	2971	562.25	356.21	D1_VDD-A__AF8	7	Q004-A	Q004.1	Q004.6
8	J01	3511	3511	486.28	309.44	D1_VDD-A__AF8	8	Q004-B	Q004.4	Q004.3
9	J1	D0307	SDPS005F	2379.04	-3958.8	D1_VDD-A__AF8	9	Q005-A	Q005.1	Q005.6
10	J01	1891	1891	714.19	449.76	D1_VDD-A__AP8	10	Q005-B	Q005.4	Q005.3
11	J01	2941	2941	676.2	297.63	D1_VDD-A__AP8	11	Q006-A	Q006.1	Q006.6
12	J01	3441	3441	752.17	250.86	D1_VDD-A__AP8	12	Q006-B	Q006.4	Q006.3
13	J01	3971	3971	714.19	262.67	D1_VDD-A__AP8	13	Q007-A	Q007.1	Q007.6
14	J01	4491	4491	714.19	215.9	D1_VDD-A__AP8	14	Q007-B	Q007.4	Q007.3
15	J01	4541	4541	524.27	157.32	D1_VDD-A__AP8	15	Q008-A	Q008.1	Q008.6
16	J01	5021	5021	676.2	110.55	D1_VDD-A__AP8	16	Q008-B	Q008.4	Q008.3
17	J01	5591	5591	486.28	122.36	D1_VDD-A__AP8	17	Q009-A	Q009.1	Q009.6
18	J1	D1307	SDPS009F	2790.46	-4630.17	D1_VDD-A__AP8	18	Q009-B	Q009.4	Q009.3
19	J01	3071	3071	182.41	356.21	D1_VDD-A__AV8	19	Q0010-A	Q0010.1	Q0010.6
20	J01	3081	3081	144.43	297.63	D1_VDD-A__AV8	20	Q0010-B	Q0010.4	Q0010.3
21	J01	4101	4101	220.39	204.09	D1_VDD-A__AV8	21	Q0011-A	Q0011.1	Q0011.6
22	J01	4131	4131	106.44	262.67	D1_VDD-A__AV8	22	Q0011-B	Q0011.4	Q0011.3
23	J01	4611	4611	258.38	215.9	D1_VDD-A__AV8	23	Q0012-A	Q0012.1	Q0012.6
24	J01	4621	4621	220.39	157.32	D1_VDD-A__AV8	24	Q0012-B	Q0012.4	Q0012.3
25	J01	5151	5151	182.41	169.13	D1_VDD-A__AV8	25	Q0013-A	Q0013.1	Q0013.6
26	J01	5161	5161	144.43	110.55	D1_VDD-A__AV8	26	Q0013-B	Q0013.4	Q0013.3
27	J1	D0309	SDPS000F	2097.1	-4115.11	D1_VDD-A__AV8	27	Q0014-A	Q0014.1	Q0014.6

<colgroup><col><col><col><col><col span="2"><col><col><col><col><col></colgroup><tbody>
</tbody>

...

To this:
==========After edit: Shift Down Paste=============

Sort	REFDES	PIN_NUMBER	PIN_NAME	PIN_X	PIN_Y	NET_NAME	Sort	REF_DES	DUT_FET_PIN#	TC_FET_PIN#	PIN_NAME
1	J01	1011	1011	410.31	543.3	D1_VDD-A__AF8	1	Q001-A	Q001.1	Q001.6	SDPS005F
2	J01	1401	1401	600.24	437.95	D1_VDD-A__AF8	2	Q001-B	Q001.4	Q001.3	SDPS005F
3	J01	1481	1481	296.36	437.95	D1_VDD-A__AF8	3	Q002-A	Q002.1	Q002.6	SDPS005F
4	J01	1921	1921	600.24	391.18	D1_VDD-A__AF8	4	Q002-B	Q002.4	Q002.3	SDPS005F
5	J01	2011	2011	258.38	449.76	D1_VDD-A__AF8	5	Q003-A	Q003.1	Q003.6	SDPS005F
6	J01	2451	2451	562.25	402.99	D1_VDD-A__AF8	6	Q003-B	Q003.4	Q003.3	SDPS005F
7	J01	2971	2971	562.25	356.21	D1_VDD-A__AF8	7	Q004-A	Q004.1	Q004.6	SDPS005F
8	J01	3511	3511	486.28	309.44	D1_VDD-A__AF8	8	Q004-B	Q004.4	Q004.3	SDPS005F
9	J1	D0307	SDPS005F	2379.04	-3958.8	D1_VDD-A__AF8
10	J01	1891	1891	714.19	449.76	D1_VDD-A__AP8	9	Q005-A	Q005.1	Q005.6	SDPS009F
11	J01	2941	2941	676.2	297.63	D1_VDD-A__AP8	10	Q005-B	Q005.4	Q005.3	SDPS009F
12	J01	3441	3441	752.17	250.86	D1_VDD-A__AP8	11	Q006-A	Q006.1	Q006.6	SDPS009F
13	J01	3971	3971	714.19	262.67	D1_VDD-A__AP8	12	Q006-B	Q006.4	Q006.3	SDPS009F
14	J01	4491	4491	714.19	215.9	D1_VDD-A__AP8	13	Q007-A	Q007.1	Q007.6	SDPS009F
15	J01	4541	4541	524.27	157.32	D1_VDD-A__AP8	14	Q007-B	Q007.4	Q007.3	SDPS009F
16	J01	5021	5021	676.2	110.55	D1_VDD-A__AP8	15	Q008-A	Q008.1	Q008.6	SDPS009F
17	J01	5591	5591	486.28	122.36	D1_VDD-A__AP8	16	Q008-B	Q008.4	Q008.3	SDPS009F
18	J1	D1307	SDPS009F	2790.46	-4630.17	D1_VDD-A__AP8
19	J01	3071	3071	182.41	356.21	D1_VDD-A__AV8	17	Q009-A	Q009.1	Q009.6	SDPS000F
20	J01	3081	3081	144.43	297.63	D1_VDD-A__AV8	18	Q009-B	Q009.4	Q009.3	SDPS000F
21	J01	4101	4101	220.39	204.09	D1_VDD-A__AV8	19	Q0010-A	Q0010.1	Q0010.6	SDPS000F
22	J01	4131	4131	106.44	262.67	D1_VDD-A__AV8	20	Q0010-B	Q0010.4	Q0010.3	SDPS000F
23	J01	4611	4611	258.38	215.9	D1_VDD-A__AV8	21	Q0011-A	Q0011.1	Q0011.6	SDPS000F
24	J01	4621	4621	220.39	157.32	D1_VDD-A__AV8	22	Q0011-B	Q0011.4	Q0011.3	SDPS000F
25	J01	5151	5151	182.41	169.13	D1_VDD-A__AV8	23	Q0012-A	Q0012.1	Q0012.6	SDPS000F
26	J01	5161	5161	144.43	110.55	D1_VDD-A__AV8	24	Q0012-B	Q0012.4	Q0012.3	SDPS000F
27	J1	D0309	SDPS000F	2097.1	-4115.11	D1_VDD-A__AV8

<colgroup><col><col><col><col><col span="2"><col><col><col><col><col><col></colgroup><tbody>
</tbody>

....

Thanks,
Eddie

 

[Fluff]

Assuming you'll never have J1 in 2 or more consecutive rows, try

Sub Shiftdata()
   Dim Ar As Areas
   Dim i As Long, Rw As Long
      
   With Range("B2", Range("B" & Rows.Count).End(xlUp))
      .Replace "J1", "=xxxJ1", xlWhole, , False, , False, False
      Set Ar = .SpecialCells(xlFormulas, xlErrors).Areas
      .Replace "=xxxJ1", "J1", xlWhole, , False, , False, False
   End With
   Rw = 2
   For i = 1 To Ar.Count
      Ar(i).Offset(, 6).Resize(1, 5).Insert xlDown
      Ar(i).Offset(-(Ar(i).Row - Rw), 10).Resize(Ar(i).Row - Rw).Value = Ar(i).Offset(, 2).Value
      Rw = Ar(i).Row + 1
   Next i
End Sub

 

[eddiegb1]

Hi Fluff,

Thank you very much for the code. Worked great after a minor tweak.

Needed to changed Rw = 2 to Rw = 1.

It was shifting 2 rows down instead of 1.

Thanks again,
Eddie

 

[Fluff]

Glad to help & thanks for the feedback