Find the numbers used in a range

M

MIK

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.
 
S

Shane Devenshire

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top