Hi Everyone,

I have a Row of 100 Numbers that Range from 1 to 1000.
What I would like to do is to Count How Many Times the FIRST Digit
1,2,3,4,5,6,7,8 & 9 Appear in Each Row Please.
I know that I can Use Left(B10) for Example to get the FIRST Digit But
How do you get it to do the Count Please.

Thanks in Advance.
All the Best.
Paul



*** Sent via Developersdex http://www.developersdex.com ***

Hi Paul,

One way, if I understand the question -

Sub test()

For i = 1 To 100
Cells(i, 1) = Int(1000 * Rnd + 1)
Next

Dim nArr(1 To 9) As Long
Dim n As Long

For i = 1 To 100
n = Val(Left(Cells(i, 1), 1))
nArr(n) = nArr(n) + 1
Next

Range("b1:b9") = Application.Transpose(nArr)

End Sub

Regards,
Peter T

"Paul Black" wrote in message
...
Hi Everyone,

I have a Row of 100 Numbers that Range from 1 to 1000.
What I would like to do is to Count How Many Times the FIRST Digit
1,2,3,4,5,6,7,8 & 9 Appear in Each Row Please.
I know that I can Use Left(B10) for Example to get the FIRST Digit But
How do you get it to do the Count Please.

Thanks in Advance.
All the Best.
Paul



*** Sent via Developersdex http://www.developersdex.com ***

I've just seen Bob's neat Sumproduct formula. Try inserting following at the
end of my Test macro

For i = 1 To 9
s = Chr(34) & CStr(i) & Chr(34)
Cells(i, 3).Formula = "=SUMPRODUCT(--(LEFT(A1:A100,1)=" & s & "))"
Next

Peter T


One way, if I understand the question -

Sub test()

For i = 1 To 100
Cells(i, 1) = Int(1000 * Rnd + 1)
Next

Dim nArr(1 To 9) As Long
Dim n As Long

For i = 1 To 100
n = Val(Left(Cells(i, 1), 1))
nArr(n) = nArr(n) + 1
Next

Range("b1:b9") = Application.Transpose(nArr)

End Sub

Regards,
Peter T

"Paul Black" wrote in message
...
Hi Everyone,

I have a Row of 100 Numbers that Range from 1 to 1000.
What I would like to do is to Count How Many Times the FIRST Digit
1,2,3,4,5,6,7,8 & 9 Appear in Each Row Please.
I know that I can Use Left(B10) for Example to get the FIRST Digit But
How do you get it to do the Count Please.

Thanks in Advance.
All the Best.
Paul



*** Sent via Developersdex http://www.developersdex.com ***

Thanks for the Replies.

Bob,
Your Solution Works Great, Thanks.

Ashley,
I will have a Look at Using Pivot Tables, Thanks.

Peter,
Actually I have about 500 Rows of 100 Numbers, for Example B5:CM505.
What I would like is in Cell CO5 for Example is the Total Count of the
First Digit 1 for that Individual Row, then in Cell CP5 the Total Count
of the First Digit 2 for that Individual Row etc upto Cell CW5 and the
Total Count of the First Digit 9 for that Individual Row. Then Continue
Down All 500 Rows Putting the Counts for EACH Individual Row. It would
Also be Nice to Have a Grand Total for EACH First Digit Count at the
Bottom.

Thanks Again Everyone.
All the Best.
Paul



Count of FIRST Digit
From: Peter T

I've just seen Bob's neat Sumproduct formula. Try inserting following at
the
end of my Test macro

For i = 1 To 9
s = Chr(34) & CStr(i) & Chr(34)
Cells(i, 3).Formula = "=SUMPRODUCT(--(LEFT(A1:A100,1)=" & s & "))"
Next

Peter T


One way, if I understand the question -

Sub test()

For i = 1 To 100
Cells(i, 1) = Int(1000 * Rnd + 1)
Next

Dim nArr(1 To 9) As Long
Dim n As Long

For i = 1 To 100
n = Val(Left(Cells(i, 1), 1))
nArr(n) = nArr(n) + 1
Next

Range("b1:b9") = Application.Transpose(nArr)

End Sub

Regards,
Peter T

"Paul Black" wrote in message
...
Hi Everyone,

I have a Row of 100 Numbers that Range from 1 to 1000.
What I would like to do is to Count How Many Times the FIRST Digit
1,2,3,4,5,6,7,8 & 9 Appear in Each Row Please.
I know that I can Use Left(B10) for Example to get the FIRST Digit
But
How do you get it to do the Count Please.

Thanks in Advance.
All the Best.
Paul



*** Sent via Developersdex http://www.developersdex.com ***

Put this formula in CO5

=SUMPRODUCT(--(LEFT(B5:CM5,1)=TEXT(COLUMN(A1),"0")))

copy across to CW5, then copy down to CO505:CW505

in CO506, add

=SUM(CO5:CO505)

and copy across to CW506

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Paul Black" wrote in message
...
Thanks for the Replies.

Bob,
Your Solution Works Great, Thanks.

Ashley,
I will have a Look at Using Pivot Tables, Thanks.

Peter,
Actually I have about 500 Rows of 100 Numbers, for Example B5:CM505.
What I would like is in Cell CO5 for Example is the Total Count of the
First Digit 1 for that Individual Row, then in Cell CP5 the Total Count
of the First Digit 2 for that Individual Row etc upto Cell CW5 and the
Total Count of the First Digit 9 for that Individual Row. Then Continue
Down All 500 Rows Putting the Counts for EACH Individual Row. It would
Also be Nice to Have a Grand Total for EACH First Digit Count at the
Bottom.

Thanks Again Everyone.
All the Best.
Paul



Count of FIRST Digit
From: Peter T

I've just seen Bob's neat Sumproduct formula. Try inserting following at
the
end of my Test macro

For i = 1 To 9
s = Chr(34) & CStr(i) & Chr(34)
Cells(i, 3).Formula = "=SUMPRODUCT(--(LEFT(A1:A100,1)=" & s & "))"
Next

Peter T


One way, if I understand the question -

Sub test()

For i = 1 To 100
Cells(i, 1) = Int(1000 * Rnd + 1)
Next

Dim nArr(1 To 9) As Long
Dim n As Long

For i = 1 To 100
n = Val(Left(Cells(i, 1), 1))
nArr(n) = nArr(n) + 1
Next

Range("b1:b9") = Application.Transpose(nArr)

End Sub

Regards,
Peter T

"Paul Black" wrote in message
...
Hi Everyone,

I have a Row of 100 Numbers that Range from 1 to 1000.
What I would like to do is to Count How Many Times the FIRST Digit
1,2,3,4,5,6,7,8 & 9 Appear in Each Row Please.
I know that I can Use Left(B10) for Example to get the FIRST Digit
But
How do you get it to do the Count Please.

Thanks in Advance.
All the Best.
Paul



*** Sent via Developersdex http://www.developersdex.com ***

Hi Bob,

I think if autofill'ing it might need a bit of absolute for the columns.

=SUMPRODUCT(--(LEFT($B5:$CM5,1)=TEXT(COLUMN(A1),"0")))

Regards,
Peter T

Put this formula in CO5

=SUMPRODUCT(--(LEFT(B5:CM5,1)=TEXT(COLUMN(A1),"0")))

copy across to CW5, then copy down to CO505:CW505

in CO506, add

=SUM(CO5:CO505)

and copy across to CW506

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Paul Black" wrote in message
...
Thanks for the Replies.

Bob,
Your Solution Works Great, Thanks.

Ashley,
I will have a Look at Using Pivot Tables, Thanks.

