I just watched a YouTube video Chandoo posted about a year ago. It was tips for IT users of Excel. One of the features was showing the forumula that would find the 2nd smallest and third smallest items in a list. In his example, he showed that if there were two equal items, one would be the smaller and the other would be found in the search numerically after. i.e.,
5,4,1,2,8,2
Smallest returns 1
Second smallest returns 2
Third smallest returns 2
Fourth smallest returns 4
The rational is that the list is sorted and the n'th number is returned.
In his example he uses a date, and the number and shows the date associated with the number. Problem is that even though there are two 2's only the date associdated with the first one is returned. I had a similar circumstance in a sheet I created a few days ago and got around the problem with a extremely clumsy formula. My guess is that a better Excel programmer has a great solution. I am obviously not that person.
In any case, here is a test sheet illustrating my question. I attached a JPG not knowing how people here feel about downloading spreadsheets from strangers. I know I don't like to.
The code behind the 2nd smallest number and date is in H4 and I4 and shown at the bottom of the image.
My solution was not to use a named table and start the search in the row after the prior search was found. It works but is clumsy, especially if the length of the list grows. I was tagging values that were repeated more than 3 times in the list.
Regards,
Paul
5,4,1,2,8,2
Smallest returns 1
Second smallest returns 2
Third smallest returns 2
Fourth smallest returns 4
The rational is that the list is sorted and the n'th number is returned.
In his example he uses a date, and the number and shows the date associated with the number. Problem is that even though there are two 2's only the date associdated with the first one is returned. I had a similar circumstance in a sheet I created a few days ago and got around the problem with a extremely clumsy formula. My guess is that a better Excel programmer has a great solution. I am obviously not that person.
In any case, here is a test sheet illustrating my question. I attached a JPG not knowing how people here feel about downloading spreadsheets from strangers. I know I don't like to.
The code behind the 2nd smallest number and date is in H4 and I4 and shown at the bottom of the image.
My solution was not to use a named table and start the search in the row after the prior search was found. It works but is clumsy, especially if the length of the list grows. I was tagging values that were repeated more than 3 times in the list.
Regards,
Paul