Need A Formular To Sort And Show Frequency

[travelersway]

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

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

I'd like to assure columns B and C have no blank rows.

Can this be done without manual operations?

Help is appreciated.

Travelersway

 

[Bryan Hessey]

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]

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]

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

 

[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 row
between them and c to reflect the frequency of B in A.

Thanks for your help. Any ideas?

Travelerswa

 

[Morrigan]

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 usin
C1 as a helper cell otherwise the formula in B2 will be different fro
the formula in the rest of the cells in column B.


Hope it helps.

 

[travelersway]

MORRIGAN,

Thank you for your help and staying with me ! Everything is workin
just the way it should.

Thanks again,
Travelerswa