Peter,
Actually I have about 500 Rows of 100 Numbers, for Example B5:CM505.
What I would like is in Cell CO5 for Example is the Total Count of the
First Digit 1 for that Individual Row, then in Cell CP5 the Total Count
of the First Digit 2 for that Individual Row etc upto Cell CW5 and the
Total Count of the First Digit 9 for that Individual Row. Then Continue
Down All 500 Rows Putting the Counts for EACH Individual Row. It would
Also be Nice to Have a Grand Total for EACH First Digit Count at the
Bottom.

Thanks Again Everyone.
All the Best.
Paul



Count of FIRST Digit
From: Peter T

I've just seen Bob's neat Sumproduct formula. Try inserting following at
the
end of my Test macro

For i = 1 To 9
s = Chr(34) & CStr(i) & Chr(34)
Cells(i, 3).Formula = "=SUMPRODUCT(--(LEFT(A1:A100,1)=" & s & "))"
Next

Peter T


One way, if I understand the question -

Sub test()

For i = 1 To 100
Cells(i, 1) = Int(1000 * Rnd + 1)
Next

Dim nArr(1 To 9) As Long
Dim n As Long

For i = 1 To 100
n = Val(Left(Cells(i, 1), 1))
nArr(n) = nArr(n) + 1
Next

Range("b1:b9") = Application.Transpose(nArr)

End Sub

Regards,
Peter T

"Paul Black" wrote in message
...
Hi Everyone,

I have a Row of 100 Numbers that Range from 1 to 1000.
What I would like to do is to Count How Many Times the FIRST Digit
1,2,3,4,5,6,7,8 & 9 Appear in Each Row Please.
I know that I can Use Left(B10) for Example to get the FIRST Digit
But
How do you get it to do the Count Please.

Thanks in Advance.
All the Best.
Paul



*** Sent via Developersdex http://www.developersdex.com ***

For a non-formula, vba approach:

Sub test2()
Dim rng As Range

Dim n As Long, nr As Long, nc As Long
Set rng = Range("b5:cm504")
With rng
ReDim nArr(1 To .Rows.Count, 1 To 9) As Long

For nr = 1 To .Rows.Count
For nc = 1 To .Columns.Count
n = Val(Left(.Cells(nr, nc), 1))
nArr(nr, n) = nArr(nr, n) + 1
Next
Next

End With

Set rng = rng(1).Offset(0, rng.Columns.Count + 1) _
.Resize(UBound(nArr), 9)
rng.Value = nArr

End Sub

Sub DumpRand()
Dim rng As Range
Set rng = Range("b5:cm504")
Application.Calculation = xlCalculationManual
With rng
..Cells(1, 1).Formula = "=Int(1000 * Rand() + 1)"
..Cells(1, 1).AutoFill .Columns(1)
..Columns(1).AutoFill rng
Application.Calculate
..Value = .Value
End With
Application.Calculation = xlCalculationAutomatic

End Sub

BTW - this is 500 rows x 90 columns per your example with B:CM

Regards,
Peter T


Actually I have about 500 Rows of 100 Numbers, for Example B5:CM505.
What I would like is in Cell CO5 for Example is the Total Count of the
First Digit 1 for that Individual Row, then in Cell CP5 the Total Count
of the First Digit 2 for that Individual Row etc upto Cell CW5 and the
Total Count of the First Digit 9 for that Individual Row. Then Continue
Down All 500 Rows Putting the Counts for EACH Individual Row. It would
Also be Nice to Have a Grand Total for EACH First Digit Count at the
Bottom.

Thanks Again Everyone.
All the Best.
Paul



Count of FIRST Digit
From: Peter T

I've just seen Bob's neat Sumproduct formula. Try inserting following at
the
end of my Test macro

For i = 1 To 9
s = Chr(34) & CStr(i) & Chr(34)
Cells(i, 3).Formula = "=SUMPRODUCT(--(LEFT(A1:A100,1)=" & s & "))"
Next

Peter T


One way, if I understand the question -

Sub test()

For i = 1 To 100
Cells(i, 1) = Int(1000 * Rnd + 1)
Next

