example :- Table A Table B
at the time data entry once A & 1 is coming than i don't enter this entry, can any formula & idea for this
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.
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.
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:
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:
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:
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:
2. Then copy the formula down until the last row of data. See screenshot:
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:
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:
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.
6. Click OK > OK to close the dialog boxes, and the duplicate rows are highlighted by the color you choose at once, see screenshot:
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:
2. In the Select duplicate & unique cells dialog box, click 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:
3. Then click OK, and the duplicate rows are selected as following screenshots:
|Select duplicates except first one||Select duplicates including first one|
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.