Sorting numbers, please help?
I ahve a spreadsheet that contains 5 columns of whole numbers rangeing from 1
to 100. I need to count how many times each number appears in each column or even in all 5 columns together. Ex. the number 1 appears 252 times, etc. Does anyone know how to sort the data that way? I have imported into an access table as well if that's easier. Please help? |
you could use CountIf function on excel.
e.g CountIF("A1:A500", 1) which will should how many time 1 appear from range (A1 to A500). "Kevin Schultz" wrote: I ahve a spreadsheet that contains 5 columns of whole numbers rangeing from 1 to 100. I need to count how many times each number appears in each column or even in all 5 columns together. Ex. the number 1 appears 252 times, etc. Does anyone know how to sort the data that way? I have imported into an access table as well if that's easier. Please help? |
Hopefully someone has a better method, but: Assuming that you want to keep the results in 5 columns to match the same number of rows, I have used A1 to E9 as my range, you will need to adjust the range for the number of rows that you have. assuming that columns G to K are clear, in G1 put =SMALL($A$1:$E$9,ROW())&" = "&COUNTIF($A$1:$E$9,SMALL($A$1:$E$9,ROW())) in H1 put =SMALL($A$1:$E$9,ROW()+9)&" = "&COUNTIF($A$1:$E$9,SMALL($A$1:$E$9,ROW()+9)) in I1 put =SMALL($A$1:$E$9,ROW()+18)&" = "&COUNTIF($A$1:$E$9,SMALL($A$1:$E$9,ROW()+18)) in J1 put =SMALL($A$1:$E$9,ROW()+27)&" = "&COUNTIF($A$1:$E$9,SMALL($A$1:$E$9,ROW()+27)) in K1 put =SMALL($A$1:$E$9,ROW()+36)&" = "&COUNTIF($A$1:$E$9,SMALL($A$1:$E$9,ROW()+36)) (adjust the numbers 9, 18, 27 & 36 accordingly) Formula-drag those to the number of rows for which you have data. (you can use just one column and drag it 5 times the number of rows) Hope this helps Kevin Schultz Wrote: I ahve a spreadsheet that contains 5 columns of whole numbers rangeing from 1 to 100. I need to count how many times each number appears in each column or even in all 5 columns together. Ex. the number 1 appears 252 times, etc. Does anyone know how to sort the data that way? I have imported into an access table as well if that's easier. Please help? -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=473267 |
thanks Dan, or even =Countif(A$1:E$999,Row()) and copy that down 100 rows (for data 1 to 100) Dan Wrote: you could use CountIf function on excel. e.g CountIF("A1:A500", 1) which will should how many time 1 appear from range (A1 to A500). "Kevin Schultz" wrote: I ahve a spreadsheet that contains 5 columns of whole numbers rangeing from 1 to 100. I need to count how many times each number appears in each column or even in all 5 columns together. Ex. the number 1 appears 252 times, etc. Does anyone know how to sort the data that way? I have imported into an access table as well if that's easier. Please help? -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=473267 |
All times are GMT +1. The time now is 10:39 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com