Need A Formular To Sort And Show Frequency
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 -- travelersway ------------------------------------------------------------------------ travelersway's Profile: http://www.excelforum.com/member.php...o&userid=17623 View this thread: http://www.excelforum.com/showthread...hreadid=390071 |
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 -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=390071 |
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 ------------------------------------------------------------------------ travelersway's Profile: http://www.excelforum.com/member.php...o&userid=17623 View this thread: http://www.excelforum.com/showthread...hreadid=390071 |
If you already have column B working, in column C just do a COUNTIF(A:A,B) travelersway Wrote: 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 ------------------------------------------------------------------------ Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094 View this thread: http://www.excelforum.com/showthread...hreadid=390071 |
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 ------------------------------------------------------------------------ travelersway's Profile: http://www.excelforum.com/member.php...o&userid=17623 View this thread: http://www.excelforum.com/showthread...hreadid=390071 |
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. travelersway Wrote: 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 ------------------------------------------------------------------------ Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094 View this thread: http://www.excelforum.com/showthread...hreadid=390071 |
MORRIGAN, Thank you for your help and staying with me ! Everything is working just the way it should. Thanks again, Travelersway -- travelersway ------------------------------------------------------------------------ travelersway's Profile: http://www.excelforum.com/member.php...o&userid=17623 View this thread: http://www.excelforum.com/showthread...hreadid=390071 |
All times are GMT +1. The time now is 05:11 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com