Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to find and select duplicate rows in a range in Excel?

Sometimes, there are may be some duplicate records in your data range of a worksheet, and now you want to find or select the duplicate rows in the range as following screenshots shown. Of course you can find them one after one by checking for the rows. But this is not a good choice if there are hundreds of rows. Here, I will talk about some useful ways for you to deal with this task.

doc-find-duplicates-in-range1 -2 doc-find-duplicates-in-range2

Find duplicate rows in a range with formula

Find duplicate rows in a range with Conditional Formatting

Find and select / highlight duplicate rows in a range with Kutools for Excel


Select and highlight duplicate or unique values or rows in a range:

Kutools for Excel’s Select Duplicate & Unique Cells can help you quickly select and highlight the duplicate values, rows or unique values, rows at once in a range.

doc-find-duplicates-in-range16-16

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!



The following formula can help you to find the duplicate records, please do as this:

1. In the adjacent blank cell, cell D2 in this case, please enter the formula =IF(SUMPRODUCT(($A$2:$A$10=A2)*1,($B$2:$B$10=B2)*1,($C$2:$C$10=C2)*1)>1,"Duplicates","No duplicates"), see screenshot:

doc-find-duplicates-in-range3

2. Then press Enter key, if there are identical rows in this used range, it will display Duplicates, and if this row is unique, it will display No duplicates. See screenshot:

doc-find-duplicates-in-range4

3. And then select the cell D2, and drag the fill handle to the range that you want to contain this formula, and all the duplicate rows are found. See screenshot:

doc-find-duplicates-in-range5

Notes:

1. In the formula, $A$2:$A$10, $B$2:$B$10, $C$2:$C$10 indicate the range columns that you want to find the duplicate from. You can change them as you want. And A2, B2, C2 indicate the first cells in the each column of the data which needed to be applied this formula, you can change them as well.

2. The above formula is based on data in 3 columns, if there are 4 columns in your data range which needed to be found the duplicated values from the first row, and the formula will become this: =IF(SUMPRODUCT(($A$1:$A$10=A1)*1,($B$1:$B$10=B1)*1,($C$1:$C$10=C1)*1, ($D$1:$D$10=D1)*1)>1,"Duplicates","No duplicates").


If you can’t apply the formula correctly, please don’t worry, the Conditional Formatting utility also can help you to highlight the duplicate rows. Do with the following steps:

1. The first step you should to use the CONCATENATE function to combine all the data into one cell in each row. Type this formula: =CONCATENATE(A2,B2,C2) in cell D2, see screenshot:

doc-find-duplicates-in-range6

2. Then copy the formula down until the last row of data. See screenshot:

doc-find-duplicates-in-range7

3. Select the range that you want to find the duplicate rows including the formulas in column D, and then go to Home tab, and click Conditional Formatting > New Rule, see screenshot:

doc-find-duplicates-in-range8

4. In the New Formatting Rule dialog box, click Use a formula to determine which cells to format, and enter this formula =COUNTIF($D$2:$D$10,$D2)>1 into the below text box, see screenshot:

doc-find-duplicates-in-range9

Note: $D$2:$D$10, is the column D that you have combined the other column values.

5. Then click Format button, and then click Fill tab, choose one color that you need to highlight the duplicates.

doc-find-duplicates-in-range10

6. Click OK > OK to close the dialog boxes, and the duplicate rows are highlighted by the color you choose at once, see screenshot:

doc-find-duplicates-in-range11


The above methods are somewhat troublesome for you, so here, I can introduce you an easy and handy tool-Kutools for Excel, with its Select duplicate & unique cells utility, you can quickly select the duplicate rows or unique rows as you need.

Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 60 days

If you have installed Kutools for Excel, please do with following steps:

1. Click Kutools > Select > Select Duplicate & Unique Cells, see screenshot:

doc-find-duplicates-in-range12

2. In the Select duplicate & unique cells dialog box, click button1 button to select a range that you want to use, and then check Duplicates(Except 1st one) or All duplicates(Including 1st one) option under Rule section as you need, at last, you can specify a background color or font color for the duplicate rows under the Processing of results,see screenshot:

doc-find-duplicates-in-range13

3. Then click OK, and the duplicate rows are selected as following screenshots:

Select duplicates except first one Select duplicates including first one
doc-find-duplicates-in-range14 doc-find-duplicates-in-range15

