A formula for filtering out duplicates

Jack Parry

Hi, I have a Production Schedule sheet that is inputted from our ERP system.

I'm trying to make a report that shows all the orders not ready to go into production for the next 2 weeks.

My issue is that the orders go through 2 stages and therefore have different start dates and show up as duplicates on my report.

1st stage is Offline, which can be split into 2 sub stages. (40412 + 40413)

2nd stage is general production. (40201)

I've resolved the 1st issue, by using the following formula to collect the 1st stage of the order. Giving me the earliest start date.

=IF([Planned Start Date]@row = MIN(COLLECT([Planned Start Date]:[Planned Start Date], [Order Number]:[Order Number], [Order Number]@row)), 1)

This allows me to filter out the 2nd stage of the report.

I'm trying to come up with a helper column that allows me to check if a duplicate and it assigned to both 40412 and 40413. My thinking being that I could then filter out one of the stages from my report.

My potential issue is that it may be possible that an order isn't a duplicate and is only assigned to either 40412/40413 and is filtered out by mistake.

A manual option I've come up with is just a helper column with a checkbox 'Order to hide' and the filter on that column for unchecked orders.

Would love to automate this, if I could..

any advice would be great, thanks :)

MichaelTCA

Hello @Jack Parry

I use the COUNTIF function to check for duplicates.

This is because I move rows around through automations and don't want duplicate data, so it's generally used as a condition to move a row or not.

I have a separate sheet as an archive as well for orders that are completed. You can set up an automation to move the row to an archive if the data is no longer needed or already contained in another location.

Unfortunately, you can't delete a whole row through an automation without using an app like Data Shuttle. Even then, the filters for Data Shuttle depend on the source data from the ERP and not the data from the sheet.