How to determine the text selection?

E

Eric

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
 
M

Max

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
 
E

Eric

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 said:
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 said:
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
Click to expand...
Click to expand...
 
E

Eric

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] [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
 
M

Max

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
 
E

Eric

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to determine the text? 3
How to determine the value? 3
How to determine the value? 5
How to choose maximum value 7
How to determine the number of types? 5
how do i find out what is uncommon in column list A1: A10 and B1:. 1
How to determine the text? 2
How to determine the value within specific cell position? 5

Top