one Column with 6000 rows convert into 6 columns into 1000 rows based on conditions

waqar1239

New Member
Joined
Mar 26, 2017
Messages
27
Hello Team
Here is the example
# 1
1 CREATE POINT 744STEAM:FA0032.PNT
1 PUT POINT 744STEAM:FA0032.PNT TYPE FLOAT
DESC = "4 RECOVERY MCR"
AUDITUPD = NO
COLLECTOR = srhc03
# 2
1 CREATE POINT 735WLCLF01:FY0046B.RO01
1 PUT POINT 735WLCLF01:FY0046B.RO01 TYPE FLOAT
DESC = "WASH FACTOR NO2 MUD WASHER"
AUDITUPD = NO
# 3
1 CREATE POINT C201CP_STA:STATION.MINUTE
1 PUT POINT C201CP_STA:STATION.MINUTE TYPE INTEGER
DESC =
AUDITUPD = NO
COLLECTOR = srhc03

<tbody>
</tbody>

Output

# 1
1 CREATE POINT 744STEAM:FA0032.PNT
1 PUT POINT 744STEAM:FA0032.PNT TYPE FLOAT
DESC = "4 RECOVERY MCR"
AUDITUPD = NO
COLLECTOR = srhc03
# 2
1 CREATE POINT 735WLCLF01:FY0046B.RO01
1 PUT POINT 735WLCLF01:FY0046B.RO01 TYPE FLOAT
DESC = "WASH FACTOR NO2 MUD WASHER"
AUDITUPD = NO
COLLECTOR = srhc03
# 3
1 CREATE POINT C201CP_STA:STATION.MINUTE
1 PUT POINT C201CP_STA:STATION.MINUTE TYPE INTEGER
DESC =
AUDITUPD = NO
COLLECTOR = srhc03

<tbody>
</tbody>


I have this type of data. Please advise me how i can do it.
Rgards
Heaps of thanks in advanc
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sounds like something you should do with Power Query (or "Get and Transform" as it's currently called):

You can find Power Query from the Data tab of your Excel ribbon. Use the "Get Data" button and select the right kind of data source. I'd recommend you don't import the data into Excel manually but point Power Query to the original file / data base if possible. This step might take a couple of extra clicks before you have your data visible in your Power Query editor. Also, make sure your data type is set to Text ( ie. you can see an ABC icon in the column header. If not click the icon and change it to text.).

The first actual step you want to do after you've got your data set in text is to turn it into upper case. Do this using the Format-button on the Transform tab.
Next go to the Add Column -tab and create a Custom Column. Call it what you want. It is only going to contain the case / instance number.

I'd use something like
Code:
= if Text.Start([Data],1) ="#" then [Data] else null
as the Custom column formula. Make sure you'll use whatever is the right column name in your data instead of "[Data]". Insert the right column name using the Available columns list.

Next click on the column header of your new column with your right mouse button and select Fill / Down. All the nulls will be replaced with the case / instance number.

Click on your original data column and set the Text Filter to Does Not Begin with #. Also if you have blank rows in your data column filter those out as well.

Once you're left with nothing but the data rows in your data column create another custom column. This time use something like
Code:
=Text.Start([Data],4)
as the custom column formula. This takes the first 4 characters from your Data column. You're going to use these to create new columns in your next step.

Select the new column and press the Pivot Column button found in the Transform tab. Select your data column as the Values Column and click on the Advanced options and select the Don't Aggregate option from the drop down. This is essential. Also, if some of your instances have more than a single row of each column name you're going to end up with errors after this step.

Now you're almost done. Just rename your columns to what you want and click on the Close & Load button on your Home tab. Also, you might want to rename your query by typing a new name on the Name box on the Query Settings pane you should see on your right at the Query editor window. The query name will also be used as the table name of your final table.

This step might take a bit longer, depending on the number of rows etc. but you're going to end up with just what you wanted.

The beauty of using Power Query for this is that the next time you're going to need to update your data all you need to do is open the Excel file and press the "Update All" button on your Data tab. Power Query will remember all the steps you've made and you can update your data over and over again with a simple click of a button.

I tried to list all the steps needed but in case I missed something you might want to watch some of the Power Query tutorials found on YouTube. Here's a great one by ExcelIsFun. It's 50 minutes long but well worth your time.
 
Upvote 0
with PowerQuery

Column1Custom.1.1Custom.1.2Custom.1.3Custom.1.4Custom.1.5Custom.1.6
# 1# 11 CREATE POINT 744STEAM:FA0032.PNT1 PUT POINT 744STEAM:FA0032.PNT TYPE FLOATDESC = 4 RECOVERY MCRAUDITUPD = NOCOLLECTOR = srhc03
1 CREATE POINT 744STEAM:FA0032.PNT# 21 CREATE POINT 735WLCLF01:FY0046B.RO011 PUT POINT 735WLCLF01:FY0046B.RO01 TYPE FLOATDESC = WASH FACTOR NO2 MUD WASHERAUDITUPD = NO
1 PUT POINT 744STEAM:FA0032.PNT TYPE FLOAT# 31 CREATE POINT C201CP_STA:STATION.MINUTE1 PUT POINT C201CP_STA:STATION.MINUTE TYPE INTEGERDESC =AUDITUPD = NOCOLLECTOR = srhc03
DESC = "4 RECOVERY MCR"
AUDITUPD = NO
COLLECTOR = srhc03
# 2
1 CREATE POINT 735WLCLF01:FY0046B.RO01
1 PUT POINT 735WLCLF01:FY0046B.RO01 TYPE FLOAT
DESC = "WASH FACTOR NO2 MUD WASHER"
AUDITUPD = NO
# 3
1 CREATE POINT C201CP_STA:STATION.MINUTE
1 PUT POINT C201CP_STA:STATION.MINUTE TYPE INTEGER
DESC =
AUDITUPD = NO
COLLECTOR = srhc03

Code:
[SIZE=1]// Table7
let
    Source = Excel.CurrentWorkbook(){[Name="Table7"]}[Content],
    IF = Table.AddColumn(Source, "Custom", each if Text.Contains([Column1], "#") then [Column1] else null),
    FillD = Table.FillDown(IF,{"Custom"}),
    Group = Table.Group(FillD, {"Custom"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Group, "Custom.1", each Table.Column([Count],"Column1")),
    Extract = Table.TransformColumns(List, {"Custom.1", each Text.Combine(List.Transform(_, Text.From), "@"), type text}),
    SplitCount = Table.AddColumn(Extract, "Split Count", each List.Count(Text.Split([Custom.1],"@"))),
    MaxCount = List.Max(SplitCount[Split Count]),
    Split = Table.SplitColumn(Extract, "Custom.1", Splitter.SplitTextByAnyDelimiter({"@"}, QuoteStyle.Csv)),
    RC = Table.RemoveColumns(Split,{"Custom", "Count"})
in
    RC[/SIZE]
 
Upvote 0

Forum statistics

Threads
1,212,938
Messages
6,110,788
Members
448,297
Latest member
carmadgar

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
Back
Top