Count occurances of equal values in two col. - array within SUMPRO

[John_J]

I have three named ranges: Name, Status, Name_validation_list. The two first
are about 1000 rows long. I need to get a warning if someone tries to enter
two identical occurances or combinations - Here: "C" and "Stop" are occuring
more than one time. The warning may perhaps be built from the SUMPRODUCT
returning lager than 1. This is quite easily accomplished with an IF formula.
I want to return the warning in one cell only. Example: $C$1. The criterion
in the "Status" range is fixed: "Stop", but the criterion in the "Name" range
is not. Therefore I have to test the status "Stop" to any of the values in
the named range "Name_validation_list".

A formula testing only one argument is easy and would look like this:
=SUMPRODUCT((Name="C")*(Status="Stop")) Returning "2".
However I need to test against any value: A, B, C, D, ......etc. - as in the
named range Name_validation_list. I believe this could be done through an
array formula inside the SUMPRODUCT formula, but I can't figure it out. The
Name_validation_list is about 50 rows long, so doing one by one would be to
difficult. I also don't want to add another column to assist the calculation.

Name Status
X Started
B Started
C Stop
C Stop
D Stop



Name_validation_list
A
B
C
D
E
F

 

[Spiky]

I don't see how you are going to get one formula to check 1000 rows
and return error answers in one cell. Not without a LOT of space to
read that cell. What if 20 are 2-stop errors? Do you list them all in
one cell?

If you copy a formula down all 1000 cells, you can sort or filter on
the 3rd column and put all the errors together for quick deletion or
whatever. Or possibly skip the 3rd column and use VBA to do it
directly.

=IF(ISNA(MATCH(A1,Name_validation_list,0)),"Not
Validated",IF(SUMPRODUCT((Name=A1)*(Status="Stop"))>1,"Entered
Twice",""))

 

[John_J]

Hi Spiky,
It should be possible to get the result in one cell because I only need to
know if any of the combinations are TRUE or 1. It doesn't matter which one or
how many.

Yes it is possible to add one col. However I believe it should be possible
to run this calculation with an array formula. The problem is just how to set
it up. I need to learn how to put an array formula inside a SUMPRODUCT
formula.
--
Thanks
John_J


Spiky skrev:

 

[Spiky]

Once again, the only way I can see to do this is with a UDF I've
installed, morefunc.

{=COUNTA(A1:A1000)-COUNTDIFF(A1:A1000&B1:B1000,,)}

Array entered.

If the answer is greater than zero, you have a duplicate. If the
answer is less than zero, you probably forgot to enter as array.

 

[Spiky]

Oops, forgot about the validation requirement. That I'm not sure how
to add.

 

[Domenic]

Let's assume that the data contains the following...

Name Status
X Started
X Started
B Started
B Started
C Stop
C Stop
C Stop
D Stop

If you'd like the formula to return 2, representing the number of times
a combination occurs more than once and where the corresponding name
occurs in Name_validation_list, try the following formula that needs to
be confirmed with CONTROL+SHIFT+ENTER...

=SUM(IF(FREQUENCY(IF(ISNUMBER(MATCH(Name,Name_validation_list,0)),MATCH("
~"&Name&Status,Name&Status&"",0)),ROW(Name)-MIN(ROW(Name))+1)>1,1))

Hope this helps!