Find a cell containing specific text and make it into Table Form

[edcelone]

Hi,

Would like to ask for help. I have this 1 column list below. This is what I wanted to do:
1. Find Cells containing "Best Position", then copy 1 cell above and 6 cell below as shown on Data 2 (column A).
2. Make it into Table Form as shown on Data 2 (columns B to D)

Data 1:

Book1
	A
1	Type
2	Name
3	Status
4	Odds
5	Payback
6	Combinable
7	Head
8	i
9	Matchups (MLF Championship 2020)
10	Best position
11	Id No: 46373893
12	OPEN
13	Jake, Daniel
14	1.85
15	Ana, Rory
16	1.95
17	94.93
18	Opens:
19	83939 939
20	Suspends:
21	2939300 02
22	Closes:
23	200830 2300
24	Head
25	i
26	Matchups (CDE Winner 2020)
27	Best position
28	Id No: 46373892738
29	OPEN
30	Night, Jason
31	1.8
32	Ocean, Tiger
33	2
34	94.74
35	Opens:
36	200824 1826
37	Suspends:
38	200827 1300
39	Closes:
40	200830 2300
41	Head
42	i
43	Tournament Predictions (BMW Championship 2020)
44	Hole in One in Round 3
45	Bet Offer Id: 2222785334
46	OPEN
47	Yes
48	n
49	No
50	n
51	BMW Championship 2020
52	n
53	0
54	Opens:
55	200824 0855
56	Suspends:
57	200827 1300
58	Closes:
59	200830 2300
60	Head
61	i
62	Matchups (FGE Tourney)
63	Best position
64	Id No: 475859
65	OPEN
66	Harman, John
67	1.91
68	Conners, Calley
69	1.91
70	95.5
71	Opens:
72	200824 1826
73	Suspends:
74	200827 1300
75	Closes:
76	200830 2300
77	Head
78	i
79	Matchups (FDA 2020)
80	Best position
81	Id No: 463738930
82	OPEN
83	Hatton, Harton
84	1.8
85	Woodland, Edu
86	2
87	94.74
88	Opens:
89	200824 1826
90	Suspends:
91	200827 1300
92	Closes:
93	200830 2300
94	Matchups (BMW 2018)
95	Best position
96	Id No: 4637586
97	OPEN
98	Oosthuizen, Arc
99	1.91
100	Na, Kiki
101	1.91
102	95.5
103	Opens:
104	200824 1826
105	Suspends:
106	200827 1300
107	Closes:
108	200830 2300
109	Head
110	i
111	Predictions (BMW 2020)
112	One in Round 3
113	Bet Offer Id: 2222785334
114	OPEN
115	Yes
116	n
117	No
118	n
119	BMW 2020
120	n
121	0
122	Opens:
123	200824 0855
124	Suspends:
125	200827 1300
126	Closes:
127	200830 2300
Sheet1

Data 2:

Book1
	A	B	C	D	E
1	Matchups (MLF Championship 2020)	Matchups (MLF Championship 2020)	Best position	Jake, Daniel	1.85
2	Best position	Id No: 46373893	OPEN	Ana, Rory	1.95
3	Id No: 46373893	Matchups (CDE Winner 2020)	Best position	Night, Jason	1.8
4	OPEN	Id No: 46373892738	OPEN	Ocean, Tiger	2
5	Jake, Daniel	Matchups (FGE Tourney)	Best position	Harman, John	1.91
6	1.85	Id No: 475859	OPEN	Conners, Calley	1.91
7	Ana, Rory	Matchups (FDA 2020)	Best position	Hatton, Harton	1.8
8	1.95	Id No: 463738930	OPEN	Woodland, Edu	2
9	Matchups (CDE Winner 2020)	Matchups (BMW 2018)	Best position	Oosthuizen, Arc	1.91
10	Best position	Id No: 4637586	OPEN	Na, Kiki	1.91
11	Id No: 46373892738
12	OPEN
13	Night, Jason
14	1.8
15	Ocean, Tiger
16	2
17	Matchups (FGE Tourney)
18	Best position
19	Id No: 475859
20	OPEN
21	Harman, John
22	1.91
23	Conners, Calley
24	1.91
25	Matchups (FDA 2020)
26	Best position
27	Id No: 463738930
28	OPEN
29	Hatton, Harton
30	1.8
31	Woodland, Edu
32	2
33	Matchups (BMW 2018)
34	Best position
35	Id No: 4637586
36	OPEN
37	Oosthuizen, Arc
38	1.91
39	Na, Kiki
40	1.91
Sheet2

 

[edcelone]

Using VBA Version

 

[Peter_SSs]

Give this a try.

Sub BestPosition()
  Dim a As Variant, b As Variant
  Dim i As Long, k As Long
  
  a = Range("A1", Range("A" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To UBound(a), 1 To 4)
  For i = 1 To UBound(a)
    If a(i, 1) = "Best position" Then
      k = k + 2
      b(k - 1, 1) = a(i - 1, 1)
      b(k - 1, 2) = a(i, 1)
      b(k, 1) = a(i + 1, 1)
      b(k, 2) = a(i + 2, 1)
      b(k - 1, 3) = a(i + 3, 1)
      b(k - 1, 4) = a(i + 4, 1)
      b(k, 3) = a(i + 5, 1)
      b(k, 4) = a(i + 6, 1)
    End If
  Next i
  With Range("B1:E1").Resize(k)
    .Value = b
    .Columns.AutoFit
  End With
End Sub