Count x2 Appearance of Numeric Value in any Row

[Sam via OfficeKB.com]

Hi All,

I have a dynamic named range "Data" that spans 8 columns and many rows.
"Data" houses non sequenced numeric values (not ascending or descending order)

 

[JP]

If your range was named "myData", this formula would count the number
of times the number 1 appears in the range.

=IF(COUNTIF(myData,1)>1,"Dupes!","No Dupes")

And this formula (entered as an array w/ Ctrl-Shift-Enter), will let
you know if there are any dupes at all:

=IF(COUNTA(myData)=SUM(1/COUNTIF(myData,myData)),"All Unique","Some
dupes")

Does this help?

--JP

 

[JP]

I guess I should have paid attention to the part where you mentioned
the name of the range was "Data".

=IF(COUNTIF(Data,1)>1,"Dupes!","No Dupes")

Array (Ctrl-Shift-Enter):
=IF(COUNTA(Data)=SUM(1/COUNTIF(Data,Data)),"All Unique","Some dupes")


--JP

 

[Sam via OfficeKB.com]

Hi JP,

I kept checking for replies yesterday but nothing showed up? Thank you very
much for your reply and assistance.

Not looking for duplicates as such but rather a summed count of a particular
number that appears twice and only twice in a row but can have multiple x2
appearances within my dynamic range.

Just a brief recap:
I would like to find the summed count of a specific (but variable) numeric
value that appears only twice (x2) in any row; the numeric value can appear
in any column. Would very much appreciate a formula using the dynamic named
range "Data" as opposed to the A1 notation style of cell referencing.

The criterion in this instance is "0" zero.

Sample Data Layout (using 5 columns, 8 rows):
0 89 0 100 0
216 100 205 70 220
0 216 218 0 206
343 99 250 323 256
234 0 211 99 214
219 134 235 500 243
205 0 0 150 214
99 0 250 0 0

Expected Results:
Looking for criterion "0": a summed count of 2 should be returned.
Rows 3 and 7 meet the criteria: explicit x2 appearance of zero (0) in any row.


Further help appreciated, if possible.

Cheers,
Sam

If your range was named "myData", this formula would count the number
of times the number 1 appears in the range.

=IF(COUNTIF(myData,1)>1,"Dupes!","No Dupes")

And this formula (entered as an array w/ Ctrl-Shift-Enter), will let
you know if there are any dupes at all:

=IF(COUNTA(myData)=SUM(1/COUNTIF(myData,myData)),"All Unique","Some
dupes")

Does this help?

Not looking for duplicates as such, please see above.

 

[T. Valko]

Try this:

=SUMPRODUCT(--(MMULT((ISNUMBER(data))*(data=0),{1;1;1;1;1})=2))

That's based on your sample using 5 columns. For your real data with 8
columns change:

{1;1;1;1;1}

to:

{1;1;1;1;1;1;1;1}

Note this is limited to ~5400 rows of data.

 

[Sam via OfficeKB.com]

Hi Biff,

Thank you very much for your time and assistance. That's worked Great!

Cheers,
Sam

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[JP]

Thanks Biff. How is Excel 2007 treating you?

--JP

 

[T. Valko]

How is Excel 2007 treating you?

I'm not real impressed with it and hardly ever use it.

There are a few new features that are positive but almost everything else is
not "as easy" as it was in previous versions (IMHO).


--
Biff
Microsoft Excel MVP


Thanks Biff. How is Excel 2007 treating you?

--JP

 

[JP]

That is the feeling I am getting, just from listening and reading
comments from users. I assume you have both versions installed, I was
curious to see how that worked (Dick Kusleika installed three
versions: http://tinyurl.com/3yszmn) so I could test out code in
multiple versions, but due to cost considerations and the general
disappointment I am hearing about it, I've changed my mind.

Thx,
JP