Removing duplicates by moving to another sheet

Kimbh

Hi, I have sheets A and B. Sheet A is set up that with a row that marks all duplicates as 2. I want to set up a formula that says, "If a column Duplicates is marked 2 and the customer information contains the word outdated, only move the first occurrence to Sheet B. " Leaving one copy of the duplicated on the sheet.

Thank you

Jeff Reisman

@Kimbh

Are your duplicates likely to be created on the same day?

If not, you might consider using The YEAR and YEARDAY functions to find the Julian style Created date, and a formula to then collect the lowest Julian number of the duplicate rows and set a cell value that triggers your automation to move the row out.

For a row created on January 18, 2022 this formula: =VALUE(YEAR(Created@row)+""+YEARDAY(Created@row))

would return 202218. For March 8, 2022, it would be 202267.

Get your first occurrence in a EarliestDate column:

=IF(Duplicates@row =2, MIN(COLLECT(Julian:Julian, CustomerInfo:CustomerInfo, CONTAINS("outdated", @cell))), "")

In English: If this row is a duplicate, collect the lowest value from the Julian column where customerInfo contains the word "outdated".

Then one more column, ToBeMoved:

=IF(EarliestDate@row < Julian@row, 1, "")

If the collected earliest julian date is lower than the julian date on this row, set this cell to 1.

Then automation rule to move the row when ToBeMoved changes to 1.

Jeff Reisman

Then one more column, ToBeMoved:

=IF(EarliestDate@row < Julian@row, 1, "")

If the collected earliest julian date is lower than the julian date on this row, set this cell to 1.

Then automation rule to move the row when ToBeMoved changes to 1.

Just reading this over with fresh eyes this morning. This should actually be set to where EarliestDate@row = Julian@row, that way you're deleting the first occurrence.

=IF(EarliestDate@row = Julian@row, 1, "")

IF the collected earliest Julian date is the same as the Julian date on this row, set the cell to 1.

Also - once you are sure all your formulas work, you might be able to combine some of these and remove an extraneous column...

=IF(IF(Duplicates@row =2, MIN(COLLECT(Julian:Julian, CustomerInfo:CustomerInfo, CONTAINS("outdated", @cell))), "") = Julian@row, 1, "")

This collects the lowest Julian date if the row matches the criteria, compares it to the Julian date, and if they are equal, sets the cell to 1 in order to trigger the automated move. This eliminates the need for the EarliestDate column. (I've tested all the above and they all work.)

Kimbh

Thank you