If you look at the MAX sections of this formula:

MAX((A2:A500<"")*ROW(A2:A500))

What this is saying is evaluate every row in the range
A2:A500 and tell me if it is not empty (""). This returns
and array of TRUE/FALSE. The part ROW(A2:A500)) simply
returns an array of {2;3;4;5;6....}. When you multiply the
2 arrays together, you get an array of numbers and 0's.
For example, let's say are just evaluating A2:A10, and it
has numbers in A2:A4 and A7. The resulting array would
look like:

={2;3;4;0;0;7;0;0;0}

What the user is actually trying to do is find the last
non-empty cell in column A. The array would like something
like:

{....333;334;335;336;0;0;0;0;0;0;0.....}

where it's obvious that the last non-empty cell is found
in row 336. Taking the MAX of this array returns 336.

Returning back to the main formula, it looks like this now
(assuming 336 is the last non-empty row):

=IF(MAX(COUNTIF(INDIRECT("A2:A"&(336)),INDIRECT("A 2:A"&
(336))))1,"Duplicates","No Duplicates")

What the user now has is a complete range "A2:A336" to
work with. It's only a text string at this point, so the
INDIRECT converts it to a usable range.

=IF(MAX(COUNTIF(A2:A336,A2:A336))1,"Duplicates"," No
Duplicates")

The COUNTIF basically does a count of every item in the
range against itself. To better explain, let's just assume
we're working with A2:A5 and it contains the values
3,2,3,1:

=COUNTIF(A2:A5,A2:A5)

returns

={2;1;2;1}

What this means is that it found the first "3" twice,
the "2" only once, the second "3" twice, and the "1" only
once. Because the "2" is duplicated, we see {2;1;2;1}. If
there were no duplicates, the array would be {1;1;1;1}.

So the user takes the MAX of the array that is returned.

=IF(MAX({2;1;2;1})1,"Duplicates","No Duplicates")

If that number is greater than 1 (meaning there are
duplicates), then the IF statement returns "Duplicates".
An array of {1;1;1;1;etc.} means there are no duplicates.

HTH
Jason
Atlanta, GA


-----Original Message-----
I found the formula below some place and I am trying to
understand the logic behind it. It finds duplicate
entries
in a column. If anyone would like to give me a hand in
understanding it I would appreciate it. I am a beginner
in
the fomula creation world and I think by understanding
other people's logic it will help me as I develope my own
formulas.

=IF(MAX(COUNTIF(INDIRECT("A2:A"&(MAX((A2:A500<"" )*ROW
(A2:A500)))),INDIRECT("A2:A"&(MAX((A2:A500<"")*R OW
(A2:A500))))))1,"Duplicates","No Duplicates")

Thanks
.