Duplicate report data, filtering on reports query

Jack Parry

I have a sheets that shows jobs due to go into production, there are however different stages to these jobs, therefore it has the same work order number for each of these stages.

When a job reaches a particular status it requires a review by the quality team, I've therefore created a report to show the quality team all the orders that require their review.

However, I want to remove the duplicates and only show the earliest planned start date.

I was thinking a duplicate formula and looking for a specific work centre, but it doesn't quite work.

=IF(AND([Order Number]@row <> "", OR([Work Center]@row = 40412, [Work Center]@row = 40413), COUNTIFS([Order Number]:[Order Number], [Order Number]@row) > 1), "Offline Required", "Offline Not Required")

Am I overthinking this? Is there an easier way? Maybe I can just create a column that looks at the work order and shows the earliest date?

TrevRCincySheetz

Create a helper checkbox column with the formula (with your column name adjustments)

=IF(Start@row = MIN(COLLECT(Start:Start, Order:Order, Order@row)), 1)

This will check a box if that date is the earliest date and then you can use can use that in your report builder filter to only show "checked" rows.

Jack Parry

Thanks @TrevRCincySheetz, this does bring me the earliest date, however if the date is the same for 2 of the stages it will still show me a duplicate, guess I need another helper column to further filter the data?

TrevRCincySheetz

Maybe just something unique to the row.. group by work centre or by stage (if that column is available)? Is there text in the item description that you can use in a filter using "contains" to either include or exclude a row?