software

I have a column which has a variable number of comma delimited values in the
range of 11 to 56 in each cell. Is there a method or macro to count the
occurrences of each number in the column and either output the results to a
file or another worksheet?

Example:
23,40,52,31,
42,14,
56,
27,43,19,
etc.

What I am doing now is coping the column to Word, closing the margins to 3
characters, coping that result to an clean worksheet, sorting the new column
ascending and printing the results and counting the occurrences manually.
This is getting tedious as the column grows in length.

Can anyone help me or point me to a solution?

Thanks in advance!

Glynn ..

OT: isn't there a program to sort a comma delimited ASCII numeric file?
Seems like I remember one from the DOS days.

You could use Data gt; Text to columns with comma as a delimiter to split
each number into a separate cell then use countif formulas to count
each value,

e.g.

=COUNTIF(range,11) etc.--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=532111Two steps:1. Use the Text to Columns feature to separate the data into separate cells
pull-down Data gt; Text to Columns... and use the comma as the separator.

2. After each value is in its own cell, select the entire block and use
COUNTIF() to count the occurance of each unique item.
--
Gary's Studentquot;Glynn Furrquot; wrote:

gt; I have a column which has a variable number of comma delimited values in the
gt; range of 11 to 56 in each cell. Is there a method or macro to count the
gt; occurrences of each number in the column and either output the results to a
gt; file or another worksheet?
gt;
gt; Example:
gt; 23,40,52,31,
gt; 42,14,
gt; 56,
gt; 27,43,19,
gt; etc.
gt;
gt; What I am doing now is coping the column to Word, closing the margins to 3
gt; characters, coping that result to an clean worksheet, sorting the new column
gt; ascending and printing the results and counting the occurrences manually.
gt; This is getting tedious as the column grows in length.
gt;
gt; Can anyone help me or point me to a solution?
gt;
gt; Thanks in advance!
gt;
gt; Glynn ..
gt;
gt; OT: isn't there a program to sort a comma delimited ASCII numeric file?
gt; Seems like I remember one from the DOS days.
gt;
gt;
gt;

Hi!

Assume the numbers are in the range A1:A10.

In C1 enter 11.

In D1 enter this formula:

=SUMPRODUCT(LEN(A$1:A$10)-LEN(SUBSTITUTE(A$1:A$10,C1,quot;quot;)))/2

Select both C1 and D1 and drag copy down to row 46.

Biff

quot;Glynn Furrquot; gt; wrote in message
...
gt;I have a column which has a variable number of comma delimited values in
gt;the range of 11 to 56 in each cell. Is there a method or macro to count the
gt;occurrences of each number in the column and either output the results to a
gt;file or another worksheet?
gt;
gt; Example:
gt; 23,40,52,31,
gt; 42,14,
gt; 56,
gt; 27,43,19,
gt; etc.
gt;
gt; What I am doing now is coping the column to Word, closing the margins to 3
gt; characters, coping that result to an clean worksheet, sorting the new
gt; column ascending and printing the results and counting the occurrences
gt; manually. This is getting tedious as the column grows in length.
gt;
gt; Can anyone help me or point me to a solution?
gt;
gt; Thanks in advance!
gt;
gt; Glynn ..
gt;
gt; OT: isn't there a program to sort a comma delimited ASCII numeric file?
gt; Seems like I remember one from the DOS days.
gt;