Dim nArr(1 To 9) As Long
Dim n As Long

For i = 1 To 100
n = Val(Left(Cells(i, 1), 1))
nArr(n) = nArr(n) + 1
Next

Range("b1:b9") = Application.Transpose(nArr)

End Sub

Regards,
Peter T

"Paul Black" wrote in message
...
Hi Everyone,

I have a Row of 100 Numbers that Range from 1 to 1000.
What I would like to do is to Count How Many Times the FIRST Digit
1,2,3,4,5,6,7,8 & 9 Appear in Each Row Please.
I know that I can Use Left(B10) for Example to get the FIRST Digit
But
How do you get it to do the Count Please.

Thanks in Advance.
All the Best.
Paul



*** Sent via Developersdex http://www.developersdex.com ***

I posted the wrong macro, this should be faster:

Sub test3()
Dim rng As Range
Dim vArr
Dim n As Long, nr As Long, nc As Long

Set rng = Range("b5:cm504")
vArr = rng.Value
ReDim nArr(1 To UBound(vArr), 1 To 9) As Long

For nr = 1 To UBound(vArr)
For nc = 1 To UBound(vArr, 2)
n = Val(Left(vArr(nr, nc), 1))
nArr(nr, n) = nArr(nr, n) + 1
Next
Next

Set rng = rng(1).Offset(0, rng.Columns.Count + 1) _
.Resize(UBound(nArr), 9)
rng.Value = nArr

End Sub

Regards,
Peter T

For a non-formula, vba approach:

Sub test2()
Dim rng As Range

Dim n As Long, nr As Long, nc As Long
Set rng = Range("b5:cm504")
With rng
ReDim nArr(1 To .Rows.Count, 1 To 9) As Long

For nr = 1 To .Rows.Count
For nc = 1 To .Columns.Count
n = Val(Left(.Cells(nr, nc), 1))
nArr(nr, n) = nArr(nr, n) + 1
Next
Next

End With

Set rng = rng(1).Offset(0, rng.Columns.Count + 1) _
.Resize(UBound(nArr), 9)
rng.Value = nArr

End Sub

Sub DumpRand()
Dim rng As Range
Set rng = Range("b5:cm504")
Application.Calculation = xlCalculationManual
With rng
.Cells(1, 1).Formula = "=Int(1000 * Rand() + 1)"
.Cells(1, 1).AutoFill .Columns(1)
.Columns(1).AutoFill rng
Application.Calculate
.Value = .Value
End With
Application.Calculation = xlCalculationAutomatic

End Sub

BTW - this is 500 rows x 90 columns per your example with B:CM

Regards,
Peter T


Actually I have about 500 Rows of 100 Numbers, for Example B5:CM505.
What I would like is in Cell CO5 for Example is the Total Count of the
First Digit 1 for that Individual Row, then in Cell CP5 the Total Count
of the First Digit 2 for that Individual Row etc upto Cell CW5 and the
Total Count of the First Digit 9 for that Individual Row. Then Continue
Down All 500 Rows Putting the Counts for EACH Individual Row. It would
Also be Nice to Have a Grand Total for EACH First Digit Count at the
Bottom.

Thanks Again Everyone.
All the Best.
Paul



Count of FIRST Digit
From: Peter T

I've just seen Bob's neat Sumproduct formula. Try inserting following at
the
end of my Test macro

For i = 1 To 9
s = Chr(34) & CStr(i) & Chr(34)
Cells(i, 3).Formula = "=SUMPRODUCT(--(LEFT(A1:A100,1)=" & s & "))"
Next

Peter T


One way, if I understand the question -

Sub test()

For i = 1 To 100
Cells(i, 1) = Int(1000 * Rnd + 1)
Next

Dim nArr(1 To 9) As Long
Dim n As Long

For i = 1 To 100
n = Val(Left(Cells(i, 1), 1))
nArr(n) = nArr(n) + 1
Next

Range("b1:b9") = Application.Transpose(nArr)

