Count of FIRST Digit

[Paul Black]

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

 

[Peter T]

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

 

[Guest]

Hi Paul

There are many ways to do this. You can generate a pivot table based on your
"=left(b10,1)" formula. Do this by highlighting the range of numbers
generated from the formula, and go to Data>Pivottable Report. Click the
"Next" button and then Next again. Then click finish.

You should then see a blue outlined box on a fresh worksheet. A Pivottable
Field List dialog box should appear. Drag the field that appears in this box
onto the blue box area. You then get a count for each unique number, 1 - 10.

This is the way I would do it - Pivottables are great for summarising
datasets. You can also use a slightly messier solution of "=Countif({range},
"1")" replacing the 1 with 2..10 which will return a count of 1, then 2 each
time you change the 2nd parameter.

Hope this helps

Ashley
http://vbanut.blogspot.com

 

[Bob Phillips]

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)

 

[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

 

[Paul Black]

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



Re: 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

 

[Bob Phillips]

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)

 

[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

 

[Peter T]

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

 

[Bob Phillips]

Good point

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

 

[Peter T]

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

 

[Darren Hill]

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

 

[Vasant Nanavati]

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.

 

[Darren Hill]

Thanks, that's great.
One extra feature:
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?

 

[Bob Phillips]

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)

 

[Darren Hill]

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

 

[Vasant Nanavati]

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

 

[Vasant Nanavati]

Here you go ... clunky, but I think it works, as long as there are exactly 6
digits in each cell:

=SUM((--(ISNUMBER(FIND($B$1,A1:A6))))*(--MID(A1:A6,1,1)<=$B$1)*(--MID(A1:A6,
2,1)<=$B$1)*(--MID(A1:A6,3,1)<=$B$1)*(--MID(A1:A6,4,1)<=$B$1)*(--MID(A1:A6,5
,1)<=$B$1)*(--MID(A1:A6,6,1)<=$B$1))

entered as an array formula with <Ctrl> <Shift> <Enter>. B1 contains the
target digit.

 

[Darren Hill]

Thanks, take your time - I'll only be checking the group every 5 minutes
or so...
Just kidding

Darren

 

[Peter T]

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