• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to split Cell Data in multiple rows with proper column arrangement?

Dear All,

I have a file that contain data with 1 cell have mix shade no in1 cell that require split in rows, based on number of units as associate with that shade no.

i also attache my file in which 91) original data 920 sample taken from original as small data for test (3) require result sheet

(1) Original

1-original.png

(2) Taken Sample as small data from original for test work

2-sample as small data.png


(3) Require result sheet screen shot


3-REQUIRED.png

Also attached An excel file for test.

If further clarification require please mention.

Hope there are some solution found & any help will be much appreciated.

Regards,

Chirag Raval
 

Attachments

  • HOW TO DISTRIBUTE CELL'S DATA IN ROWS.xlsx
    67 KB · Views: 4
I can't remember if you have access to Get & Transform (PowerQuery) or not... Do you?

Dear Sir @Chihiro ,

Thanks for your response.
I already installed free tools Power Query
But actually don't know how to use it.
Just one experiment it with another matter in past that's it.

I am really excited to know how to use it
In my current data situation.

Hope your help sir.

Regards,

Chirag Raval
 
Dear All,

Above Attached file is order punching report, that 1 Cell contain Shade No & Number of Units of that shade.

If 1 shade have ordered 3 units, then it should 3 times repeat 1 units 3 times (3 rows) with all related column data so data base create for further process.

if my this thread found solution, it will used for compare with dispatch detail as "Is it dispatched number of units of that shade as per our ordered? " how many & which dispatched & which is pending?

& dispatch detail from manufacturer is as rows per ordered unit , means if you punched 3 units in order As for Example 65/3, dispatch detail from manufacturer provided to us as below format with all related column data
no blank cell anywhere in detail, database as complete square, without any Hole (Blank Cell) in database.

Row No 1- 65/1
Row No 2 -65/1
Row No 3 -65/1

I Think, splitting , Repeating till end of shades in one cell, Etc can handle by VBA. how can Power query wok here?

data i 1 cell.png

Regards,

Chirag Raval
 
See attached. You can follow steps in Applied steps pane.

This is bit more involved than usual transformation, so follow steps carefully.