End Sub

Regards,
Peter T

"Paul Black" wrote in message
...
Hi Everyone,

I have a Row of 100 Numbers that Range from 1 to 1000.
What I would like to do is to Count How Many Times the FIRST Digit
1,2,3,4,5,6,7,8 & 9 Appear in Each Row Please.
I know that I can Use Left(B10) for Example to get the FIRST Digit
But
How do you get it to do the Count Please.

Thanks in Advance.
All the Best.
Paul



*** Sent via Developersdex http://www.developersdex.com ***

Hi Paul,

=SUMPRODUCT(--(LEFT(A15:A20,1)="1"))

This will count 1 and 11 as separate matching items. Is this correct?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Paul Black" wrote in message
...
Hi Everyone,

I have a Row of 100 Numbers that Range from 1 to 1000.
What I would like to do is to Count How Many Times the FIRST Digit
1,2,3,4,5,6,7,8 & 9 Appear in Each Row Please.
I know that I can Use Left(B10) for Example to get the FIRST Digit But
How do you get it to do the Count Please.

Thanks in Advance.
All the Best.
Paul



*** Sent via Developersdex http://www.developersdex.com ***

On Sun, 22 May 2005 11:39:20 +0100, Bob Phillips
wrote:

Hi Paul,

=SUMPRODUCT(--(LEFT(A15:A20,1)="1"))

This will count 1 and 11 as separate matching items. Is this correct?


I have a problem that this approach might help with.
I have a table with six-digit entries like:
100000
010000
020010
003001
010040
010201

I need a formula that can return the count of each possible digit.
So, if I checked for "1" it would tell me that all 6 above entries contain
a 1 (the last entry has 2 1's, but it's only 1 cell)
If I checked for "2", it would tell me that 2 of the above entries contain
a 2.

Is this possible, without resorting to splitting the information into six
separate columns?

Darren

Something like:

=SUM(--((NOT(ISERROR(FIND(1,A1:A6))))))

entered as an array formula with <Ctrl <Shift <Enter.

No doubt there are many other and better ways.

--

Vasant

"Darren Hill" wrote in message
news:op.sq6sahyled89cl@omega...
On Sun, 22 May 2005 11:39:20 +0100, Bob Phillips
wrote:

Hi Paul,

=SUMPRODUCT(--(LEFT(A15:A20,1)="1"))

This will count 1 and 11 as separate matching items. Is this correct?


I have a problem that this approach might help with.
I have a table with six-digit entries like:
100000
010000
020010
003001
010040
010201

I need a formula that can return the count of each possible digit.
So, if I checked for "1" it would tell me that all 6 above entries contain
a 1 (the last entry has 2 1's, but it's only 1 cell)
If I checked for "2", it would tell me that 2 of the above entries contain
a 2.

Is this possible, without resorting to splitting the information into six
separate columns?

Darren

Thanks, that's great.
One extra featu
If I have data like:

100200

I need to be able to check only the highest digit in a cell. So, when I
perform this search, and count the "1"s, the above cell shouldn't be
included. On this version of the count, this cell should be counted only
when I search for "2".
Is there a way to modify the formula for this?



On Sun, 22 May 2005 18:13:47 +0100, Vasant Nanavati <vasantn *AT* aol
*DOT* <com wrote:

Something like:

=SUM(--((NOT(ISERROR(FIND(1,A1:A6))))))

entered as an array formula with <Ctrl <Shift <Enter.

No doubt there are many other and better ways.




--
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/

The silence is deafening :-). This one is substantially trickier, I think. I
will play with it when I have a bit of time.

--

Vasant

"Darren Hill" wrote in message
news:op.sq6t3jcved89cl@omega...
Thanks, that's great.
One extra featu
If I have data like:

100200

I need to be able to check only the highest digit in a cell. So, when I
perform this search, and count the "1"s, the above cell shouldn't be
included. On this version of the count, this cell should be counted only
when I search for "2".
Is there a way to modify the formula for this?



