• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula Challenge 018 – Conditional Unique Nth or next smallest value


Active Member
Formula Challenge 018– Conditional Unique Nth or next smallest value

Challenge description

In our last challenge, we wrote a formula to display the nth largest unique number from the Data column for items in the Item column that matched the currently selected Lookup Item.

But if N was say 6, but we only had 3 unique items, then all these formulas returned Zero.

So your challenge is to amend those approaches so that in those instances, the next smallest non-zero unique value is returned.

Ready? Go!

Challenge setup

Put the following data in A1:B13

Item (i)	Data (d)
Cat	        662100
Cat	        11287
Cat	        11287
Apple	        9439
Apple	        3090
Cat	        3090
Apple        	662100
Cat        	11287
Cat	        11287
Cat	        9439
Apple	        3090
Cat	        3090
Assign the name 'i' to the Item list

Assign the name 'd' to the Data list

Those ranges can include or exclude the header rows as you see fit.  

Put the following data in D1:E2

[pre][code]Nbr(n):	Lookup(l):
3	Cat
Assign the name 'n' to the range D2

Assign the name 'l' to the E2

If successful, your formula results will match the below table:

Item N = 1 N = 2 N = 3 N = 4 N = 5
Cat 662100 11287 9439 3090 3090
Apple 662100 9439 3090 3090 3090[/code][/pre]

* No whole column references

* No intermediate formulas

* No VBA

* No biting, hitting, scratching, or eye gouging. I want a good clean fight, people.

* The formula should work no matter where the source data is shifted in the worksheet. So no hard-coded references other than the named ranges i,d,n, and l

My formula length: 94 characters.

Luke M

Excel Ninja
Staff member
Here's my array formula. Header rows not included in range i and d.


Length: 66


Excel Ninja

The word unique loses context. 0 means we have no more unique numbers.


Active Member
Damn, you guys are too clever for me!

I didn't think of MIN(IF(i=l,d).

Instead I went for:


One day I'm going to win one of these things ;-)


New Member

sir, somebody can guide me,

i want in excel sheet1 example every 28 rows one page,i have nearly one thousand pages in one sheet1,now i need to edit every 20 rows in thousand pages example jhon,

may i know any shortcuts formula


Excel Ninja
Hi safiyya,

Please start with a new thread to get replies. See green sticky posts on forums home page for further help.



Luke M

Excel Ninja
Staff member

I would strongly suggest you read the forum rules. Not only have you attempted to hijack this thread, but you've also posted in several other threads that have no relevance. I've had to go through several of them and remove your posts. Assuming you read these responses, you need to create a new thread with your problem clearly stated.

Also, I'd recommend showing us an example of what you want to see. The word description you've given so far is not clear enough for me/us to help you.


Active Member
Taking Sajan's challenge 17 formula, and using Shrivallabha's method, then currently the shortest is this:


58 Characters. And there I was laughing gleefully to myself at my 94 character 'winner'.

Luke M

Excel Ninja
Staff member
Hold on there Jeff. I know you told me to ignore the errors in challenge 17, but that won't work here if you want the smallest number to be repeated. Trying to have your cake and eat it too? =P


Active Member
This revised formula seems to work:


73 Characters.