I did quick validation on result (# of rows = SUM(Total Units) & Sum(Value) is same for original and result). But check through and see if this is what you needed.

If I have time later, I'll do a write up on steps and concepts used.
 

Attachments

  • HOW TO DISTRIBUTE CELL'S DATA IN ROWS.xlsx
    182.6 KB · Views: 6
Dear Sir,

Amazing, First time i see the Power of "PowerQuery"...you just done without any VBA Macro....in 21 steps in PowerQuery..!!! & its work as needed.!!

QUERY TABLE.png

When Every steps seen in PowerQuery's Formula Bar
steps in formula bar of power query.png

Result

result.png
When double click on your every steps , i stop at 4th step,

error.png

What happened i don't know but ..try to understand this things.

Means we must expand our criteria of work in Excel as must to know VBA & Power Query , Power Pivote also....

Hope there are that you have some time to little try to describe that how that happen.

Please suggest where we can learn this "Power Query" from Basic.

Regards,
CHirag Raval
 
Last edited:
Sorry, but I'm busy at work today and won't have time to do write up.

But issue is in your #"Split Column by Delimiter" step.

Look in the file I uploaded. You'll see that column names were changed in that step.
Code:
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Quality#(cr)#(lf)No.", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"BLND", "QLTY"}),

Otherwise, you'll need to use system generated column name in subsequent steps.
 
Ok, here's my process explained.

1. First load the data to PQ and go into editor.

2. Right click and split [Quality No.] using "/" as delimiter. Immediately go to formula bar, and change respective column names to "BLND" and "QLTY" in the formula. Change both column data type to Text.
Code:
= Table.SplitColumn(#"Changed Type", "Quality#(cr)#(lf)No.", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"BLND", "QLTY"})
3. Initially I was going to split [Shade / Units] column in stages, but then I realized number of period used in separating each value wasn't consistent. So I went into advanced mode and split using "." and put result into each row.
Code:
= Table.ExpandListColumn(Table.TransformColumns(#"Changed Type1", {{"Shade / Units", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Shade / Units")
4. This creates null rows intermixed with desired value. So filtered out null from the column.
Code:
= Table.SelectRows(#"Changed Type2", each [#"Shade / Units"] <> null and [#"Shade / Units"] <> "")
5. Then final split is applied using "/". And column names changed in the formula bar ("SHD" & "Units").
Code:
= Table.SplitColumn(#"Filtered Rows", "Shade / Units", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"SHD", "Units"})
6. [QLTY] was concatenated with [SHD]. But since [SHD] is numeric column... It is first converted to text using Number.ToText. Then added preceding zeros using Text.PadStart.
Code:
= Table.AddColumn(#"Changed Type3", "QLTY SHD", each [QLTY] & "-" &  Text.PadStart(Number.ToText([SHD]),3,"0"))
7. Looking at your sample result, you needed each unit split into row. This is accomplished using List.Generate() iterator function. Starting from Unit Value, step -1 while unit is greater than 0. For each iteration return value 1. The result is expanded.
Code:
= Table.AddColumn(#"Changed Type4", "Custom", each List.Generate(()=>[Units], each _ > 0, each _ - 1, each 1))
8. Renamed columns to easily identify columns in subsequent steps.
9. [#"Orig Qty."] was divided by [Orig Unit] to obtain Qty per unit.
Code:
= Table.AddColumn(#"Renamed Columns", "Qty.", each [#"Orig Qty."] / [Orig Unit])
10. [Rate] multiplied by [Qty.] (calculated column from step 9), to obtain Value.
Code:
= Table.AddColumn(#"Added Custom2", "Value", each [#"Qty."] * [Rate])
11. Removed and reordered columns to desired result.
 
Dear Sir @Chihiro

its raise error on step no 4,


4. This creates null rows intermixed with desired value. So filtered out null from the column.
= Table.SelectRows(#"Changed Type2", each [#"Shade / Units"] <> null and [#"Shade / Units"]


ERROR.png
Where is Misspell?

Regards,

Chirag Raval
 
Dear Sir @Chihiro

Many Thanks ...please explain that I follow your attached file's steps OR
post no 8's steps?

i already copy your entire content of function in advance query editor & apply it to any this type of data & successfully complete the process. thats give perfact result.

but if i want to step by step go..then i follow files steps or your post no 8's post?

function.png
Please Help

Regards,

Chirag Raval
 
Respected Sir @Chihiro ,

Till now, I tremendous try to steal some time from my office work, to study your marvelous effort.
I actually want to learn this method & till this thread i know little that power query works basically
As Column oriented, regardless of rows there, & its also work amazing if we take require steps.

I try my best to understand its terminology that how its works, what can we do with it etc.

Can you say something about its core usefulness that i. Which situation we can take advantage of it?
That is because when this thread start, i think in my mind that solution will get through vba, but
You sudden throw power query steps thats really feel new & very important surprise.
& Its definetly open the gate towards little known powerfull tool of excel.
Can you provide some usefull links that we can learn from basics about this Power Query?

I always be thankfull to introduce another
Path of possibility that what can we also do with our excel data.

I will be revert after study & practice on your valuable efforts.

Thanks again.

Regards,

Chirag Raval
 
Last edited:
PowerQuery is one of best addition to Excel in recent years. Along with PowerPivot.

Think of PowerQuery as data transformation tool (ETL, Extract, Transform, Load).

You need to connect to various data source? PQ can connect to many things (Zipped file content, Hosted file, Json response, API, XML, Databases, Excel file etc).

Need to flatten data structure, merge or append data (i.e. join/union data)? PQ is the go to tool.

Once data is transformed, it can be loaded back to sheet or loaded into data model for use in pivot table.

PowerPivot is the layer that adds more analysis capability (data modeling and relationship based analysis). With powerful DAX expression language.

Combined, these tool gives powerful BI capability to Excel.
 
Dear Sir @Chihiro

Thank you very much ..now i really feel i am ittle ant wanting ride on The sea of knowlege.
I think many usefull concepts start become merging in excel. excel becoming vast huge possibility
Start to linking with excel & pray to god for new generation may be grasp that without struggling much to understand
from introduction To excel to this edge.

I also think very powerfull but little used Acess's relationship concept & many more concepts as described by you
Generate much sofisticated Excel to the world . Now time to must become vast from vba....there are whole & various
Database Conepts merging with Excel thats feel surprise, feear of less knowledge,& also thrill...like we see Huge concepts
Emerging in excel.

Thank you very much for your kind effort to lighten on power query like a kind saint
Blessing on child like pupils without want any rewards ,like only Saints just want to kindly give.

Regards,

Chirag Raval
 
Back
Top