How to repeat table data?

How does the table data repeat? The data in the table is sometimes abnormal--knowing that the company does not have the same name, but there are duplicate names; it was the only ID number that appeared on the heads of two of people. Repetition sometimes occurs in a table, needs to be identified, and sometimes you need to find out what data is not in a single table. So how do you quickly find duplicate items? In fact, with two small functions if and countif can be completely done.

Take this staff table below, there are some duplicate records, need to find out quickly.

Now, use a small formula to fix it. Create a secondary column B, which holds the label for the duplicate of the judgment, and if there is a repetition, mark "repeat", and do not mark anything without repetition. Enter the formula in cell B2 and return:

=if (COUNTIF (A:A,A2) >1, "Repeat", "")

You can then automatically populate each row by dragging the small box fill handle button (or double-click the fill handle) in the lower-right corner of the B2 cell. After that, all duplicate data lines show "Get Away".

To make the duplicated items look more conspicuous, we can also add color to the duplicated items. Set through the start → conditional formatting → highlight cell rule → repeat value path.

In the Duplicate Value dialog box, set a fill color and text color for the repeat value, and then determine.

Then we see that all the duplicated items are marked with the specified color.

If there is a duplicate ID number in the registration form, how do I get it? Please look at the table below, the number of dense, you can suddenly determine a few repeat it?

In fact, we just need to use another formula in cell B2:

=if (COUNTIF (a:a,a2& "*") >1, "Repeat", "" ")

Using the fill handle to apply the formula to subsequent lines automatically gives the duplicate ID's location.