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