Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find the numbers that are used in a range | Excel Discussion (Misc queries) | |||
How to find a range of numbers? | Excel Worksheet Functions | |||
Find Median of Positive numbers only in Range | Excel Worksheet Functions | |||
Using COUNTIF to find numbers within a range greater than the mean | Excel Worksheet Functions | |||
find numbers in a range that add to a specific value | Excel Discussion (Misc queries) |