1 column data converted into multiple column

[edcelone]

Hi,

Good day..I have this 1 column data below..I would like to convert it into multiple column as shown below..Note that the number of data are changing..The only pattern I can see is that the data is in between the italized-bold words and the data to get for Oil Player, Megawatt per Hour, Top Load 1000, Top Load 2000 is the data after the blank cell as shown below.

Power Distribution.xlsx
	A	B	C
1	Power Distribution Company
2	Meralco
3	Aboitiz
4	NAPOCOR
5	NGCP
6	BATELEC 1
7	BATELEC 2
8	ISELCO
9
10	Total
11	Charge
12
13	Oil Player
14
15	Shell
16	4637
17	5674
18
19	Petron
20	2627
21	60
22	67
23	356
24
25	Chevron
26	930
27	722
28	-85
29
30	ifuel
31	-384
32	637
33
34	Seaoil
35	6474
36	-84
37
38	GasGo
39	4637
40	-848
41	6473
42
43	Unioil
44	823
45	3938
46
47	Installed Capacity Total
48	Charge
49
50	Megawatt per Hour
51	2018 EW
52	2019 Total
53
54	5000
55	-93784
56	62535
57
58	6530040
59	63554
60	8789
61	78
62
63	74848
64	4665
65	5776
66
67	85959
68	52626
69	970
70	-767
71
72	5363
73	74748
74	7374
75
76	784
77	3748
78	5674
79
80	5243
81	-9697
82	55
83
84	Total
85	Oil Player
86
87	Top Load 1000
88	2018 EW
89	2019 Total
90
91	5.5
92	5083
93	-7980
94
95	57.09
96	6474
97	-4858
98	9404
99
100	58
101	7839
102	-5858
103	-83494
104
105	78.9
106	838
107	-39
108
109	89
110	6373
111	8793
112	93
113
114	766
115	3664
116
117	89.6
118	63873
119	-8696
120
121	Total
122	Charge
123	Distribution
124
125	Top Load 2000
126	2018 EW
127	2019 Total
128
129	4.8
130	482
131	8933
132
133	84.8
134	6272
135	-4858
136	3484
137
138	79.79
139	8484
140	895
141	-83494
142
143	805
144	472
145	-39
146
147	92
148	73
149	693
150	574
151
152	595
153	6262
154	7238
155
156	78.9
157	8595
158	8383
159
160	Total
161	Closed
162	Energy Distribution
Power

Power Distribution.xlsx
	E	F	G	H	I
1	Power Distribution Company	Oil Player	Megawatt per Hour	Top Load 1000	Top Load 2000
2	Meralco	Shell	5000	5.5	4.8
3	Aboitiz	Petron	6530040	57.09	84.8
4	NAPOCOR	Chevron	74848	58	79.79
5	NGCP	ifuel	85959	78.9	805
6	BATELEC 1	Seaoil	5363	89	92
7	BATELEC 2	GasGo	784	766	595
8	ISELCO	Unioil	5243	89.6	78.9
Power

 

[Peter_SSs]

Welcome to the MrExcel board!

You didn't say if you were interested in a particular approach (formula/vba/power query) but here is a possible formula approach.

B1:E1 headings entered manually.

In column B, formula gets entered in B2 only. The other results should automatically 'spill' down the column.

Similar for the C2 formula. Then copy the C2 formula across to E2.

edcelone 2020-08-22 1.xlsm
	A	B	C	D	E
1	Power Distribution Company	Oil Player	Megawatt per Hour	Top Load 1000	Top Load 2000
2	Meralco	Shell	5000	5.5	4.8
3	Aboitiz	Petron	6530040	57.09	84.8
4	NAPOCOR	Chevron	74848	58	79.79
5	NGCP	ifuel	85959	78.9	805
6	BATELEC 1	Seaoil	5363	89	92
7	BATELEC 2	GasGo	784	766	595
8	ISELCO	Unioil	5243	89.6	78.9
9
10	Total
11	Charge
12
13	Oil Player
14
15	Shell
16	4637
17	5674
18
19	Petron
20	2627
21	60
22	67
23	356
24
25	Chevron
26	930
27	722
28	-85
29
30	ifuel
31	-384
32	637
33
34	Seaoil
35	6474
36	-84
37
38	GasGo
39	4637
40	-848
41	6473
42
43	Unioil
44	823
45	3938
46
47	Installed Capacity Total
48	Charge
49
50	Megawatt per Hour
51	2018 EW
52	2019 Total
53
54	5000
55	-93784
56	62535
57
58	6530040
59	63554
60	8789
61	78
62
63	74848
64	4665
65	5776
66
67	85959
68	52626
69	970
70	-767
71
72	5363
73	74748
74	7374
75
76	784
77	3748
78	5674
79
80	5243
81	-9697
82	55
Sheet1

Cell Formulas
Range	Formula
B2:B8	B2	=INDEX(FILTER(INDEX(A:A,MATCH(B1,A:A,0)+1):A1000,ISTEXT(INDEX(A:A,MATCH(B1,A:A,0)+1):A1000)),SEQUENCE(ROWS(A2:A8)))
C2:E8	C2	=INDEX(FILTER(INDEX($A:$A,MATCH(C1,$A:$A,0)+3):$A1000,ISNUMBER(INDEX($A:$A,MATCH(C1,$A:$A,0)+3):$A1000)*(INDEX($A:$A,MATCH(C1,$A:$A,0)+2):$A999="")),SEQUENCE(ROWS(A2:A8)))
Dynamic array formulas.

 

