Microsoft Excel Functions & Formulas

Use the TRANSPOSE and OR functions to

determine duplicate numbers in a list

Imagine you have a long list of numbers, and your task is to identify

all numbers that occur more than once. All of the values need to be

checked to see if they appear more than once by using the

TRANSPOSE and OR functions. Then all duplicated numbers have

to be shaded with the help of the COUNTIF function, which is con-

nected to conditional formatting.

4

To determine duplicate numbers in a list:

1. In columns A and B list numbers, some of which are

repeated at least once.

2. Select cell D2 and type the following array formula:

=OR(TRANSPOSE(A2:A11)=B2:B11).

3. Press <Ctrl+Shift+Enter> ...