How to Identify Duplicate Job Numbers on Multiple Sheets?

Pnda

I manage 30 production projects across three Smartsheet sheets: one main sheet (Project Management) and two spin-off sheets (for Ordering and Install Certification tracking after a project ships). All sheets use a unique 4-digit job number for each project.

What’s a formula or function I can use to quickly identify if any of the two spin-off sheets have duplicate job number entries? I update these sheets three times a week after production hours, and I want to ensure all jobs are entered and there are no duplicates.

SSFeatures

Hi @Pnda,

In your spin-off sheets, you can create a column named ID Count which counts the number of times that each ID appears within the sheet. This will be a helper column that you can right click and hide, if you wish.

You can use this formula:

=COUNT(COLLECT(ID:ID, ID:ID, ID@row)) 

Notice how Task 1 has the value 2.

You can do this for both spin-off sheets. Next, you can create a Report that includes the data from both spin-off sheets and filters for any rows where ID Count > 1.

This will give you a quick way to see all of the IDs that are duplicates.

To ensure that your spin-off sheets contain the same number of IDs as your primary sheet, you can use another report or reuse this same report. This report you can use the Summary feature to count the number of IDs. In this example, notice how my Sheet A has 6 IDs, whereas my Sheet B has 4 IDs but 2 of them are duplicates.

I hope this helps! Let me know if you have any other questions.