Question:

There is a list which consists of many rows. We want to quickly figour out which rows are duplicated with a specific field. For example, below is a list records the id info, how can we quickly filter out the duplicated ids?

sn id type
[0] 4ef168bb-40f2-4f28-93e2-89ed14159b33 string
[1] 06b98d4a-5a03-4e15-824e-913f018462af string
[2] c6482a72-885e-4457-9c43-0dfde5a03202 string
[3] bfe40da1-db9c-4a37-bcd5-a3009a5d94b4 string
[4] a1cc59a2-0eec-43c5-82cd-d392a503212a string
[5] 59feed38-5cfc-4257-8db8-072f04b42995 string
[6] aa7a5ce1-41b6-4e1d-8f76-1a9b548b8a51 string
[7] 038491e8-4e0f-482a-a57b-28444bb02962 string
[8] 8fcd978f-8c5b-4ebd-b2cd-195c130b6a4d string
[9] 8fcd978f-8c5b-4ebd-b2cd-195c130b6a4d string
[10] 3d16c1ae-3851-4bf5-b162-3253c8c3c49f string
[11] 7a54b8e1-ae6e-400f-8d5b-64ecc5fd8d74 string
[12] c6a5eb88-081f-401c-929e-a903bd0c5091 string

Solution:

  1. Paste the above data into MS Excel from cell A1, and add a column named count right to the last column of the above list
    • How to quickly filter out the duplicated values
  2. Input a formula “=COUNTIF($B$2:$B$35,B2)” into cell D2 (Just underneath the column name count). Pay attention to the dollar sign $, it is important to this formula!$ means absolute reference.
    • How to quickly filter out the duplicated values
  3. Put mouse on the right bottom of the cell D2’s border and double click the mouse when it turns to a cross sign.
    1. Notice the cross sign:
      • How to quickly filter out the duplicated values
    2. Double click the mouse:
      •  How to quickly filter out the duplicated values
  4. Select the cell D2, then on the MS Excel menu, select Data –> Filter
    • How to quickly filter out the duplicated values
  5. On the cell D1, click the dropdown button and only check the numbers greater than 1
    • How to quickly filter out the duplicated values
  6. Hit OK button, done. The duplicated rows are filtered out.
    • How to quickly filter out the duplicated values

Sample Download:

ScreenDuplicate.xlsx (9.67 kb)

An alternative solution:

If the data is in a database, for example, in a SQL Server, then you can select the duplicated records out by this SQL statement:

SELECT
    [id], COUNT([id]) AS repeatCount 
FROM
    idListTable
GROUP BY
    [id]
HAVING
    COUNT([id]) > 1 

Reference:

从库存表中查询所有拼箱信息(查询某字段重复的记录)