Row Report - Multiple Sheets - Remove Duplicate Rows

Lynnette

Good afternoon,

I am working on creating a row report using multiple sheets of mostly the same data. Starting with sheet one, at sheet 2 the row is 'moved' and more data is added and sheet 3 the row is copied and more data added. I have created the report pulling all the data in, and was able to find a filter successfully work with sheets 1 and 2. Adding sheet 3 brings in duplicate rows from sheet 2 with the additional data. I have not found a filter that allows to remove items from sheet 2 if they are on sheet 3. Are there any suggestions? I have a column that could be used as the 'trigger' to define. Is there a way to say "If sheet 3 Project ID = Project ID on sheet 2, remove sheet 2 row"

Paul Newcome

What is your copy row trigger?

Lynnette

What is your copy row trigger?

The trigger is a specific status, unfortnately, the status will continue to change along the process so I haven't found a way to use that as a capture to remove duplicates. I have other fields that could be used to remove the duplicate such as a Check Mark field used.

Paul Newcome

There may be a way to add to the report filter to essentially mimic the copy row trigger and condition(s) if any to remove the duplicated rows. Are you able to provide screenshots of the existing report filter(s) and the existing automation?

Lynnette

Duplicate Rows in Report:

Sheets: PIF, CPG, AFE

Sheet PIF will move a row to the CPG sheet when status fits criteria. No duplicates.

Sheet CPG will COPY row to AFE when status fits criteria. Creates duplicates.

The need is that when any CPG and AFE rows are duplicated, AFE row stays and CPG is filtered out.

Automation for copy of row:

These are the current filters on the row report from the three sheets:

There is a field on the AFE sheet that could be used as a trigger to what would be duplicated, but when trying to use it, I loose the information that is not duplicated.

Paul Newcome

You can create a new filter group that combines the sheet name and the status. Maybe something along the lines of OR for the outermost grouping and then AND for the filter group. Sheet Name is "AFE" and Status is not one of "abc".

To explain more visually… See in your last screenshot you have the two filters for Project Name grouped together with an And? This would be the same for the above with Sheet Name and Status.

Then the outermost And that combines all three, that would be "Or".

Lynnette

I have tried that now, unfortunately it did not work.

I even flipped the statuses, thinking I need the 'not' part correctly.

I also tried the same filter group using a check mark if it was on AFE instead of the status, and it didn't work either.

Paul Newcome

The status is not one of should be just that one status that triggers the copy row.