Hi all,
I want to split the value in same cell into separate cell/row in excel.
but the value from other column need inherited to new row
not sure if my description is clear enough, i put the example as below.
is it possible to be done by UiPath activity?
Please account for the merged cells, any advice or solution would be appreciated!
Before:
goal:
Excel workbook:
Book1.xlsx (9.8 KB)
Hi Michael,
Create an empty datatable named dummydt. Read an Excel file, and for each row that contains a comma, split the row and add the resulting values to dummydt
can you provide a workflow example?
Hi @Michael_L
Please check the attached xaml and let me know if you have any queries further:
Sequence40.xaml (22.4 KB)
Excel Sheet2 is input and sheet3 is output:
Book1.xlsx (11.2 KB)
Regards
Thank you for taking your time to come up with this example.
Why is Number defined twice ?
To check whether the number variable contains any value or not. If there is no value then I have assigned the previous value stored in number variable. @Michael_L
Regards
Hi @Michael_L
If you find the solution for your query please do mark my post as solution to close the loop.
Regards
can you give a quick run-down of what is going on in your code?
Hi @Michael_L
->Reading the data table
->Creating a new data table with same headers by using .clone
->For each row in data table to iterate each row
->Splitting of values in Account column by using the comma(,) as seperator
->Assign Routing value to a variable
->Iterating through each value of account
->Assigng the number value into a variable
->Checking if the number value is empty or not and if it is empty the the previous value will be
assigned
->Add data row is used to add the extracted values to the respective columns
->Checing if the number value is contained any value or not and if there is value in that variable the
stored that value in new variable because to use that value for the next iteration which I have used
in the earlier steps.
->Checking the value is present in the array variable and if it is also having some value then I have
stored that also into a new variable for further actions because the same account should be
written for multiple lines rights.
->At last after the for each row in data table i have used the Write range work book to write the extracted data into a new sheet.
Regards
thank you so much, you are the best, worked as intended 
You’re welcome @Michael_L
Happy Automation!!
One more question:
I have two additional columns i want to bring in (Name & State) for input sheet:
want the result to output the same as “Number” column. Where would I make the changes in the code, i know it has to be somewhere in the multiple assign statement when looping through the array, but can’t figure it out, please advise
thank you I ended up figuring it out and I added the exact same syntax in the multiple assign statements, and actually combining the 4 if activities into one if activity with the true condition being a multiple assign, thanks!
Hello,
I am trying to practice more with unstructured data for future projects in excel. please see rows 4-22 for additional examples i created for unstructured data, i want to do the same you did (for row 1-3).
see attached for new input sheet:
Input.xlsx (10.0 KB)
screenshot:
note: for row 20-22 in column “Number”, the / is a delimiter that needs to be separated and tied to individual to “Account” and “Routing” columns, same thing what you did for row 1-3.
Please advise, thanks again for your help again
hello please see recent post, would really appreciate if you can assist again!
Sure I will provide the code tomorrow. @Michael_L
Regards
thank you so much for your help again, please provide it when you can!
Actually working on that will provide the solution once it’s done. @Michael_L
Regards
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.
