1 column data converted into multiple column

edcelone

New Member
Joined
Aug 22, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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
ABC
1Power Distribution Company
2Meralco
3Aboitiz
4NAPOCOR
5NGCP
6BATELEC 1
7BATELEC 2
8ISELCO
9
10Total
11Charge
12
13Oil Player
14
15Shell
164637
175674
18
19Petron
202627
2160
2267
23356
24
25Chevron
26930
27722
28-85
29
30ifuel
31-384
32637
33
34Seaoil
356474
36-84
37
38GasGo
394637
40-848
416473
42
43Unioil
44823
453938
46
47Installed Capacity Total
48Charge
49
50Megawatt per Hour
512018 EW
522019 Total
53
545000
55-93784
5662535
57
586530040
5963554
608789
6178
62
6374848
644665
655776
66
6785959
6852626
69970
70-767
71
725363
7374748
747374
75
76784
773748
785674
79
805243
81-9697
8255
83
84Total
85Oil Player
86
87Top Load 1000
882018 EW
892019 Total
90
915.5
925083
93-7980
94
9557.09
966474
97-4858
989404
99
10058
1017839
102-5858
103-83494
104
10578.9
106838
107-39
108
10989
1106373
1118793
11293
113
114766
1153664
116
11789.6
11863873
119-8696
120
121Total
122Charge
123Distribution
124
125Top Load 2000
1262018 EW
1272019 Total
128
1294.8
130482
1318933
132
13384.8
1346272
135-4858
1363484
137
13879.79
1398484
140895
141-83494
142
143805
144472
145-39
146
14792
14873
149693
150574
151
152595
1536262
1547238
155
15678.9
1578595
1588383
159
160Total
161Closed
162Energy Distribution
Power


Power Distribution.xlsx
EFGHI
1Power Distribution CompanyOil PlayerMegawatt per HourTop Load 1000Top Load 2000
2MeralcoShell50005.54.8
3AboitizPetron653004057.0984.8
4NAPOCORChevron748485879.79
5NGCPifuel8595978.9805
6BATELEC 1Seaoil53638992
7BATELEC 2GasGo784766595
8ISELCOUnioil524389.678.9
Power
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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
ABCDE
1Power Distribution CompanyOil PlayerMegawatt per HourTop Load 1000Top Load 2000
2MeralcoShell50005.54.8
3AboitizPetron653004057.0984.8
4NAPOCORChevron748485879.79
5NGCPifuel8595978.9805
6BATELEC 1Seaoil53638992
7BATELEC 2GasGo784766595
8ISELCOUnioil524389.678.9
9
10Total
11Charge
12
13Oil Player
14
15Shell
164637
175674
18
19Petron
202627
2160
2267
23356
24
25Chevron
26930
27722
28-85
29
30ifuel
31-384
32637
33
34Seaoil
356474
36-84
37
38GasGo
394637
40-848
416473
42
43Unioil
44823
453938
46
47Installed Capacity Total
48Charge
49
50Megawatt per Hour
512018 EW
522019 Total
53
545000
55-93784
5662535
57
586530040
5963554
608789
6178
62
6374848
644665
655776
66
6785959
6852626
69970
70-767
71
725363
7374748
747374
75
76784
773748
785674
79
805243
81-9697
8255
Sheet1
Cell Formulas
RangeFormula
B2:B8B2=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:E8C2=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.
 

ER_Neha

Board Regular
Joined
Jul 25, 2020
Messages
121
Office Version
  1. 365
Platform
  1. Windows
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
ABCDE
1Power Distribution CompanyOil PlayerMegawatt per HourTop Load 1000Top Load 2000
2MeralcoShell50005.54.8
3AboitizPetron653004057.0984.8
4NAPOCORChevron748485879.79
5NGCPifuel8595978.9805
6BATELEC 1Seaoil53638992
7BATELEC 2GasGo784766595
8ISELCOUnioil524389.678.9
9
10Total
11Charge
12
13Oil Player
14
15Shell
164637
175674
18
19Petron
202627
2160
2267
23356
24
25Chevron
26930
27722
28-85
29
30ifuel
31-384
32637
33
34Seaoil
356474
36-84
37
38GasGo
394637
40-848
416473
42
43Unioil
44823
453938
46
47Installed Capacity Total
48Charge
49
50Megawatt per Hour
512018 EW
522019 Total
53
545000
55-93784
5662535
57
586530040
5963554
608789
6178
62
6374848
644665
655776
66
6785959
6852626
69970
70-767
71
725363
7374748
747374
75
76784
773748
785674
79
805243
81-9697
8255
Sheet1
Cell Formulas
RangeFormula
B2:B8B2=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:E8C2=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
pic 1.png
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

VBA Code:
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 ...
1598149462236.png

...and paste into cell A1?
 

ER_Neha

Board Regular
Joined
Jul 25, 2020
Messages
121
Office Version
  1. 365
Platform
  1. Windows
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.
Untitled.png
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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?

1598160988831.png
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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",
 

Watch MrExcel Video

Forum statistics

Threads
1,122,657
Messages
5,597,391
Members
414,142
Latest member
Banyangt

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top