Automation Workflow Action - MOVE ROWs (Duplicates) is somehow moving everything

Christine Cao

Business Need - I built in workflow automation to COPY row to this master sheet when status changes. That works as intended. However because I'm using COPY row, it then add "duplicate" rows onto my MASTER sheet. Next Step...

On the MASTER Project Portfolio sheet, I've built in series of background columns and formulas in my sheet to identify duplicate rows as they are automatically copied into the sheet using above automation workflow mentioned. The formulas I've created essentially identify duplicates, RANK them in order to just keep the latest entry and then CHECK BOX the column to "REMOVE"' - essentially moving those rows to another sheet. The formulas are working correctly.

The problem is when running the workflow. When I run the workflow manually, it's MOVING everything and also apparently wipes out my formulas. I don't know why. Everything seems correct but if someone can provide some insight.

You can see the REMOVE column has been checked. The fourth line should stay on the sheet when I run the workflow

This is the workflow set-up: Criteria is when the "REMOVE" column is "checked" as shown above. I've also tried it with TRIGGER: When Rows added or Changed, WHEN Rows are Changed. Same results - workflows moves everything even though the column REMOVE is NOT checked.

After Running the Workflow, you can see it removed everything including the 4th row which did NOT have the REMOVE checked. Why is that?

Paul Newcome

That is correct.

Paul Newcome

When you run an automation manually, it will ignore the trigger and only look at conditions. Since you do not have any conditions entered, it is going to grab all rows. Try adding a condition for the box to be checked before running it manually.

Christine Cao

@Paul Newcome, Hi Paul. Thank you for that additional input about it ignoring the trigger and looking at condition. In this case, when you suggest adding a condition before running manually, would I then add the "condition" such as REMOVE column is "Checked" - basically the same as my trigger?

Paul Newcome

That is correct.

Anna Butler

Hi, is there any way that the original post formulas can be shared? I find this very useful, since I'm having issues creating a master sheet from multiple other sheets and using the Add/Move row automation is creating duplicates.

Christine Cao

@Anna Butler - based on Paul's feedback, I added another condition (exact same as the trigger condition) in my workflow and that seems to resolve my original issue of EVERYTHING moved, not just the ones with the REMOVE checked when I run the workflow manually. Keep in mind, I created a few background columns to get the "REMOVE" checkbox to work properly and not have duplicate rows on my sheet.

Anna Butler

@Christine Cao , thanks for your response! What I'm wondering at the moment is the formulas I believe you are referencing here:

"Keep in mind, I created a few background columns to get the "REMOVE" checkbox to work properly and not have duplicate rows on my sheet."

Specifically, the formulas used in the "Duplicate IDs", "Rank" & "Remove" columns.

I totally understand the second automation/workflow you are using above; I'm struggling with the formulas in the sheet to get there :)

Christine Cao

@Anna Butler - sorry for delay. Been out. I followed instructions by someone on youtube on this use case cause I couldn't figure it out either and I'm not super savvy yet with formulas :) But this worked. I haven't found an easier way.

1. First, on your main sheet where you capture all your "copied" rows from another source sheets (using the first workflow), make sure you have a column for Row ID (auto generated # type column) as well as a column that captures a "unique" data value. In my case, my unique data value I have is called SOW # - unique for every project. (stands for Statement of Work #). This is what I use later in my formulas so needed to call that out.
2. There's several next steps here that I had to do to make this work. additional Background columns added:
3. Occurrences column formula: =COUNTIFS([SOW #]:[SOW #], [SOW #]@row, [SOW #]:[SOW #], NOT(ISBLANK(@cell)))
4. Duplicate Checkbox column: formula: =IF(COUNTIF([SOW #]:[SOW #], [SOW #]@row) > 1, 1, 0)
5. Duplicate IDs column formula: =JOIN(COLLECT([Row ID]:[Row ID], [SOW #]:[SOW #], [SOW #]@row, [Duplicate Checkbox]:[Duplicate Checkbox], 1), CHAR(10))
6. Rank column. Two options below.

To retain the first entry:

=IFERROR(RANKEQ([Row ID]@row, COLLECT([Row ID]:[Row D], [SOW #]:[SOW #], [SOW #]@row, [Duplicate Checkbox]:[Duplicate Checkbox], 1), 1), " -- ")

To remain the latest entry:

=IFERROR(RANKEQ([Row ID]@row, COLLECT([Row ID]:[Row ID], [SOW #]:[SOW #], [SOW #]@row, [Duplicate Checkbox]:[Duplicate Checkbox], 1)), " -- ")

7. Remove checkbox column formula =IF(Rank@row > 1, 1, 0)

Then set-up workflow that I created to "MOVE duplicate rows" (see above)

Hope this helps.

Christine