Removing duplicate rows from a report

Sandy Glassberg

Hi there,

I'd like to create a report from a sheet that has many duplicate rows. The point of the report is to list one example of every inventory item we have (some items we have singles, other items we have many). I'm already using a helper column "Duplicate Helper" with the following formula for another purpose:

=IF(COUNTIF([Full description]:[Full description], [Full description]@row) > 1, 1)

Is there another formula I should add in a separate column that indicates that it is the one from the many that needs to be pulled into the report?

I hope that makes sense! Thanks in advance!!!

Kelly Moore

Hey @Sandy Glassberg

You can find the first instance of a row with the help of a column that indicates row position (ex: [Row ID], Created Date - anything that tracks the relative position down (or up) a sheet). I will assume that you add new rows to the bottom of the sheet AND once a row is created, you are not changing it's position on the sheet (sliding the row up or down the sheet to a new position).

If you do not have the system autonumber column or Created Date, please add one of these. You will need to SAVE the sheet after adding.

I'll assume you added the autonumber column. If you added/use Created, edit the formula to replace [Row ID] with Created. Also be sure to edit the formula to use your actual column name for your part number. The formula below will flag duplicates but will leave the first instance of a row unchecked (if you use =1 instead of greater than 1 it will check the first instance and leave the duplicates unchecked. If you have many duplicates it may be simpler to flag the first instance.)

=IF(COUNTIFS([Full description]:[Full description], [Full description]@row, [Row ID]:[Row ID], @cell<=[Row ID]@row)>1,1)

Filtering on this column (to either include or exclude, depending on if you flagged your duplicates or first instances) should give you the list you desire.

Does this work for you?

Kelly

Sandy Glassberg

Thank you, Kelly! That appears to have worked. However, here is an error message that has popped up when I try to save the sheet with the new columns and formula. This sheet currently has about 4,000 rows that this column formula would be calculating. So I deleted a couple of other columns with formulas that I've decided are no longer relevant, but this error still occurs. Do you have any further suggestions? Thanks again!

Kelly Moore

Hey @Sandy Glassberg

It sounds like you were already on the verge of too many references. Are you doing lookups against your Part Numbers and Descriptions?