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

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,
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?

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.

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.

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)

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.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.