• 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

    Hui...

  • 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

jeffreyweir

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

[pre]
Code:
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
[/pre]
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]
Rules


* 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
Here's my array formula. Header rows not included in range i and d.

=MAX(MIN(IF(i=l,d)),LARGE(IFERROR((FREQUENCY((i=l)*d,d)>0)*d,),n))

Length: 66
 

shrivallabha

Excel Ninja
=MAX(Any_Challenge_17_formula,MIN(IF(i=l,d)))


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

jeffreyweir

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


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


Instead I went for:

=MIN(IFERROR(LARGE(IF(IFERROR((FREQUENCY((i=l)*d,d)>0)*d,)>0,d),ROW(OFFSET(A1,,,n))),FALSE()))


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

safiyya

New Member
safiyya,


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
 

Faseeh

Excel Ninja
Hi safiyya,


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


Regards.

Faseeh
 

Luke M

Excel Ninja
@safiyya

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.
 

jeffreyweir

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


=MAX(LARGE(IF(FREQUENCY(IF(i=l,d),d),d),n),MIN(IF(i=l,d)))

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

Luke M

Excel Ninja
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
 

jeffreyweir

Active Member
This revised formula seems to work:

=MIN(IFERROR(LARGE(IF(FREQUENCY(IF(i=l,d),d),d),ROW(OFFSET(A1,,,n))),""))


73 Characters.
 
Top