On Sun, 22 May 2005 18:13:47 +0100, Vasant Nanavati <vasantn *AT* aol
*DOT* <com wrote:

Something like:

=SUM(--((NOT(ISERROR(FIND(1,A1:A6))))))

entered as an array formula with <Ctrl <Shift <Enter.

No doubt there are many other and better ways.




--
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/

Hi Darren,

Try following to return the highest digit found in any of the six digits of
any cell in the range:

=MAX(VALUE(MID(A1:A6,{1,2,3,4,5,6},1)))

Then combine with Bob's formula
=SUM(--(ISNUMBER(FIND(MAX(VALUE(MID(A1:A6,{1,2,3,4,5,6},1 ))),A1:A6))))

or if the above Max formula is in B7
=SUM(--(ISNUMBER(FIND(MAX(VALUE(B7)),A1:A6))))

which should return a count of the cells that contain that highest digit.

All above formulas array entered. Also, all cells in the range must have six
digits. So a value like 1234 should be a string '001234. Not sure how to
cater for variable length numbers.

If I correctly understand what's required (?) this seems to work, but better
double check!

Regards,
Peter T

"Darren Hill" wrote in message
news:op.sq6t3jcved89cl@omega...
Thanks, that's great.
One extra featu
If I have data like:

100200

I need to be able to check only the highest digit in a cell. So, when I
perform this search, and count the "1"s, the above cell shouldn't be
included. On this version of the count, this cell should be counted only
when I search for "2".
Is there a way to modify the formula for this?



On Sun, 22 May 2005 18:13:47 +0100, Vasant Nanavati <vasantn *AT* aol
*DOT* <com wrote:

Something like:

=SUM(--((NOT(ISERROR(FIND(1,A1:A6))))))

entered as an array formula with <Ctrl <Shift <Enter.

No doubt there are many other and better ways.




--
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/

A bit simpler

=SUM(--(ISNUMBER(FIND(1,A1:A6))))

also as an array formula.

Continuing the SUMPRODUCT (non-array) theme

=SUMPRODUCT(--(ISNUMBER(FIND(1,A1:A6))))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message
...
Something like:

=SUM(--((NOT(ISERROR(FIND(1,A1:A6))))))

entered as an array formula with <Ctrl <Shift <Enter.

No doubt there are many other and better ways.

--

Vasant

"Darren Hill" wrote in message
news:op.sq6sahyled89cl@omega...
On Sun, 22 May 2005 11:39:20 +0100, Bob Phillips
wrote:

Hi Paul,

=SUMPRODUCT(--(LEFT(A15:A20,1)="1"))

This will count 1 and 11 as separate matching items. Is this correct?


I have a problem that this approach might help with.
I have a table with six-digit entries like:
100000
010000
020010
003001
010040
010201

I need a formula that can return the count of each possible digit.
So, if I checked for "1" it would tell me that all 6 above entries
contain
a 1 (the last entry has 2 1's, but it's only 1 cell)
If I checked for "2", it would tell me that 2 of the above entries
contain
a 2.

Is this possible, without resorting to splitting the information into
six
separate columns?

Darren

Thanks, Bob, for those other approaches. I see they both work, too.
Can you see an answer to my additional question (repeated below):

If I have data like:

100200

I need to be able to check only the highest digit in a cell. So, when I
perform this search, and count the "1"s, the above cell
shouldn't be included. On this version of the count, this cell should be
counted only when I search for "2".
Is there a way to modify the formula for this?

Finally, these formulas all use "--" - what does this signify?

Thanks again

Darren


On Sun, 22 May 2005 18:54:09 +0100, Bob Phillips
wrote:

A bit simpler

=SUM(--(ISNUMBER(FIND(1,A1:A6))))

also as an array formula.

Continuing the SUMPRODUCT (non-array) theme

=SUMPRODUCT(--(ISNUMBER(FIND(1,A1:A6))))