Get a column that contain all unique id's from one sheet to another

davital

Hello, I have a master sheet that contains a lot of id's with duplicates.

I want to pull automatically only id's that are appear once in the master sheet in order to do a counter of how many times each id's appears in the master sheet.

I try to find a formula in the form but I did not find a transparent and clear answer to this problem. Many users have complained that there is no convenient way to do this.

Please guide me😓

Dor

Davidp

https://community.smartsheet.com/discussion/102490/get-a-column-that-contain-all-unique-ids-from-one-sheet-to-another

Your master sheet makes sense and I understand you are trying to determine how many times a specific data point is counted.

When I do this, I typically do it on the main sheet itself with a "helper" column and then write a count if statement. A few examples below reference the "ID#" column.

You can see in the ID# column there are 8 rows that have repeating ID numbers, in some cases non duplicated. If you reference the Primary Column Count @ row that tells you how many times the ID# @ row appears in the ID# column or ID#:ID# cell range. Looking at this column, you can easily see where the duplicates are at, any field greater than 1. If 1 then that ID# is NOT duplicated.

The ID# count is looking at ID#:ID# and telling me how many "Distinct" ID numbers there are. This is counting all ID options in the ID# column and saying there are 5 distinct records in that field. It reviews the entire column and then only counts the number of times all options appear omitting duplicates beyond the initial appearance in that count.

For fun I wrote an if statement to tell me if a record is a duplicate or not identified in the duplicate column. This function is saying any ID# that appears more than once in the ID# giving a true or false result based on the "if portion of the if statement identified below.

Primary Column Count @ Row: =IF(ISBLANK([ID#]@row), "", COUNTIF([ID#]:[ID#], [ID#]@row))

ID # Count: =COUNT(DISTINCT([ID#]:[ID#]))

Duplicates: =IF(COUNTIF([ID#]:[ID#], [ID#]@row) > 1, "True", "False")

I"m not entirely sure I understand how you are trying to pull the "distinct" records to a second sheet and then count them.