# Thread: LARGE Function Dilemma (Excel 2003 SP1)

1. ## LARGE Function Dilemma (Excel 2003 SP1)

I have a list of numbers between 100 and 200 that are randomly listed in B5:B500. I want to have column C list (once) in descending order each number that appears in column B. Then in column D I want to show how many times the corresponding number in column C occurs in the overall list in column B. See attached sample worksheet, scanned with NAV.

My problem is that when I use the LARGE function in column C, it returns multiple instances of the same number (i.e., cells C11:C13; C14:C16; C19:C20, etc. in sample worksheet)

How can I change my formula in column C so that multiple instances of the same number will not be returned?

Thanks.

2. ## Re: LARGE Function Dilemma (Excel 2003 SP1)

If you download and install Laurent Longre's free Morefunc add-in from Excel add-ins, you can use the UNIQUEVALUES function:
- Select F5:F29 (or whatever range you want, as long as it's large enough to hold all distinct values)
- Enter the formula =UNIQUEVALUES(B5:B29) and confirm with Ctrl+Shift+Enter to make it an array formula.

3. ## Re: LARGE Function Dilemma (Excel 2003 SP1)

Does the attached work for you?
Starting in row 5

<table border=1><td>C</td><td>D</td><td>=max(range)</td><td>=counitif(range,c5)</td>
<td>=LARGE(range,SUM(\$D\$55)+1)</td><td>=counitif(range,c6)</td></table>

4. ## Re: LARGE Function Dilemma (Excel 2003 SP1)

Thanks for your replies. I chose mbarron

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•