| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
The Kth element in a text list
=Small(A1:A5,2) from
12 15 12 10 17 returns the value 12, but what from def ghi qrs def abc returns "def" as the second lowest, by sort order or ascii value? I can get as far as =MIN(CODE(A1:A5)) entered as an array formula giving 97, the lowest ascii value of the first letter, but then I come off the rails. Any ideas? kjs |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
The Kth element in a text list
It might be able to be done.....but, it depends on what you're really
working with. So, if your posted example does not reflect actual scenario, we need to know. That being said....Maybe something like this? With this list of 3-letter values in A1:A10 abc add def aab bft dhi wcl zet drw jkl B1: (the rank item you want....eg 5 means you want the 5th smallest) This formula returns that ranked item from the list C1: =INDEX(A1:A10,MATCH(SMALL(INDEX(--(CODE(MID(UPPER(A1:A10),1,1))&CODE(MID(UPPER(A1:A1 0),2,1))&CODE(MID(UPPER(A1:A10),3,1))),0),B1),INDE X(--(CODE(MID(UPPER(A1:A10),1,1))&CODE(MID(UPPER(A1:A1 0),2,1))&CODE(MID(UPPER(A1:A10),3,1))),0),0)) or....if you prefer an ARRAY FORMULA version (committed with ctrl+shift+enter) C1: =INDEX(A1:A10,MATCH(SMALL(--(CODE(MID(UPPER(A1:A10),1,1))&CODE(MID(UPPER(A1:A1 0),2,1))&CODE(MID(UPPER(A1:A10),3,1))),B1),--(CODE(MID(UPPER(A1:A10),1,1))&CODE(MID(UPPER(A1:A1 0),2,1))&CODE(MID(UPPER(A1:A10),3,1))),0)) Does that help? *********** Regards, Ron XL2002, WinXP "kjs" wrote: =Small(A1:A5,2) from 12 15 12 10 17 returns the value 12, but what from def ghi qrs def abc returns "def" as the second lowest, by sort order or ascii value? I can get as far as =MIN(CODE(A1:A5)) entered as an array formula giving 97, the lowest ascii value of the first letter, but then I come off the rails. Any ideas? kjs |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
The Kth element in a text list
Try this:
Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =INDEX(rng,MATCH(SMALL(COUNTIF(rng,"<"&rng),k),COU NTIF(rng,"<"&rng),0)) Where k = the kth element Biff "kjs" wrote in message ... =Small(A1:A5,2) from 12 15 12 10 17 returns the value 12, but what from def ghi qrs def abc returns "def" as the second lowest, by sort order or ascii value? I can get as far as =MIN(CODE(A1:A5)) entered as an array formula giving 97, the lowest ascii value of the first letter, but then I come off the rails. Any ideas? kjs |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
The Kth element in a text list
Ron Coderre wrote...
It might be able to be done.....but, it depends on what you're really working with. So, if your posted example does not reflect actual scenario, we need to know. You've put your finger on the limitation of my query - the text values, and I don't yet have specific data, would be of any length not just 3 letters. That being said....Maybe something like this? <brevity snip Does that help? *********** Regards, Ron XL2002, WinXP It sure does - it gives me one oar when I had none. kjs |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
The Kth element in a text list
T. Valko...
Try this: Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =INDEX(rng,MATCH(SMALL(COUNTIF(rng,"<"&rng),k),COU NTIF(rng,"<"&rng),0)) Where k = the kth element Biff What a marvel! I'll spend the rest of the day figuring just how that works. Thank you. kjs |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
The Kth element in a text list
Then you might consider this
If list order does not matter: 1) sort the list in ascending order 2) run a series of numbers (from 1 to the list count) next to the list Then the n-th ranked numeric value corresponds to the n-th ranked text list item. Does that help? *********** Regards, Ron XL2002, WinXP "kjs" wrote: Ron Coderre wrote... It might be able to be done.....but, it depends on what you're really working with. So, if your posted example does not reflect actual scenario, we need to know. You've put your finger on the limitation of my query - the text values, and I don't yet have specific data, would be of any length not just 3 letters. That being said....Maybe something like this? <brevity snip Does that help? *********** Regards, Ron XL2002, WinXP It sure does - it gives me one oar when I had none. kjs |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
The Kth element in a text list
I think my formula stash is getting too big. That formula was in it, but I
completely missed it. Fortunately, you didn't! *********** Regards, Ron XL2002, WinXP "T. Valko" wrote: Try this: Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =INDEX(rng,MATCH(SMALL(COUNTIF(rng,"<"&rng),k),COU NTIF(rng,"<"&rng),0)) Where k = the kth element Biff "kjs" wrote in message ... =Small(A1:A5,2) from 12 15 12 10 17 returns the value 12, but what from def ghi qrs def abc returns "def" as the second lowest, by sort order or ascii value? I can get as far as =MIN(CODE(A1:A5)) entered as an array formula giving 97, the lowest ascii value of the first letter, but then I come off the rails. Any ideas? kjs |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
The Kth element in a text list
You're welcome. Thanks for the feedback!
Biff "kjs" wrote in message ... T. Valko... Try this: Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =INDEX(rng,MATCH(SMALL(COUNTIF(rng,"<"&rng),k),COU NTIF(rng,"<"&rng),0)) Where k = the kth element Biff What a marvel! I'll spend the rest of the day figuring just how that works. Thank you. kjs |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
The Kth element in a text list
I have it stashed under: Sort text using formula
I think it was in a post by Harlan where I first saw this technique. Until then, I had also used some variation of CODE. Biff "Ron Coderre" wrote in message ... I think my formula stash is getting too big. That formula was in it, but I completely missed it. Fortunately, you didn't! *********** Regards, Ron XL2002, WinXP "T. Valko" wrote: Try this: Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =INDEX(rng,MATCH(SMALL(COUNTIF(rng,"<"&rng),k),COU NTIF(rng,"<"&rng),0)) Where k = the kth element Biff "kjs" wrote in message ... =Small(A1:A5,2) from 12 15 12 10 17 returns the value 12, but what from def ghi qrs def abc returns "def" as the second lowest, by sort order or ascii value? I can get as far as =MIN(CODE(A1:A5)) entered as an array formula giving 97, the lowest ascii value of the first letter, but then I come off the rails. Any ideas? kjs |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
The Kth element in a text list
Wow! This formula is so versatile. Thanks Biff for sharing.
I had so much fun playing with it and came up with the following findings. Please correct me if I am wrong. Keep SMALL and change "<" to "", I get the kth element in descending order. Keep "<" and change SMALL to LARGE, I also get the kth element in descending order. With SMALL, "<" and k = 1, I get the smallest element. With LARGE, "<" and k = 1, I get the largest element. With SMALL, "" and k = 1, I also get the largest element. All this is good only when I have only numbers (right aligned) or only text (left aligned) in the range. If I have text and numbers in the range, I don't trust any of the above formulae. e.g. A1:A10 I only have zzz (text) or 999 (number). I get the same result regardless of whether I use SMALL or LARGE in the formula. I am keeping the "<" in the formula. I don't understand why. I also don't trust the formula when I have blanks. I only focus on all text or all numbers plus blanks in the range. e.g. =INDEX(rng,MATCH(SMALL(COUNTIF(rng,"<"&rng),1),COU NTIF(rng,"<"&rng),0)) rng is A1:A10. A1: aaa A10: zzz A2:A9 have other 3-letter combination in between. No blanks. The formula returns aaa as the smallest element. No problem. Now delete aaa in A1 and the result is 0. No problem. Put back aaa into A1 and I get aaa. No problem. Now delete zzz in A10. I still get aaa and not 0 for the blank in A10. ??? I am missing something. Appreciate input. Epinn "T. Valko" wrote in message ... Try this: Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =INDEX(rng,MATCH(SMALL(COUNTIF(rng,"<"&rng),k),COU NTIF(rng,"<"&rng),0)) Where k = the kth element Biff "kjs" wrote in message ... =Small(A1:A5,2) from 12 15 12 10 17 returns the value 12, but what from def ghi qrs def abc returns "def" as the second lowest, by sort order or ascii value? I can get as far as =MIN(CODE(A1:A5)) entered as an array formula giving 97, the lowest ascii value of the first letter, but then I come off the rails. Any ideas? kjs |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
The Kth element in a text list
One more thing I just discovered.
LARGE and "" is exactly the same as SMALL and "<" i.e. ascending order. Epinn "Epinn" wrote in message ... Wow! This formula is so versatile. Thanks Biff for sharing. I had so much fun playing with it and came up with the following findings. Please correct me if I am wrong. Keep SMALL and change "<" to "", I get the kth element in descending order. Keep "<" and change SMALL to LARGE, I also get the kth element in descending order. With SMALL, "<" and k = 1, I get the smallest element. With LARGE, "<" and k = 1, I get the largest element. With SMALL, "" and k = 1, I also get the largest element. All this is good only when I have only numbers (right aligned) or only text (left aligned) in the range. If I have text and numbers in the range, I don't trust any of the above formulae. e.g. A1:A10 I only have zzz (text) or 999 (number). I get the same result regardless of whether I use SMALL or LARGE in the formula. I am keeping the "<" in the formula. I don't understand why. I also don't trust the formula when I have blanks. I only focus on all text or all numbers plus blanks in the range. e.g. =INDEX(rng,MATCH(SMALL(COUNTIF(rng,"<"&rng),1),COU NTIF(rng,"<"&rng),0)) rng is A1:A10. A1: aaa A10: zzz A2:A9 have other 3-letter combination in between. No blanks. The formula returns aaa as the smallest element. No problem. Now delete aaa in A1 and the result is 0. No problem. Put back aaa into A1 and I get aaa. No problem. Now delete zzz in A10. I still get aaa and not 0 for the blank in A10. ??? I am missing something. Appreciate input. Epinn "T. Valko" wrote in message ... Try this: Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =INDEX(rng,MATCH(SMALL(COUNTIF(rng,"<"&rng),k),COU NTIF(rng,"<"&rng),0)) Where k = the kth element Biff "kjs" wrote in message ... =Small(A1:A5,2) from 12 15 12 10 17 returns the value 12, but what from def ghi qrs def abc returns "def" as the second lowest, by sort order or ascii value? I can get as far as =MIN(CODE(A1:A5)) entered as an array formula giving 97, the lowest ascii value of the first letter, but then I come off the rails. Any ideas? kjs |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
The Kth element in a text list
Don't get carried away with </ Large/Small! It can be confusing! <bg
That particular formula only works with TEXT and I assumed the rng only contained TEXT. I didn't see any numbers and/or blank/empty cells represented in the sample. This formula works for TEXT only and accounts for blank/empty cells (no error trap) (array entered): =INDEX(rng,MATCH(SMALL(IF(rng<"",COUNTIF(rng,"<"& rng)),ROWS($1:1)),IF(rng<"",COUNTIF(rng,"<"&rng)) ,0)) This formula works for both TEXT and NUMBERS and accounts for blank/empty cells (array entered). Note: without the error trap any blank/empty cells will be included as 0's at the end of the list: =IF(ROWS($1:1)<=COUNTA(rng),INDEX(rng,MATCH(SMALL( COUNTIF(rng,"<"&rng&"")+COUNT(rng)*ISTEXT(rng)+100 000*ISBLANK(rng),ROWS($1:1)),COUNTIF(rng,"<"&rng&" ")+COUNT(rng)*ISTEXT(rng)+100000*ISBLANK(rng),0)), "") It will sort numbers first, then the text. Biff "Epinn" wrote in message ... One more thing I just discovered. LARGE and "" is exactly the same as SMALL and "<" i.e. ascending order. Epinn "Epinn" wrote in message ... Wow! This formula is so versatile. Thanks Biff for sharing. I had so much fun playing with it and came up with the following findings. Please correct me if I am wrong. Keep SMALL and change "<" to "", I get the kth element in descending order. Keep "<" and change SMALL to LARGE, I also get the kth element in descending order. With SMALL, "<" and k = 1, I get the smallest element. With LARGE, "<" and k = 1, I get the largest element. With SMALL, "" and k = 1, I also get the largest element. All this is good only when I have only numbers (right aligned) or only text (left aligned) in the range. If I have text and numbers in the range, I don't trust any of the above formulae. e.g. A1:A10 I only have zzz (text) or 999 (number). I get the same result regardless of whether I use SMALL or LARGE in the formula. I am keeping the "<" in the formula. I don't understand why. I also don't trust the formula when I have blanks. I only focus on all text or all numbers plus blanks in the range. e.g. =INDEX(rng,MATCH(SMALL(COUNTIF(rng,"<"&rng),1),COU NTIF(rng,"<"&rng),0)) rng is A1:A10. A1: aaa A10: zzz A2:A9 have other 3-letter combination in between. No blanks. The formula returns aaa as the smallest element. No problem. Now delete aaa in A1 and the result is 0. No problem. Put back aaa into A1 and I get aaa. No problem. Now delete zzz in A10. I still get aaa and not 0 for the blank in A10. ??? I am missing something. Appreciate input. Epinn "T. Valko" wrote in message ... Try this: Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =INDEX(rng,MATCH(SMALL(COUNTIF(rng,"<"&rng),k),COU NTIF(rng,"<"&rng),0)) Where k = the kth element Biff "kjs" wrote in message ... =Small(A1:A5,2) from 12 15 12 10 17 returns the value 12, but what from def ghi qrs def abc returns "def" as the second lowest, by sort order or ascii value? I can get as far as =MIN(CODE(A1:A5)) entered as an array formula giving 97, the lowest ascii value of the first letter, but then I come off the rails. Any ideas? kjs |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
The Kth element in a text list
Don't get me wrong. You gave a perfect solution for the OP's request.
Yes, I got carried away. I was "greedy" and I thought I could use that formula on numbers as well. It did seem to work when I had all numbers and no blanks. Okay, I won't drive myself crazy with Large/Small, "<" and "" etc. Forgive me and I shall give everyone and myself a break hopefully soon. Thanks for the wonderful gifts. If I understand correctly, both formulae return the smallest item. I won't worry about the nth element in a range of text and numbers. Epinn "T. Valko" wrote in message ... Don't get carried away with </ Large/Small! It can be confusing! <bg That particular formula only works with TEXT and I assumed the rng only contained TEXT. I didn't see any numbers and/or blank/empty cells represented in the sample. This formula works for TEXT only and accounts for blank/empty cells (no error trap) (array entered): =INDEX(rng,MATCH(SMALL(IF(rng<"",COUNTIF(rng,"<"& rng)),ROWS($1:1)),IF(rng<"",COUNTIF(rng,"<"&rng)) ,0)) This formula works for both TEXT and NUMBERS and accounts for blank/empty cells (array entered). Note: without the error trap any blank/empty cells will be included as 0's at the end of the list: =IF(ROWS($1:1)<=COUNTA(rng),INDEX(rng,MATCH(SMALL( COUNTIF(rng,"<"&rng&"")+COUNT(rng)*ISTEXT(rng)+100 000*ISBLANK(rng),ROWS($1:1)),COUNTIF(rng,"<"&rng&" ")+COUNT(rng)*ISTEXT(rng)+100000*ISBLANK(rng),0)), "") It will sort numbers first, then the text. Biff "Epinn" wrote in message ... One more thing I just discovered. LARGE and "" is exactly the same as SMALL and "<" i.e. ascending order. Epinn "Epinn" wrote in message ... Wow! This formula is so versatile. Thanks Biff for sharing. I had so much fun playing with it and came up with the following findings. Please correct me if I am wrong. Keep SMALL and change "<" to "", I get the kth element in descending order. Keep "<" and change SMALL to LARGE, I also get the kth element in descending order. With SMALL, "<" and k = 1, I get the smallest element. With LARGE, "<" and k = 1, I get the largest element. With SMALL, "" and k = 1, I also get the largest element. All this is good only when I have only numbers (right aligned) or only text (left aligned) in the range. If I have text and numbers in the range, I don't trust any of the above formulae. e.g. A1:A10 I only have zzz (text) or 999 (number). I get the same result regardless of whether I use SMALL or LARGE in the formula. I am keeping the "<" in the formula. I don't understand why. I also don't trust the formula when I have blanks. I only focus on all text or all numbers plus blanks in the range. e.g. =INDEX(rng,MATCH(SMALL(COUNTIF(rng,"<"&rng),1),COU NTIF(rng,"<"&rng),0)) rng is A1:A10. A1: aaa A10: zzz A2:A9 have other 3-letter combination in between. No blanks. The formula returns aaa as the smallest element. No problem. Now delete aaa in A1 and the result is 0. No problem. Put back aaa into A1 and I get aaa. No problem. Now delete zzz in A10. I still get aaa and not 0 for the blank in A10. ??? I am missing something. Appreciate input. Epinn "T. Valko" wrote in message ... Try this: Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =INDEX(rng,MATCH(SMALL(COUNTIF(rng,"<"&rng),k),COU NTIF(rng,"<"&rng),0)) Where k = the kth element Biff "kjs" wrote in message ... =Small(A1:A5,2) from 12 15 12 10 17 returns the value 12, but what from def ghi qrs def abc returns "def" as the second lowest, by sort order or ascii value? I can get as far as =MIN(CODE(A1:A5)) entered as an array formula giving 97, the lowest ascii value of the first letter, but then I come off the rails. Any ideas? kjs |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
The Kth element in a text list
I won't worry about the nth element in a range of text and numbers.
Yeah, I can't think of a situation where you'd need to know the 10th element, but ya never know! Basically, the formula is meant to sort the entire range by drag copying. If you needed to know the first or last TEXT value (alphabetically) in a TEXT ONLY list: First: =LOOKUP(2,1/((COUNTIF(rng,"<"&rng)=0)*(rng<"")),rng) Or, an array formula: =INDEX(rng,MATCH(0,IF(rng<"",COUNTIF(rng,"<"&rng) ),0)) For the last, just flip the "<" to "". Biff "Epinn" wrote in message ... Don't get me wrong. You gave a perfect solution for the OP's request. Yes, I got carried away. I was "greedy" and I thought I could use that formula on numbers as well. It did seem to work when I had all numbers and no blanks. Okay, I won't drive myself crazy with Large/Small, "<" and "" etc. Forgive me and I shall give everyone and myself a break hopefully soon. Thanks for the wonderful gifts. If I understand correctly, both formulae return the smallest item. I won't worry about the nth element in a range of text and numbers. Epinn "T. Valko" wrote in message ... Don't get carried away with </ Large/Small! It can be confusing! <bg That particular formula only works with TEXT and I assumed the rng only contained TEXT. I didn't see any numbers and/or blank/empty cells represented in the sample. This formula works for TEXT only and accounts for blank/empty cells (no error trap) (array entered): =INDEX(rng,MATCH(SMALL(IF(rng<"",COUNTIF(rng,"<"& rng)),ROWS($1:1)),IF(rng<"",COUNTIF(rng,"<"&rng)) ,0)) This formula works for both TEXT and NUMBERS and accounts for blank/empty cells (array entered). Note: without the error trap any blank/empty cells will be included as 0's at the end of the list: =IF(ROWS($1:1)<=COUNTA(rng),INDEX(rng,MATCH(SMALL( COUNTIF(rng,"<"&rng&"")+COUNT(rng)*ISTEXT(rng)+100 000*ISBLANK(rng),ROWS($1:1)),COUNTIF(rng,"<"&rng&" ")+COUNT(rng)*ISTEXT(rng)+100000*ISBLANK(rng),0)), "") It will sort numbers first, then the text. Biff "Epinn" wrote in message ... One more thing I just discovered. LARGE and "" is exactly the same as SMALL and "<" i.e. ascending order. Epinn "Epinn" wrote in message ... Wow! This formula is so versatile. Thanks Biff for sharing. I had so much fun playing with it and came up with the following findings. Please correct me if I am wrong. Keep SMALL and change "<" to "", I get the kth element in descending order. Keep "<" and change SMALL to LARGE, I also get the kth element in descending order. With SMALL, "<" and k = 1, I get the smallest element. With LARGE, "<" and k = 1, I get the largest element. With SMALL, "" and k = 1, I also get the largest element. All this is good only when I have only numbers (right aligned) or only text (left aligned) in the range. If I have text and numbers in the range, I don't trust any of the above formulae. e.g. A1:A10 I only have zzz (text) or 999 (number). I get the same result regardless of whether I use SMALL or LARGE in the formula. I am keeping the "<" in the formula. I don't understand why. I also don't trust the formula when I have blanks. I only focus on all text or all numbers plus blanks in the range. e.g. =INDEX(rng,MATCH(SMALL(COUNTIF(rng,"<"&rng),1),COU NTIF(rng,"<"&rng),0)) rng is A1:A10. A1: aaa A10: zzz A2:A9 have other 3-letter combination in between. No blanks. The formula returns aaa as the smallest element. No problem. Now delete aaa in A1 and the result is 0. No problem. Put back aaa into A1 and I get aaa. No problem. Now delete zzz in A10. I still get aaa and not 0 for the blank in A10. ??? I am missing something. Appreciate input. Epinn "T. Valko" wrote in message ... Try this: Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =INDEX(rng,MATCH(SMALL(COUNTIF(rng,"<"&rng),k),COU NTIF(rng,"<"&rng),0)) Where k = the kth element Biff "kjs" wrote in message ... =Small(A1:A5,2) from 12 15 12 10 17 returns the value 12, but what from def ghi qrs def abc returns "def" as the second lowest, by sort order or ascii value? I can get as far as =MIN(CODE(A1:A5)) entered as an array formula giving 97, the lowest ascii value of the first letter, but then I come off the rails. Any ideas? kjs |
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
The Kth element in a text list
Sorry, KJS, I didn't expect I had so much to say. I should have started my own thread. If I want to discuss on first and last value I'll definitely start my own thread. Allow me to finish off with sorting on this thread.
Biff, I am glad I said "If I understand correctly......" and you clarified. I almost miss the beauty of the formulae - *sort* by *dragging* and not just the first value. You know, I was so focused on nth element because it was how it all started. I think finally I understand every single word in your posts i.e. error trap, blanks at the end of the list, text, sort ...... Without dragging down, I missed so much. We use COUNTIF, < etc. because it is TEXT. Of course, we can use the same formula for numbers. But why bother, when I can just do =SMALL(C$1:C$10,ROWS($1:1)) for all numbers and no blanks. I don't really want to understand the reasoning/difference between the two. I think I have taken in a lot within the last 24 hours and I don't want to confuse myself any more. Also, I have to go and study first and last value now. I have a bunch of formulae (e.g. LOOKUP, COUNTIF) to determine last value and I have to go and check if yours is already there. Thanks for alerting me that I can flip "<" to "" to get a different order. When I first discovered this by myself (plus LARGE/SMALL), I was so delighted. Thank you for your wisdom and patience, my good teacher. Epinn "T. Valko" wrote in message ... I won't worry about the nth element in a range of text and numbers. Yeah, I can't think of a situation where you'd need to know the 10th element, but ya never know! Basically, the formula is meant to sort the entire range by drag copying. If you needed to know the first or last TEXT value (alphabetically) in a TEXT ONLY list: First: =LOOKUP(2,1/((COUNTIF(rng,"<"&rng)=0)*(rng<"")),rng) Or, an array formula: =INDEX(rng,MATCH(0,IF(rng<"",COUNTIF(rng,"<"&rng) ),0)) For the last, just flip the "<" to "". Biff "Epinn" wrote in message ... Don't get me wrong. You gave a perfect solution for the OP's request. Yes, I got carried away. I was "greedy" and I thought I could use that formula on numbers as well. It did seem to work when I had all numbers and no blanks. Okay, I won't drive myself crazy with Large/Small, "<" and "" etc. Forgive me and I shall give everyone and myself a break hopefully soon. Thanks for the wonderful gifts. If I understand correctly, both formulae return the smallest item. I won't worry about the nth element in a range of text and numbers. Epinn "T. Valko" wrote in message ... Don't get carried away with </ Large/Small! It can be confusing! <bg That particular formula only works with TEXT and I assumed the rng only contained TEXT. I didn't see any numbers and/or blank/empty cells represented in the sample. This formula works for TEXT only and accounts for blank/empty cells (no error trap) (array entered): =INDEX(rng,MATCH(SMALL(IF(rng<"",COUNTIF(rng,"<"& rng)),ROWS($1:1)),IF(rng<"",COUNTIF(rng,"<"&rng)) ,0)) This formula works for both TEXT and NUMBERS and accounts for blank/empty cells (array entered). Note: without the error trap any blank/empty cells will be included as 0's at the end of the list: =IF(ROWS($1:1)<=COUNTA(rng),INDEX(rng,MATCH(SMALL( COUNTIF(rng,"<"&rng&"")+COUNT(rng)*ISTEXT(rng)+100 000*ISBLANK(rng),ROWS($1:1)),COUNTIF(rng,"<"&rng&" ")+COUNT(rng)*ISTEXT(rng)+100000*ISBLANK(rng),0)), "") It will sort numbers first, then the text. Biff "Epinn" wrote in message ... One more thing I just discovered. LARGE and "" is exactly the same as SMALL and "<" i.e. ascending order. Epinn "Epinn" wrote in message ... Wow! This formula is so versatile. Thanks Biff for sharing. I had so much fun playing with it and came up with the following findings. Please correct me if I am wrong. Keep SMALL and change "<" to "", I get the kth element in descending order. Keep "<" and change SMALL to LARGE, I also get the kth element in descending order. With SMALL, "<" and k = 1, I get the smallest element. With LARGE, "<" and k = 1, I get the largest element. With SMALL, "" and k = 1, I also get the largest element. All this is good only when I have only numbers (right aligned) or only text (left aligned) in the range. If I have text and numbers in the range, I don't trust any of the above formulae. e.g. A1:A10 I only have zzz (text) or 999 (number). I get the same result regardless of whether I use SMALL or LARGE in the formula. I am keeping the "<" in the formula. I don't understand why. I also don't trust the formula when I have blanks. I only focus on all text or all numbers plus blanks in the range. e.g. =INDEX(rng,MATCH(SMALL(COUNTIF(rng,"<"&rng),1),COU NTIF(rng,"<"&rng),0)) rng is A1:A10. A1: aaa A10: zzz A2:A9 have other 3-letter combination in between. No blanks. The formula returns aaa as the smallest element. No problem. Now delete aaa in A1 and the result is 0. No problem. Put back aaa into A1 and I get aaa. No problem. Now delete zzz in A10. I still get aaa and not 0 for the blank in A10. ??? I am missing something. Appreciate input. Epinn "T. Valko" wrote in message ... Try this: Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =INDEX(rng,MATCH(SMALL(COUNTIF(rng,"<"&rng),k),COU NTIF(rng,"<"&rng),0)) Where k = the kth element Biff "kjs" wrote in message ... =Small(A1:A5,2) from 12 15 12 10 17 returns the value 12, but what from def ghi qrs def abc returns "def" as the second lowest, by sort order or ascii value? I can get as far as =MIN(CODE(A1:A5)) entered as an array formula giving 97, the lowest ascii value of the first letter, but then I come off the rails. Any ideas? kjs |
|
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
The Kth element in a text list
Also, I have to go and study first and last value now.
I have a bunch of formulae (e.g. LOOKUP, COUNTIF) to determine last value and I have to go and check if yours is already there. I didn't mean the FIRST TEXT ENTRY or the LAST TEXT ENTRY in the range. I meant the first or last ordered alphabetically: Brown Smith Williams Adams Luce Adams would be the first and Williams would be the last. Of course, one could use the original formula I posted and just change the k parameter but the LOOKUP formula is much simpler. Biff "Epinn" wrote in message ... Sorry, KJS, I didn't expect I had so much to say. I should have started my own thread. If I want to discuss on first and last value I'll definitely start my own thread. Allow me to finish off with sorting on this thread. Biff, I am glad I said "If I understand correctly......" and you clarified. I almost miss the beauty of the formulae - *sort* by *dragging* and not just the first value. You know, I was so focused on nth element because it was how it all started. I think finally I understand every single word in your posts i.e. error trap, blanks at the end of the list, text, sort ...... Without dragging down, I missed so much. We use COUNTIF, < etc. because it is TEXT. Of course, we can use the same formula for numbers. But why bother, when I can just do =SMALL(C$1:C$10,ROWS($1:1)) for all numbers and no blanks. I don't really want to understand the reasoning/difference between the two. I think I have taken in a lot within the last 24 hours and I don't want to confuse myself any more. Also, I have to go and study first and last value now. I have a bunch of formulae (e.g. LOOKUP, COUNTIF) to determine last value and I have to go and check if yours is already there. Thanks for alerting me that I can flip "<" to "" to get a different order. When I first discovered this by myself (plus LARGE/SMALL), I was so delighted. Thank you for your wisdom and patience, my good teacher. Epinn "T. Valko" wrote in message ... I won't worry about the nth element in a range of text and numbers. Yeah, I can't think of a situation where you'd need to know the 10th element, but ya never know! Basically, the formula is meant to sort the entire range by drag copying. If you needed to know the first or last TEXT value (alphabetically) in a TEXT ONLY list: First: =LOOKUP(2,1/((COUNTIF(rng,"<"&rng)=0)*(rng<"")),rng) Or, an array formula: =INDEX(rng,MATCH(0,IF(rng<"",COUNTIF(rng,"<"&rng) ),0)) For the last, just flip the "<" to "". Biff "Epinn" wrote in message ... Don't get me wrong. You gave a perfect solution for the OP's request. Yes, I got carried away. I was "greedy" and I thought I could use that formula on numbers as well. It did seem to work when I had all numbers and no blanks. Okay, I won't drive myself crazy with Large/Small, "<" and "" etc. Forgive me and I shall give everyone and myself a break hopefully soon. Thanks for the wonderful gifts. If I understand correctly, both formulae return the smallest item. I won't worry about the nth element in a range of text and numbers. Epinn "T. Valko" wrote in message ... Don't get carried away with </ Large/Small! It can be confusing! <bg That particular formula only works with TEXT and I assumed the rng only contained TEXT. I didn't see any numbers and/or blank/empty cells represented in the sample. This formula works for TEXT only and accounts for blank/empty cells (no error trap) (array entered): =INDEX(rng,MATCH(SMALL(IF(rng<"",COUNTIF(rng,"<"& rng)),ROWS($1:1)),IF(rng<"",COUNTIF(rng,"<"&rng)) ,0)) This formula works for both TEXT and NUMBERS and accounts for blank/empty cells (array entered). Note: without the error trap any blank/empty cells will be included as 0's at the end of the list: =IF(ROWS($1:1)<=COUNTA(rng),INDEX(rng,MATCH(SMALL( COUNTIF(rng,"<"&rng&"")+COUNT(rng)*ISTEXT(rng)+100 000*ISBLANK(rng),ROWS($1:1)),COUNTIF(rng,"<"&rng&" ")+COUNT(rng)*ISTEXT(rng)+100000*ISBLANK(rng),0)), "") It will sort numbers first, then the text. Biff "Epinn" wrote in message ... One more thing I just discovered. LARGE and "" is exactly the same as SMALL and "<" i.e. ascending order. Epinn "Epinn" wrote in message ... Wow! This formula is so versatile. Thanks Biff for sharing. I had so much fun playing with it and came up with the following findings. Please correct me if I am wrong. Keep SMALL and change "<" to "", I get the kth element in descending order. Keep "<" and change SMALL to LARGE, I also get the kth element in descending order. With SMALL, "<" and k = 1, I get the smallest element. With LARGE, "<" and k = 1, I get the largest element. With SMALL, "" and k = 1, I also get the largest element. All this is good only when I have only numbers (right aligned) or only text (left aligned) in the range. If I have text and numbers in the range, I don't trust any of the above formulae. e.g. A1:A10 I only have zzz (text) or 999 (number). I get the same result regardless of whether I use SMALL or LARGE in the formula. I am keeping the "<" in the formula. I don't understand why. I also don't trust the formula when I have blanks. I only focus on all text or all numbers plus blanks in the range. e.g. =INDEX(rng,MATCH(SMALL(COUNTIF(rng,"<"&rng),1),COU NTIF(rng,"<"&rng),0)) rng is A1:A10. A1: aaa A10: zzz A2:A9 have other 3-letter combination in between. No blanks. The formula returns aaa as the smallest element. No problem. Now delete aaa in A1 and the result is 0. No problem. Put back aaa into A1 and I get aaa. No problem. Now delete zzz in A10. I still get aaa and not 0 for the blank in A10. ??? I am missing something. Appreciate input. Epinn "T. Valko" wrote in message ... Try this: Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =INDEX(rng,MATCH(SMALL(COUNTIF(rng,"<"&rng),k),COU NTIF(rng,"<"&rng),0)) Where k = the kth element Biff "kjs" wrote in message ... =Small(A1:A5,2) from 12 15 12 10 17 returns the value 12, but what from def ghi qrs def abc returns "def" as the second lowest, by sort order or ascii value? I can get as far as =MIN(CODE(A1:A5)) entered as an array formula giving 97, the lowest ascii value of the first letter, but then I come off the rails. Any ideas? kjs |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| drop down list and adding numeric values to text | Excel Discussion (Misc queries) | |||
| Cells within a list to display a certain text but have different v | Excel Discussion (Misc queries) | |||
| Removing leading decimals for text list | Excel Discussion (Misc queries) | |||
| Formulas dealing with text data | Excel Worksheet Functions | |||
| Counting Repeated text or duplicates in a list | Excel Discussion (Misc queries) | |||
Linear Mode
