Removing old entries by comparing Modified time

Jeffrey Calderon

Hello, I am trying to find the index number of a matching number in column7. So if Column7 was 56 I would expect that this function

=MATCH([Column7]1, (COLLECT([Column7]:[Column7], [Row ID]:[Row ID], <>[Row ID]1)))

would return 4. Collect returns a list of numbers which are not blanks and excludes itself since it is excluding all the values with a Row ID of 1. Since only it has a Row ID of one. What I am actually getting is #NO MATCH.

Ultimately, I need a function which returns the index number of the duplicate Column7 value. The index number would be feed to an INDEX function to return the Modified time of the duplicate SO that I can compare Modified Date with the current cell.

older Modified time would be flagged in another cell and moved to another sheet automatically. This would be done by an automation that would look for a value that says "outdated" or something.

Paul Newcome

I'm not sure I follow. Why would the value of 56 in [Column7] return a 4? I see another "56" on row 6, but that still doesn't explain where 4 is coming from.

Are you just trying to flag the older date of the two rows containing 56?

=IF([Primary Column]@row < MAX(COLLECT([Primary Column]:[Primary Column], [Column7]:[Column7], [Column7]@row)), 1)

The above will flag any date earlier than the most recent date for any row that has the same data in [Column7].

Jeffrey Calderon

Oh wow, thanks for replying so fast. You were right it should return 6. Sorry I was quite tired when I was writing that. My aim is to know the actual row number of the matching column 7 number. If I know the row number of the duplicate column7 number, I can compare their modified time, flag the old ones and have automation move it to a "Recycling Bin" sheet.

The behavior of the formula I was using is baffling me.

Paul Newcome

@Jeffrey Calderon If you take a look at the formula I provided above, you will see that it will flag the older row(s).

First we COLLECT all of the date/time stamps in the [Primary Column] for all rows where [Column7] is the same. Then we say that if the date/time in the [Primary Column] on that row is less than the MAX, flag it.

This will save you the step of pulling the row number and will accomplish the same thing.