Formula Challenge 018 – Conditional Unique Nth or next smallest value

[jeffreyweir]

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]

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]

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]

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


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

 

[jeffreyweir]

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 ;-)

 

[jeffreyweir]

A bit worrying that we haven't heard from Sajan in a while. Hope he's alright.

 

[safiyya]

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]

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]

@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]

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]

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]

This revised formula seems to work:

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


73 Characters.