Let's look a little more closely at the formula we've used: This indicates that our formula is working, since it has found each row which has a duplicate somewhere in the table. If you look more closely, you'll see that there are actually only two rows which are duplicated (Desktop | Monitor, and Server | Backup Tapes). The results of this are shown here:Īs you can see, there are four rows shown here where the Duplicate value is greater than one. This to be entered into D2 in our table and then copied and pasted down the table. We'll start by adding a new column to our table, into which we will enter this formula. The first step in identifying duplicate rows is to write a formula using COUNTIFS to count how often each row is repeated. Use COUNTIFS to find rows that contain duplicate values Our example will require that we use two range/criteria pairs, but Excel allows you to have up to 127 pairs in a single formula. =COUNTIFS(criteria_range1, criteria1, …)Īs shown here, you can define multiple ranges that the COUNTIFS function should look at, and the criteria it should use when deciding whether to count each row as part of its calculation. The screenshots and examples in this lesson have been produced using Excel 2013, but this method will work in all three versions. Note that the COUNTIFS function was introduced in Excel 2007, so the method we're going to look at in this lesson will only work if you have Excel 2007, Excel 2010 or Excel 2013.
EXCEL FIND DUPLICATE VALUES COLUMNS HOW TO
COUNTIF allows you to use just one criteria, whereas COUNTIFS allows you to use multiple criteria. You can find out more about COUNTIF in our lesson on how to Use COUNTIF to count the cells in a range that match certain values. It is closely related to the COUNTIF function. The COUNTIFS syntaxĬOUNTIFS is a function that allows you to count only those rows in a spreadsheet where certain criteria are met. For example, a Desktop Monitor has been ordered twice, as have Backup Tapes for the Server. A sample of this data can be seen in the following picture:Īs you can see in this example, there are several examples where the Product and Part Ordered are repeated.
We need to identify how often each spare part is being ordered for each machine. Our data table lists orders of spare parts for several computers. This lesson uses an example of a product order table. Excel offers a number of ways to find rows that contain duplicate values. However, most of them focus on finding rows where the value in just one column is duplicated. In this lesson, we look at how to use the COUNTIFS function to find rows with duplicate entries in two or more columns. We then use the COUNTIFS function in combination with Excel's Conditional Formatting feature to highlight duplicate and triplicate rows.
0 kommentar(er)
