Unique function

Dear all,

I have 5 excel files which contains list of orders, in each order I have the code of the order and status of the order as well.

Status (1) means the order is delivered.

Status (2) means the order is postponed.

Status (3) means the order is canceled

Status (4) means the customer didn't answer.

Now if the order's status is 2 or 4 it will be rewritten in the next file to re-contact with the customer and maybe the status will changes from 2 to 1 or 3 or 4 or stays 2 it depends of the customer's respond.

These 5 files are the files of the orders from Wednesday to Sunday.

Now what I want is to have all these files in one file and remove the duplicated orders because as I explained the order in the file of Sunday which its status 2 or 4 will be rewritten in the file of Monday with the same code and its status may be changed to 1 or 3 or others so in this way how can I use the unique tool and make sure that it takes the latest status.

Note that, as shown in the picture below each color represent one order. Follow the same for all files.

Accepted answers

BrandonB

I attached a screenshot below of what this can look like and included an example workflow. Using the file name, I parsed out the date using the date time tool. Then I sorted it descending. Finally, I used a unique tool on my customer ID. In your case it would be the code.

example workflow.png

example workflow.yxzp

All comments

BrandonB

If the files have the date within the name, it may be best to "Output File Name as Field" and parse the dates out. Then, you can sort by the date descending so the latest orders are up top, and finally do a unique tool on the code field. This way if there are any duplicates, they will be filtered out because they will be further down the data set after the sort occurs.

Feras95p

Hi @BrandonB,

Unfortunately, the dates are not accurate because if the status of a Wednesday's order is 2 at the date 20-05-2020 then it will be written again in the Thursday file with the same date and different status "depends on the customer's respond".

Any other idea?

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703