[edcelone]

hi @Peter_SSs

I am particularly interested on its VBA version.

 

[ER_Neha]

Welcome to the MrExcel board!

You didn't say if you were interested in a particular approach (formula/vba/power query) but here is a possible formula approach.

B1:E1 headings entered manually.

In column B, formula gets entered in B2 only. The other results should automatically 'spill' down the column.

Similar for the C2 formula. Then copy the C2 formula across to E2.

edcelone 2020-08-22 1.xlsm
	A	B	C	D	E
1	Power Distribution Company	Oil Player	Megawatt per Hour	Top Load 1000	Top Load 2000
2	Meralco	Shell	5000	5.5	4.8
3	Aboitiz	Petron	6530040	57.09	84.8
4	NAPOCOR	Chevron	74848	58	79.79
5	NGCP	ifuel	85959	78.9	805
6	BATELEC 1	Seaoil	5363	89	92
7	BATELEC 2	GasGo	784	766	595
8	ISELCO	Unioil	5243	89.6	78.9
9
10	Total
11	Charge
12
13	Oil Player
14
15	Shell
16	4637
17	5674
18
19	Petron
20	2627
21	60
22	67
23	356
24
25	Chevron
26	930
27	722
28	-85
29
30	ifuel
31	-384
32	637
33
34	Seaoil
35	6474
36	-84
37
38	GasGo
39	4637
40	-848
41	6473
42
43	Unioil
44	823
45	3938
46
47	Installed Capacity Total
48	Charge
49
50	Megawatt per Hour
51	2018 EW
52	2019 Total
53
54	5000
55	-93784
56	62535
57
58	6530040
59	63554
60	8789
61	78
62
63	74848
64	4665
65	5776
66
67	85959
68	52626
69	970
70	-767
71
72	5363
73	74748
74	7374
75
76	784
77	3748
78	5674
79
80	5243
81	-9697
82	55
Sheet1

Cell Formulas
Range	Formula
B2:B8	B2	=INDEX(FILTER(INDEX(A:A,MATCH(B1,A:A,0)+1):A1000,ISTEXT(INDEX(A:A,MATCH(B1,A:A,0)+1):A1000)),SEQUENCE(ROWS(A2:A8)))
C2:E8	C2	=INDEX(FILTER(INDEX($A:$A,MATCH(C1,$A:$A,0)+3):$A1000,ISNUMBER(INDEX($A:$A,MATCH(C1,$A:$A,0)+3):$A1000)*(INDEX($A:$A,MATCH(C1,$A:$A,0)+2):$A999="")),SEQUENCE(ROWS(A2:A8)))
Dynamic array formulas.

Hello, I tried using the formula , but it's showing me that the function is not valid. I have attached the screenshot. Can you please help. how to bring the solution

 

[Peter_SSs]

I am particularly interested on its VBA version.

OK, try this with a copy of your workbook. Data in column A, results to go into columns E:H

Sub ExtractData()
  Dim rws As Long, i As Long, r As Long
  Dim Hdr As Range, StartCell As Range
  
  Range("E1:H1").Value = Array("Oil Player", "Megawatt per Hour", "Top Load 1000", "Top Load 2000")
  rws = Range("A1").End(xlDown).Row - 1
  For Each Hdr In Range("E1:H1")
    Set StartCell = Columns("A").Find(What:=Hdr.Value, LookAt:=xlWhole, MatchCase:=False)
    i = 0
    r = 0
    Do Until r = rws
      i = i + 1
      If Len(StartCell.Offset(i).Value) > 0 And Len(StartCell.Offset(i - 1).Value) = 0 Then
        r = r + 1
        Hdr.Offset(r).Value = StartCell.Offset(i).Value
      End If
    Loop
  Next Hdr
End Sub

@ER_Neha
I'm not sure why you get that message. Do you still get it if cells B1:E1 are populated with the required headings as suggested?

B1:E1 headings entered manually.

In your Excel version do you normally use comma as the separator in formulas, or semicolon?

What happens if you start with a blank sheet and copy my mini-sheet from post 2 using the BB2XL button ...

...and paste into cell A1?

 

[ER_Neha]

Hello , I use comma as the separator in formulas.
And while copying bb2xl I get the same pop up as shown in the image posted by you. and this is my excel page after pasting the content.

 

[Peter_SSs]

I get the same pop up

Do you have the FILTER function in your Excel version? If you click the fx button and choose 'Lookup & Reference' is FILTER in the list?

 

[ER_Neha]

Do you have the FILTER function in your Excel version? If you click the fx button and choose 'Lookup & Reference' is FILTER in the list?

View attachment 20926

Thank you for the help. I checked I don't have this filter function in the LOOKUP & REFERENCE list.

 

[Peter_SSs]

Thank you for the help. I checked I don't have this filter function in the LOOKUP & REFERENCE list

OK. I thought you would just get a #NAME? error in the cell in that case.
If you have Excel 365 as your profile says then you should get FILTER and other new functions "fairly soon",

 

[edcelone]

Thank you @Peter_SSs !