Microsoft Office Tutorials and References

In Depth Information

Duplicate values are determined by the value displayed in the cell — not necessarily the

value stored in the cell. For example, assume that two cells contain the same date. One

of the dates is formatted to display as 5/15/2013, and the other is formatted to display

as May 15, 2013. When removing duplicates, Excel considers these dates to be differ-

ent. Similarly, values that are formatted differently are considered to be different. So

$1,209.32 is not the same as 1209.32. Therefore, you might want to apply formatting to

entire columns to ensure that duplicate rows are not overlooked just because of a

formatting difference.

Identifying duplicate rows

If you would like to identify duplicate rows so you can examine them without automatically deleting them,

here's another method. Unlike the technique described in the previous section, this method looks at actual val-

ues, not formatted value.

Create a formula to the right of your data that concatenates each of the cells to the left. The following formulas

assume that the data is in columns A:F.

Enter this formula in cell G2:

=A2&B2&C2&D2&E2&F2

Add another formula in cell H2. This formula displays the number of times that a value in column G occurs.

=COUNTIF(G:G,G2)

Copy these formulas down the column for each row of your data.

Column H displays the number of occurrences of that row. Unduplicated rows will display 1. Duplicated rows

will display a number that corresponds to the number of times that row appears.

Figure 16-6 shows a simple example. If you don't care about a particular column, just omit it from the formula

in column G. For example, if you want to find duplicates regardless of the Status column, just omit D2 from the

concatenating formula.