Need A Formular To Sort And Show Frequency

**travelersway** · 07-25-2005, 09:48 PM

Column A is a list of numbers that will be manually entered, processed, and replaced by new data repeatedly.
I'd like to have column B represent the sorted list without multiples in desending order with NO BLANK ROWS and column C represent the frequency of occurrences for each value in A

A...B...C
5...3...2
3...4...3
4...5...1
4
3
4

Can this be done without manual operations?

Help is appreciated.

Travelersway

**Bryan Hessey** · 07-25-2005, 10:14 PM

Yes, firstly number column D as a helper column, put a 1 in D1 and CTRL-Drag to following rows, this should sequential increase for each row.

assuming data is in A1 to A999

In B1 enter
=SMALL(A$1:A$999,D1) with crtl/shift/enter to enter the array formula
and formula drag this to the last line of data

in C1 enter
=" "&B2&" = "&COUNTIF(A$1:A$999,B1),"")
in C2 enter
=IF(B2<>B1," "&B2&" = "&COUNTIF(A$1:A$999,B2),"")
and formula drag this to last line of data

This produces the required counts, you can omit the " "&B2&" = "& if you just want numbers

**travelersway** · 07-26-2005, 10:23 AM

Bryan, Thanks

I was able to get column B to sort. I couldn't completely get column c to work. But, I dont think that is what I need.

I edited my thread to note that column B should be the sorted list of column A with no multiples and no blank rows. Column C should be the associated frequency for column B.

Any ideas?

Thanks,
Travelersway

**Morrigan** · 07-26-2005, 11:08 AM

If you already have column B working, in column C just do a COUNTIF(A:A,B)

Originally Posted by travelersway

Bryan, Thanks

I was able to get column B to sort. I couldn't completely get column c to work. But, I dont think that is what I need.

I edited my thread to note that column B should be the sorted list of column A with no multiples and no blank rows. Column C should be the associated frequency for column B.

Any ideas?

Thanks,
Travelersway

**travelersway** · 07-26-2005, 12:52 PM

Thanks Morrigan,

I have B Sorted, but not as I need. It's sorted with multiple entries.
I need B to show one entry for each value in A with no blank rows between them and c to reflect the frequency of B in A.

Thanks for your help. Any ideas?

Travelersway

**Morrigan** · 07-26-2005, 01:58 PM

Assume row 1 is your header, A2:A7 is the data

B2 = SMALL(A$2:A$7,1+SUM(C$1:C1))
C2 = COUNTIF(A$2:A$7,B2)

Here is the catch, C1 can be blank but CANNOT be a number. I am using C1 as a helper cell otherwise the formula in B2 will be different from the formula in the rest of the cells in column B.

Hope it helps.

Originally Posted by travelersway

Thanks Morrigan,

I have B Sorted, but not as I need. It's sorted with multiple entries.
I need B to show one entry for each value in A with no blank rows between them and c to reflect the frequency of B in A.

Thanks for your help. Any ideas?

Travelersway

**travelersway** · 07-27-2005, 11:07 AM

MORRIGAN,

Thank you for your help and staying with me ! Everything is working just the way it should if all the cells are used iin column B. This error message ( #NUM! ) comes up for the balance of the unused cells in column B after the sort of column A.

Is there a way to remove it or make it hidden? The problem is that it appears on the form that I want to print

Thanks again,
Travelersway

Need A Formular To Sort And Show Frequency

LinkBack

Thread Tools

Rate This Thread

Display

Need A Formular To Sort And Show Frequency

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions