Hi,
I need a way to sort 20 numbers the are in 1 field into 20 seperate fields or count these numbers and report how many of those numbers are between 01 - 10, 11 - 20, 21 - 30 etc upto 71 - 80. the numbers are seperated by a , 'comma' here is an example of what i want to do:
41, 52, 32, 08, 39, 55, 21, 20, 09, 46, 15, 05, 77, 23, 69, 60, 12, 19, 07, 29
they are the numbers in the cell. i want to have a script to make each number go into its own cell either on the same row or in another sheet on the same row, or count the numbers that are in that cell and report how many of each number between a set of numberrs.
so the ouput for this line of numbers should be:
01 - 10 : 4
11 - 20 : 4
21 - 30 : 3
31 - 40 : 2
41 - 50 : 2
51 - 60 : 3
61 - 70 : 1
71 - 80 : 1
or to put each seperate number in a different cell without commas.
thanks Anthony
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Hi Anthony,
You can split the numbers into 20 different fields using the Data...Text to Columns menu item. Specify "Delimited" text, click the "Next" button, then check the boxes for "Space" and "Comma" as delimiters.
You can now use a SUMPRODUCT formula to count your values. If cells A1:T1 contain your data fields, and cells Z1:Z8 contain the start of each decade range (1, 11, 21, 31, 41, 51, 61 & 71), then the following formula returns the count:
=SUMPRODUCT((A$1:T$1>=Z1)*
You can also solve the problem without splitting your data up into separate fields.
Assume that cell A1 contains your list of 20 numbers (separated by commas) and that cells Z1:Z8 contain the numbers 1, 11, 21, 31, 41, 51, 61 & 71 (same as above). The following formula returns the count of numbers in each decade range:
=SUMPRODUCT((MID("," & A$1 & ",",FIND("|",SUBSTITUTE(",
Cheers!
Brad
Anthony_E,
Somewhat after the event it is worth noting that with a sorted list of the numbers in a vertical list (starting in cell A2) a formula such as the following will count the number in each decade (0-10, 11-20, 21-30, 31-40, 41-50, 51-60, 61-70, 71-70 etc) if copied down column B next to the sorted list:
=IF(INT((A1-1)/10)=INT((A2
Unless I am wrong this is a rather easier solution than given so far.
Patrick
Anthony_E,
You might like to have a look at the following file to make the listing of the numbers in each decade somewhat easier:
http://www.asdy88.dsl.pipe
I know it's after the event but all the same you might find it useful.
Patrick
Business Accounts
Answer for Membership
by: ZwiekhorstPosted on 2004-12-20 at 23:54:50ID: 12873822
Hi Anthony,
this does what you want it will take you number range in cell B1 and put the result into B3 to B10
you just have to put in the A column the "0-10" etc..
Sub numbercontr()
Dim numbers(100), k(8) As Integer
Dim i As Integer
Range("B1").Select
i = 0
For j = 1 To 100 Step 4
numbers(i) = Val(Mid(ActiveCell.Text, j, 2))
i = i + 1
Next
i = 0
While numbers(i) > 0
If (numbers(i) < 11) Then k(1) = k(1) + 1 Else: _
If numbers(i) < 21 Then k(2) = k(2) + 1 Else: _
If numbers(i) < 31 Then k(3) = k(3) + 1 Else: _
If numbers(i) < 41 Then k(4) = k(4) + 1 Else: _
If numbers(i) < 51 Then k(5) = k(5) + 1 Else: _
If numbers(i) < 61 Then k(6) = k(6) + 1 Else: _
If numbers(i) < 71 Then k(7) = k(7) + 1 Else: _
If numbers(i) < 81 Then k(8) = k(8) + 1
i = i + 1
Wend
Range("b3").Select
For i = 1 To 8
ActiveCell(i, 1) = k(i)
Next
End Sub
If you need more help for a full macro let me now
cheers Eric