Notes:

1. If you check Select entire rows option, the entire duplicate or unique rows will be selected.

2. If you check Case sensitive option, the text will be compared case sensitive.

Click to know more about this Select duplicate & unique cells function.

 Download and free trial Kutools for Excel Now !


Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!


Related articles:

How to filter unique records from selected column in Excel?

How to remove duplicates and replace with blank cells in Excel?


Recommended Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 71%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 82% Excel problems.

  •  Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
  •  Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  •  Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
  •  Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
  •  No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
  •  Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
  •  60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  •  Change the way you work now, and give you a better life immediately!

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!

 

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    SANDEEP KADAM · 2 years ago
    at the time of data entry can i stop duplicate entries in two columns
    example :- Table A Table B
    A 1
    B 2
    at the time data entry once A & 1 is coming than i don't enter this entry, can any formula & idea for this
  • To post as a guest, your comment is unpublished.
    SANDEEP KADAM · 2 years ago
    Can i find duplicate entries in two columns at time of data entry, that can i prevent duplicate entries in two columns
    example, Table A Table B
    A 1
    B 2
    at time of data entry next A and 1 i don't enter this entry, plz give any idea
  • To post as a guest, your comment is unpublished.
    Rahul Joshi · 3 years ago
    Hi,

    suppose i have data of 15 letters (alphanumeric), and i want it to be split in different columns.

    ex - ASDFGH11WE31005 this is the 15 letters code, i want it to be spilt in different columns like - AS DF GH 11 WE 3 1005
    pls suggest any shortcut or any formula to split it
  • To post as a guest, your comment is unpublished.
    Otsile · 3 years ago
    Good day,

    I am dealing with a similar problem but one that goes beyond just checking for duplicates and I am hoping you could shed some light in as to how to tackle it.

    To illustrate I will build up onto the problem you have already illustrated above and adding some more complexities to it. Suppose after checking for and finding duplicates (ie., product, order or quantity and price), you now want to select a shop from which you can now buy your products from (I assume the duplicates tells you what products in what amount you can buy at what prices, and there is that repeat of products, orders and prices). The Shop Name given is for the shop that actually has stock of items required.

    A B C D E F
    PRODUCT ORDER PRICE SHOP NAME Distance to Shop (miles) Shop chosen to buy from
    QQQQ 50 30 Shop A 15 ?
    PPPP 60 40 Shop A 15 ?
    XXXX 45 28 Shop B 30 ?
    QQQQ 50 30 Shop A 15 ?
    VVVV 65 42 Shop A 15 ?
    BBBB 48 21 Shop A 15 ?
    XXXX 45 28 Shop B 30 ?
    QQQQ 50 30 Shop B 30 ?
    MMMM 80 35 Shop B 30 ?

    Suppose you now know you can buy a product at the given quantities (order) and at the given prices, at either Shop A or Shop B or Shop B but now you want to decide on the shop to buy from. One of the factors used in the criteria for shop selection could be how far the shop is from your own location.

    Obviously for product XXXX the only shop to buy from, where the product is available is Shop B therefore the value to return under column E would always be Shop B.
    For product QQQQ, you would have the option of buying from either Shop A or Shop B. You now want Excel to have you choose a shop to buy from. You want to select the nearest shop.


    How would you go about using a formula to solve this one?


    Regards,


    Moses
  • To post as a guest, your comment is unpublished.
    Otsile · 3 years ago
    Suppose you have now ascertained that there are duplicates and the next thing is you want to check is if these duplicates (products, orders, and prices) can be obtained are from different shops. This is tantamount to introducing another column listingshops which actaully sell these products and you want to be able to select a shop to buy from based on another criteria not listed here (knowledge of shop location,distance to the shop, etc)

    Eg for Product QQQ, you can get same order at same price at both Shop A and Shop B and you wanna return either Shop A or Shop B based on that criteria you know. How would you tell Excel to return as a value either either Shop A or Shop B?

    PRODUCT ORDER PRICE SHOP NAME
    QQQQ 50 30 Shop A
    PPPP 60 40 Shop A
    XXXX 45 28 Shop B
    QQQQ 50 30 Shop A
    VVVV 65 42 Shop A
    BBBB 48 21 Shop A
    XXXX 45 28 Shop B
    QQQQ 50 30 Shop B
    MMMM 80 35 Shop B