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
   • 
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.
   • 
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:
      • 
   2. Double click the mouse:
      •  
4. Select the cell D2, then on the MS Excel menu, select Data –> Filter
   • 
5. On the cell D1, click the dropdown button and only check the numbers greater than 1
   • 
6. Hit OK button, done. The duplicated rows are filtered out.
   • 

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:

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