Count x2 Appearance of Numeric Value in any Row
 

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)

Count x2 Appearance of Numeric Value in any Row
 

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


On Mar 31, 1:40*pm, "Sam via OfficeKB.com" <u4102@uwe wrote:
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)

Count x2 Appearance of Numeric Value in any Row
 

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


On Mar 31, 2:01*pm, JP wrote:
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")

Count x2 Appearance of Numeric Value in any Row
 

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

JP wrote:
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.

--JP

--
Message posted via http://www.officekb.com

Count x2 Appearance of Numeric Value in any Row
 

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.


--
Biff
Microsoft Excel MVP


"Sam via OfficeKB.com" <u4102@uwe wrote in message
news:8208d647695af@uwe...
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

JP wrote:
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.

--JP

--
Message posted via http://www.officekb.com

Count x2 Appearance of Numeric Value in any Row
 

Hi Biff,

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

Cheers,
Sam

T. Valko wrote:
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.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200804/1

Count x2 Appearance of Numeric Value in any Row
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Sam via OfficeKB.com" <u4102@uwe wrote in message
news:820c7bc651493@uwe...
Hi Biff,

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

Cheers,
Sam

T. Valko wrote:
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.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200804/1

Count x2 Appearance of Numeric Value in any Row
 

Thanks Biff. How is Excel 2007 treating you?

--JP


On Apr 1, 2:28*pm, "T. Valko" wrote:
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.

--
Biff
Microsoft Excel MVP

Count x2 Appearance of Numeric Value in any Row
 

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


"JP" wrote in message
...
Thanks Biff. How is Excel 2007 treating you?

--JP


On Apr 1, 2:28 pm, "T. Valko" wrote:
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.

--
Biff
Microsoft Excel MVP

Count x2 Appearance of Numeric Value in any Row
 

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


On Apr 2, 2:13*am, "T. Valko" wrote:
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