Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting Numbers with Multiple Decimals (cont.) | Excel Discussion (Misc queries) | |||
Sorting numbers with brackets | Excel Discussion (Misc queries) | |||
Sorting alphanumeric numbers | Excel Discussion (Misc queries) | |||
Sorting when some numbers have a text suffix | Excel Discussion (Misc queries) | |||
Sorting imported "numbers" | Excel Discussion (Misc queries) |