Splitting Excel in various columns


I have this excel file and I have rows of same “ID” as shown below:

To put it simple, I want every 2 rows of same ID to be on the same row:

I think it is a bit like candy crush. We want to find matching rows based on column “ID” and then paste it in a single row. We do the same for every row below.

Is that possible?
Thanks for helping!
UIPath_Excel_Scratch (1).xlsx (120.7 KB)

Hi @Yudhisteer_Chintaram1 !
Would you mind confirming us that we only find 2 occurences of the same ID ?

1 Like

Hi @Hiba_B ,

Unfortunately no. As shown in the first image, wa can have several instances of same ID. However, we need to go from first row to bottom and concatanate same TWO IDs.

For ex. ID “def” occur a third time in the first image. If there would be a fourth time, then they would concatanate.

Alright, so at each occurence that are different from previous values, 2 more columns Time Out X + Weight Out X ? (because you can’t have same name headers)
Something like this if i understand the business rule:

we indeed have several occurrences of the ID, but fortunately we only have TWO key/values with like same ID same time in but only TWO different weight in

Ah no actually. The inverse.
If we have this:


Then it become like this:

Is this one possible?

Aaaaah alright more clear now, so it always works at a pair data linked by the id, and we need to take into account the order.
Yes it’s possible, gonna try something and i’ll let you know

1 Like

Yes, always at a pair data linked by the id.
Yes, I think the order will help you.

Thanks for trying @Hiba_B . Let me know!

1 Like

Alright here is a suggestion:
From_separate_lines_to_shortned_ones.xaml (24.8 KB)
The output is supposed to be in the same file, with a created “Out” sheet.

Let us know if the result is not what is expected !

1 Like

Wow @Hiba_B . It works just fine!
Would you mind explaining me your code please?

I wanted to know if it is possible to convert your flow to python?
Can we do it?

Sure !
First, we build a datatable that contains the 5 wanted columns at the output with the wanted order (ID, Time In, Time Out, Weight In, Weight Out), called output_DT.
Then we read the excel file, called DT
Then foreach row in DT, we use a filter on a temporary datatable that has the value of DT.
→ if the temporary datatable never has seen the ID, then we create an empty row where we fill in 3 data
→ if the temporary datatable already has seen the ID. The problem is output_dt already has filled only the columns A, B and D, but we need a way to know if the column C or E is filled in to ask the robot to fill it.

  • to do so, the robot has to find a case where the ID already exist + column E is empty. If the condition is met, a break happens and a boolean is used to not add a row but only fill in E
  • if the robot does not find the case where ID exist + column E is empty, then the boolean is set to true, so the robot create a new row to fill in the information

Let me know if I am still unclear on some elements !


You mean converting uipath studio code into python ?

I meant to write a python code to do the same thing the robot is doing.
Is that feasible?

maybe possible but that’s out of my competency, sorry :confused:
You should open a new topic so that python experts might help :grin: or if you know some, you should call them on the topic

1 Like

Okay sure. :slight_smile: I thought of asking :smiley: Thanks anyway! It works like a charm!

1 Like

Great ! Don’t forget to mark the topic as solved so it benefits the others that are having the same issue if you don’t plan on calling python expert in the topic :smile:

1 Like

Yes sure. I already marked your xaml file as the solution :smiley:

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.