Find the numbers used in a range
 

I have a range D1:Q25 that can have differnet numbers and the numbers can
repeat. In column A, I want to list the each number used in the range. Column
B is to show the number of times each number is used in the range. In other
words, I want to make a summary of numbers used in the range and show how
many times each number is used in the range.

Find the numbers used in a range
 

You have an answer in your other post

"MIK" wrote:

I have a range D1:Q25 that can have differnet numbers and the numbers can
repeat. In column A, I want to list the each number used in the range. Column
B is to show the number of times each number is used in the range. In other
words, I want to make a summary of numbers used in the range and show how
many times each number is used in the range.

Find the numbers used in a range
 

see your other posts

--
Biff
Microsoft Excel MVP


"MIK" wrote in message
...
I have a range D1:Q25 that can have differnet numbers and the numbers can
repeat. In column A, I want to list the each number used in the range.
Column
B is to show the number of times each number is used in the range. In
other
words, I want to make a summary of numbers used in the range and show how
many times each number is used in the range.

Find the numbers used in a range
 

Hi,

Here is one way:
This method requires the ATP to be attached - Tools, Add-ins, check the
Analysis ToolPak

1. Choose Tools, Data Analysis, Sampling, OK
2. In the Input Range enter D1:Q25, choose Periodic and enter 1 as the
Period, choose Output Range and enter A1, click OK. This is a column with
all your numbers.
3. In B1 enter the formula =A1
4. In B2 enter the formula
=IF(A2=A1,1/0,A2)
Copy this formula down
5. Select all the data in column B and choose Copy, Edit, Paste Special,
Values.
6. With column B selected press F5, Special, Constants, and uncheck
everything except Errors. Click OK
7. Press Ctrl+- (control minus) and choose Shift cells up. These are the
unique items from your source data.
8. In C1 enter the formula
=COUNTIF(D$1:Q$25,B1)
fill this formula down.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"MIK" wrote:

I have a range D1:Q25 that can have differnet numbers and the numbers can
repeat. In column A, I want to list the each number used in the range. Column
B is to show the number of times each number is used in the range. In other
words, I want to make a summary of numbers used in the range and show how
many times each number is used in the range.

Find the numbers used in a range
 

use subtotals
--
Thanks
Suleman Peerzade


"MIK" wrote:

I have a range D1:Q25 that can have differnet numbers and the numbers can
repeat. In column A, I want to list the each number used in the range. Column
B is to show the number of times each number is used in the range. In other
words, I want to make a summary of numbers used in the range and show how
many times each number is used in the range.