Group random numbers

[Tony S.]

Can someone help me with a way to produce a grouping from a list of random
numbers. I know the column need to be sorted first, but I don't know how to
group the results. In col "A" I have something like:
1
2
4
6
8
9
12
10
23
11
3
13
5
15

I want to produce something like:
1-6, 8-13, 15, 23

Thanks!

 

[Sean Timmons]

Make sure you have a coumn Header (i.e. - "Numbers").Highlight your column,
Data - Pivot Chart.

Drop Numbers in the row and data fields.

right-clik on one fo the numbers in the row field - Group and show Detail -
Group.

Pick your starting number and the range per, so starting at 1 by 6 and pick
a top range.

 

[Tony S.]

Sean,
Thanks for your reply, but I was looking for a routine to automatically
create the out I needed. Do you know if there is a VB way to acomplish this?

 

[Sean Timmons]

Unfortunatel,y I don't.

Sean,
Thanks for your reply, but I was looking for a routine to automatically
create the out I needed. Do you know if there is a VB way to acomplish this?

 

[Tony S.]

Thanks for trying.
I've got a long list of numbers to try to group on a daily basis. Does
anyone else have any ideas?

 

[Mike Middleton]

Tony S. -

For a VBA approach, you could look at the Better Histogram VBA code
available from the Histogram page at www.treeplan.com. That code uses the
FREQUENCY worksheet function for grouping data into a frequency distribution
table, and you could disregard the charting code.

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel

 

[Tony S.]

Thanks Mike, I'll look into that.

Tony S. -

For a VBA approach, you could look at the Better Histogram VBA code
available from the Histogram page at www.treeplan.com. That code uses the
FREQUENCY worksheet function for grouping data into a frequency distribution
table, and you could disregard the charting code.

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel

 

[Oxo]

Assuming your values are all in column A, create a table (D1:E4) that in the
first column has the starting value of each group and in the second column
the description of that group, e.g.
1 1 - 6 widgets
7 7 - 10 widgets
11 11 widgets
12 12 - 17 widgets
18 18
19 19 - 23 widgets

Then, in B1, use a vlookup formula
VLOOKUP(a1,$d$1:$E:$4,2,1)

Copy this formula down to all rows with values that you want to group. Note
that your table should address all values you want to measure (no gaps)

 

[Tony S.]

Thanks for your input Oxo, but I don't have descriptions, just a large list
of non-sequencial random numbers that I need to automatically sort group as
best as possible with have to go through and read the list to determine where
gaps my be.

From this list of numbers, I'm trying to obtain an output that reads
something like
1-57, 60-68, 72, 75, 80-94, 103 etc.