Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to determine the text selection?
Does anyone have any suggestions on how to determine the text?
There is a list of data under column A and there is a list of text under column B For example [1] 1 APPLE [2] 1 BOY [3] 2 APPLE [4] 3 CAT [5] 1 CAT [6] 2 CAT [7] 3 APPLE There is a list of numbers under column A 1,1,2,3,1,2,3 There is a list of texts under column B, but the set order is based on following order: BOY (first priority on selection), APPLE (second priority on selection), CAT (third priority on selection) I would like to select the text according to the numbers based on higher priority for selection and return it under column C If any 1 under column A contain more than 3 types of text as shown above, then I prefer to select BOY in cell C1. [1] 1 APPLE [2] 1 BOY selected [5] 1 CAT If any 2 under column A contains APPLE (second), CAT (third) under column B, then I prefer to select APPLE in cell C1, because the APPLE has a higher priority for selection in cell C2. [3] 2 APPLE selected [6] 2 CAT If any 3 under column A contains APPLE (second), CAT (third) under column B, then I prefer to select CAT in cell C1, because the APPLE has a higher priority for selection in cell C3. [4] 3 CAT [7] 3 APPLE selected Does anyone have any suggestions? Thanks in advance for any suggestions Eric |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to determine the text selection?
One way to drive out the ascending list by priority using a tiebreaker criteria
With priority indications in A1 down, items in B1 down (as per post) In C1: =IF(A1="","",A1+ROW()/10^10) In D1: =IF(ROW()COUNT(C:C),"",INDEX(B:B,MATCH(SMALL(C:C, ROW()),C:C,0))) Copy C1:D1 down to cover the max expected of source data, say down to D50? Hide/minimize col C. Col D returns the required results. Items indicated with the same priority (ties) will appear in the same relative order that they are within the source. Any good? Hit the YES below -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "Eric" wrote: Does anyone have any suggestions on how to determine the text? There is a list of data under column A and there is a list of text under column B For example [1] 1 APPLE [2] 1 BOY [3] 2 APPLE [4] 3 CAT [5] 1 CAT [6] 2 CAT [7] 3 APPLE There is a list of numbers under column A 1,1,2,3,1,2,3 There is a list of texts under column B, but the set order is based on following order: BOY (first priority on selection), APPLE (second priority on selection), CAT (third priority on selection) I would like to select the text according to the numbers based on higher priority for selection and return it under column C If any 1 under column A contain more than 3 types of text as shown above, then I prefer to select BOY in cell C1. [1] 1 APPLE [2] 1 BOY selected [5] 1 CAT If any 2 under column A contains APPLE (second), CAT (third) under column B, then I prefer to select APPLE in cell C1, because the APPLE has a higher priority for selection in cell C2. [3] 2 APPLE selected [6] 2 CAT If any 3 under column A contains APPLE (second), CAT (third) under column B, then I prefer to select CAT in cell C1, because the APPLE has a higher priority for selection in cell C3. [4] 3 CAT [7] 3 APPLE selected |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to determine the text selection?
Thank you very much for suggestions
Could you please provide more detailed description on how to retrieve BOY for 1, APPLE for 2, and APPLE for 3? Thank you very much for any suggestions Eric "Max" wrote: One way to drive out the ascending list by priority using a tiebreaker criteria With priority indications in A1 down, items in B1 down (as per post) In C1: =IF(A1="","",A1+ROW()/10^10) In D1: =IF(ROW()COUNT(C:C),"",INDEX(B:B,MATCH(SMALL(C:C, ROW()),C:C,0))) Copy C1:D1 down to cover the max expected of source data, say down to D50? Hide/minimize col C. Col D returns the required results. Items indicated with the same priority (ties) will appear in the same relative order that they are within the source. Any good? Hit the YES below -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "Eric" wrote: Does anyone have any suggestions on how to determine the text? There is a list of data under column A and there is a list of text under column B For example [1] 1 APPLE [2] 1 BOY [3] 2 APPLE [4] 3 CAT [5] 1 CAT [6] 2 CAT [7] 3 APPLE There is a list of numbers under column A 1,1,2,3,1,2,3 There is a list of texts under column B, but the set order is based on following order: BOY (first priority on selection), APPLE (second priority on selection), CAT (third priority on selection) I would like to select the text according to the numbers based on higher priority for selection and return it under column C If any 1 under column A contain more than 3 types of text as shown above, then I prefer to select BOY in cell C1. [1] 1 APPLE [2] 1 BOY selected [5] 1 CAT If any 2 under column A contains APPLE (second), CAT (third) under column B, then I prefer to select APPLE in cell C1, because the APPLE has a higher priority for selection in cell C2. [3] 2 APPLE selected [6] 2 CAT If any 3 under column A contains APPLE (second), CAT (third) under column B, then I prefer to select CAT in cell C1, because the APPLE has a higher priority for selection in cell C3. [4] 3 CAT [7] 3 APPLE selected |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to determine the text selection?
Did the earlier work out for you?
I don't understand your additional/new query below -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "Eric" wrote: Thank you very much for suggestions Could you please provide more detailed description on how to retrieve BOY for 1, APPLE for 2, and APPLE for 3? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to determine the text selection?
There is the result for your suggestions as shown below, but I would like to
select BOY for 1 and return in cell E1, APPLE for 2 and return in cell E2, and APPLE for 3 and return in cell E3, there is missing the selection process based on your formula, which is the tough part and I don't know how to do it. Do you have any suggestions on how to do it in Excel? Thank you very much for any suggestions Eric [A] [b] [C] [D] 1 APPLE 1 APPLE 1 BOY 1 BOY 2 APPLE 2 CAT 3 CAT 3 APPLE 1 CAT 1.000000001 CAT 2 CAT 2.000000001 CAT 3 APPLE 3.000000001 APPLE "Max" wrote: Did the earlier work out for you? I don't understand your additional/new query below -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "Eric" wrote: Thank you very much for suggestions Could you please provide more detailed description on how to retrieve BOY for 1, APPLE for 2, and APPLE for 3? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to determine the text selection?
Ah, if you want to return the source col A as well into col E, just point the
INDEX part to col A, viz, in E1: =IF(ROW()COUNT(C:C),"",INDEX(A:A,MATCH(SMALL(C:C, ROW()),C:C,0))) Copy down. Solved? hit the YES below -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "Eric" wrote: There is the result for your suggestions as shown below, but I would like to select BOY for 1 and return in cell E1, APPLE for 2 and return in cell E2, and APPLE for 3 and return in cell E3, there is missing the selection process based on your formula, which is the tough part and I don't know how to do it. Do you have any suggestions on how to do it in Excel? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to determine the text selection?
Thank you very much for your suggestions
The set order is based on following order: BOY (first priority on selection), APPLE (second priority on selection), CAT (third priority on selection) so you cannot sort them based on TEXT, For sorting TEXT based on characters, "APPLE" is less than "BOY", but the given rule shows "BOY" is greater than "APPLE" and "APPLE" is greater than "CAT". For the C column, =IF(A1="","",A1+ROW()/10^10) which only add extra value at the end in order to differentiate the same numbers for ranking, but I would like to sort and rank the defined TEXT, not the number. All 1 under column A contains , APPLE (second), BOY (first), CAT (third) under column B, then I prefer to select BOY based on the given rules. [1] 1 APPLE [2] 1 BOY selected [5] 1 CAT For the number 1, which contains "APPLE","BOY" AND "CAT", based on the given rule, the largest priority is "BOY", because it is the first selection, so "BOY" will be selected and return in cell E1. If any 2 under column A contains APPLE (second), CAT (third) under column B, then I prefer to select APPLE in cell E2, because the APPLE has a higher priority for selection in cell E2. [3] 2 APPLE selected [6] 2 CAT For the number 2, which contains "APPLE" AND "CAT", based on the given rule, the larger priority is "APPLE", because it is the second selection, so "APPLE" will be selected and return in cell E2. If any 3 under column A contains APPLE (second), CAT (third) under column B, then I prefer to select CAT in cell E3, because the APPLE has a higher priority for selection in cell E3. [4] 3 CAT [7] 3 APPLE selected For the number 3, which contains "APPLE" AND "CAT", based on the given rule, the larger priority is "APPLE", because it is the second selection, so "APPLE" will be selected and return in cell E3. Doo you have any suggestions? Thank you very much for any suggestions Eric [A] [b] [C] [D] 1 APPLE 1 APPLE 1 BOY 1 BOY 2 APPLE 2 CAT 3 CAT 3 APPLE 1 CAT 1.000000001 CAT 2 CAT 2.000000001 CAT 3 APPLE 3.000000001 APPLE "Max" wrote: Ah, if you want to return the source col A as well into col E, just point the INDEX part to col A, viz, in E1: =IF(ROW()COUNT(C:C),"",INDEX(A:A,MATCH(SMALL(C:C, ROW()),C:C,0))) Copy down. Solved? hit the YES below -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "Eric" wrote: There is the result for your suggestions as shown below, but I would like to select BOY for 1 and return in cell E1, APPLE for 2 and return in cell E2, and APPLE for 3 and return in cell E3, there is missing the selection process based on your formula, which is the tough part and I don't know how to do it. Do you have any suggestions on how to do it in Excel? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to determine the text selection?
I'm out here. I don't know, maybe there's a chance that other responders
will understand you better, and offer you something. -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to determine the text? | Excel Discussion (Misc queries) | |||
Determine day of week in text format | Excel Discussion (Misc queries) | |||
Random Text selection Q | Excel Worksheet Functions | |||
Copy Selection - Transpose Selection - Delete Selection | Excel Discussion (Misc queries) | |||
losing text from text box when pasting selection to word | Excel Discussion (Misc queries) |