Formula logic explaination

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

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<>"")*ROW
(A2:A500))))))>1,"Duplicates","No Duplicates")

Thanks
 
the formula boils down to

=if(max(Countif(range,range))>1,"Duplicates,"No Duplicates")

Countif(range,range) would return an array like {1;4;1;1;1;1;1;2;4;4;4;2}

for a column with data like:

1
2
3
4
5
6
7
8
2
2
2
8


Max finds the max number in the array. If it is greater than 1, then there
are duplicates. Otherwise, there are not.

The other part of the formula is just finding the max row with a value so
the range dynamically determines the range of cells to check.
 
If you are a beginner in formula creation; trust me, this is not the formula
to start with.

The formula first finds the last row with an entry in the range A2:A500,
compares every entry to every other entry from row 2 to the last row with an
entry in column A found by the preceding test, determines whether or not any
entry is duplicated, and yields the answer.
 
In the Countif help it shows Countif (range,criteria), you
mention Countif (range,range) is there a difference here?

Also, I'm not understanding how the array returned by the
Countif is actually generated, what do the numbers in the
array represent?

Thanks for the time spent helping. . .
 
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("A2: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
 
The criteria can be a range, and in this case it is a multi-cell range with
the values to be counted.

The array is generated by counting each entry in the list in turn, so in the
example, the first 1 has one instance, the 2 has 4 instances, the 3 has 1, 4
has 1, etc. returning {1;4;1;1;1;1;1;2;4;4;4;2}

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
if it only different in that we are giving it a multicell contiguous range
of criteria rather than a single cell. This happens to be the same range as
the first argument, so we are counting the number of times each value in
each cell in range is found in the entire range

Look at the first two cell
A1: 1
A2: 2

the first element in the array is the number of times 1 (from A1) occurs in
the range. It occurs once, so the array contains a 1 as the first element.

the second element in the array is the number of times 2 (from A2) occurs in
the range. It occurs 4 times, so the array contains a 4 as the first
element. If 2 occurs 4 times, then there should be 3 other cells that
contain a 2 and in the array at the corresponding location you will find a 4
reflected as well.

You can reproduce the array in cells by going to b1 and entering

=Countif($A$1:$A$12,A1) and drag fill down to column b12

this is the way countif works traditionally (and as shown in help).
Apparently it was discovered that it can accept a contiguous range as the
second argument and return an array. Not all capabilities are covered in
Help. some are discovered using experimentation.

Then again, if the behavior is not written down, there should be no
restriction on Microsoft changing the behavior in a later version.
 
Back
Top