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:
- 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
- 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.
- Put mouse on the right bottom of the cell D2’s border and double click the mouse when it turns to a cross sign.
- Select the cell D2, then on the MS Excel menu, select Data –> Filter
- On the cell D1, click the dropdown button and only check the numbers greater than 1
- 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