Duplicate report data, filtering on reports query
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?
Answers
-
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.
-
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?
-
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?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!