Need help with formula IF PARENT, then INDEX/MATCH for Dupe Flag

Sherry Fox

First, thanks in advance for looking at my problem. I am sure I am making this more difficult than it has to be.

New: Release Update Sheet

• Formula being added to this sheet in a Flag column
• Based on IF Parent Row
• This sheet has Parent AND Child rows
• Initial field for INDEX/MATCH is Release Column

Release List

• Duplicate (want to add this flag to the other sheet)
• "Same" column on this sheet is called Releases
• Desired return array is Duplicate? which is a Flag
• Formula on this sheet for the flag: =IF(COUNTIF(Releases:Releases, Releases@row) > 1, 1, 0)

Since the formula resides on a sheet with both Parent/Child rows, I want a blank cell to appear on all child rows.

=IF([Is Parent]@row=1,INDEX({Duplicates}, MATCH(Release@row, {Releases}, 0)),"")

Paul Newcome

No worries. Any time you have human intervention, you have the possibility for a process to not be followed properly.

What if you used a form for new entries and update requests for updates? Then people wouldn't need any access to the sheet (other than maybe viewer) and can't delete rows.

Paul Newcome

Are you able to provide screenshots for context?

Sherry Fox

@Paul Newcome ,

Sorry about that! I was multi-tasking, and I had a few fires to put out!

New: Release Update Sheet (left side)

New: Release Update Sheet (right side)

Release List

Paul Newcome

Is your INDEX/MATCH not working as expected?

Sherry Fox

@Paul Newcome ,

My formula does not seem to be working correctly. I have a helper column on the right that shows the release #s. This helper was a requirement, as the helper and child rows all have the same release #. Plus for each release, the child rows are identical, so this helper was needed. This is my formula, again for your reference. I have filtered by the Duplicate flag, there are 9 flags. But when I took a closer look at the helper on the right, only TWO were actually dupes from this list. The Release List shows 16 duplicates (8 Releases, each listed twice). I am not sure what I did wrong in my formula. I just noticed that when I turn the filter off it says "#NO MATCH" in the cell. Is that something I should just fix with IFERROR once I get the corrected formula figured out?

=IF([Is Parent]@row=1,INDEX({Duplicates}, MATCH(Release@row, {Releases}, 0)),"")

Paul Newcome

Isn't that what you are trying to do though? Show the duplicated parent rows?

Sherry Fox

@Paul Newcome ,

Yes, But the formula did not work right. It does show a set of 2 dupes, which I circled. The release List Sheet where I initially noticed the duplicates shows the following. I color-coded each set of dupes. In the image below, I have filtered by the duplicate column.

Paul Newcome

I still fail to see where the problem is. All of the dupes are flagged, and they are all parent rows.

What is the expected output?

Sherry Fox

@Paul Newcome ,

My formula that is incorrect is on the first sheet (New: Release Update Sheet)

But the sheet does not show all the duplicates. It should show all of these #s as duplicates (Release List).

If my formula in the first screenshot was working correctly, it would show the same duplicates in the 2nd screenshot. All gray rows in the first screenshot are parent rows, I have the sheet collapsed.

Paul Newcome

But the cells you have highlighted in the first screenshot are duplicates, and they are shown in the second screenshot. In fact even the cells that are not highlighted in the first screenshot are listed in the second screenshot.

Sherry Fox

@Paul Newcome ,

The lists should be identical. If the 2nd List (Release List) shows 16 numbers, 8 sets of duplicates, it should show the same way on the 1st sheet (New: Release Update Sheet). That first list belongs to another team, the 2nd sheet is mine for managing metrics and a dashboard, based on the data created by this other team 1st sheet (New: Release Update Sheet). If there ARE any dupes, I need to be able to show them where they are in THEIR data, so they can fix it.

Paul Newcome

SO there are no other rows in the first screenshot that are flagged?

Sherry Fox

@Paul Newcome ,

No, no other rows that ARE flagged, but there are rows that SHOULD be flagged. This would then make it identical to the 2nd screenshot of showing the dupes.

Paul Newcome

Ok. So there are rows in the sheet from the first screenshot that are not flagged but should be? Can you show some of those rows?

Sherry Fox

@Paul Newcome ,

Okay, after a substantial about of checking for dupes that I knew (due to 2nd screenshot), I just found out that the reason that the 2 sheets did not appear to be identical, is that some users, instead of changing the status to Hold or Cancelled, they were deleting the project from the list! And when it came off hold, they added it back on (same release #), as if it was a brand new project. And my workflows just advised me of a new Release. The formula does in fact work properly. I am sorry about this, the formula does not need to be fixed, it is some co-workers on another team.

Paul Newcome

Glad you were able to get it sorted.