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.
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:
OR
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
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
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 !
maybe possible but that’s out of my competency, sorry
You should open a new topic so that python experts might help or if you know some, you should call them on the topic
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