Removing Duplicates from a Report

brendan.mccue

Hello,

I have a sheet with many line items, some of which share some columns that have duplicitous information. I would like to pull a report from that sheet to display certain information on a dashboard and, ideally, within the report, run a conditional format to remove the duplicate line items.

I am also reading up on the INDEX and COLLECT functions as it appears they may allow me to "summarize" information into another sheet which I can then run a report on for use in my dashboard. But I am having trouble understanding and implementing the INDEX and COLLECT functions.

Paul Newcome

I would suggest a helper column on the source sheet with a formula to flag the duplicates then a filter in the report to only show rows where the duplicates column is not flagged.

brendan.mccue

Hi Paul,

It looks like I have that formula working and checking a check box for any "Job Code" that is duplicated.

=IF(COUNTIF([Job Code]:[Job Code], [Job Code]@row) > 1, 1)

Although this is not getting me to where I want to be. I want my report to pull one of each of the duplicates but since they are duplicates, the formula checks the check box on all of them. Therefore, my report won't be able to distinguish against the first unique job code and its duplicates.

Thanks,

Brendan

Paul Newcome

Would you want the most recent or the original entry in your report?

brendan.mccue

Either would work, I just need to keep one entry of each duplicate.

Paul Newcome

Ok. In that case you can insert an auto-number column with no special formatting and modify your formula like so:

=IF([Auto-Number]@row = MIN(COLLECT([Auto-Number]:[Auto-Number], [Job Code]:[Job Code], @cell = [Job Code]@row)), 1)

brendan.mccue

Hi Paul,

It looks like the use of the auto numbering column and the check box column with the formula you proposed is working. To confirm, is the formula telling the check box column to not check off the highest auto number there is for that duplicate set?

Thanks,

Brendan

Paul Newcome

The formula is checking the box on the first row entered with that Job Code. If you wanted the most recent, you would swap the MIN function for a MAX function.

brendan.mccue

Got it, thanks for your help Paul!