Look up one value and return multiple corresponding values in exce

[Morphis]

Here is the outline - In Excel I have 6 columns, Column 1 has Numerical
Ranks from 1 -- 100 or whatever the figure. Column 2 has Ratings from say 25
down to whatever say 0 (there can be multiple values ie 7 of Rating 3).
Column 3 has text/string in my case names. Column 4 is identical to Column 1,
ie same Ranks. Column 5 has the same but not always the same number of
ratings. Column 6 has names and can be longer or shorter than Column 3. It is
NOT!! in the same order as Column 3 with some matching names having different
row numbers and can be new entries. NOTE: The names and ratings columns 2 & 3
and also 5 & 6 are always together but their values can change ie from
Columns 2 & 3 --> 5 & 6.

Question - I want to find matching!! names in Columns 3 & 6 then find the
relevant ranks from Columns 1 & 4, then subtract the value of Column 4 from
Column 1. Then find the MAX and MIN values. I would like to divide these
results according to the ratings column say greater than 10 and less or equal
to 10. The result should show 4 answers the highest and lowest difference in
ranks for ratings greater and less than or equal to 10 for each name/string.
Then I want to concentate the string name and new rank and ratings. I realise
new entries will show errors. Anyone have any advice, on how to approach
this?

 

[Morphis]

Here is the outline - In Excel I have 6 columns, Column 1 has Numerical
Ranks from 1 -- 100 or whatever the figure. Column 2 has Ratings from say 25
down to whatever say 0 (there can be multiple values ie 7 of Rating 3).
Column 3 has text/string in my case names. Column 4 is identical to Column 1,
ie same Ranks. Column 5 has the same but not always the same number of
ratings. Column 6 has names and can be longer or shorter than Column 3. It is
NOT!! in the same order as Column 3 with some matching names having different
row numbers and can be new entries. NOTE: The names and ratings columns 2 & 3
and also 5 & 6 are always together but their values can change ie from
Columns 2 & 3 --> 5 & 6.

Question - I want to find matching!! names in Columns 3 & 6 then find the
relevant ranks from Columns 1 & 4, then subtract the value of Column 4 from
Column 1. Then find the MAX and MIN values. I would like to divide these
results according to the ratings column say greater than 10 and less or equal
to 10. The result should show 4 answers the highest and lowest difference in
ranks for ratings greater and less than or equal to 10 for each name/string.
Then I want to concentate the string name and new rank and ratings. I realise
new entries will show errors. Anyone have any advice, on how to approach
this?

I really need some help or advice here..

 

[pete the greek]

i wonder if you could use vlookup
you would probably have to split your data into 2 named ranges the only
problem being the names column would have to be sorted ascending

you could probably do it with a macro that gets the name from column 3 and
gets the associated data from cols 1&2 then the macro would need to look in
column 6 for the name and pick up the associated data from col 4 and 5. once
you have all the data for a particular name the calulation should be possible
and the result placed where ever you want it.

 

[vezerid]

Morphis,
what you ask can probably be done, we just have to make sure we
understand. Is this how your data look like?

Rank1 Rating1 Name1 Rank2 Rating2 Name2
1 25 cat 1 25 dog
2 25 dog 2 24 lion
3 25 fly 3 24 APPLE
4 25 dog 4 23 cat
5 24 bird 5 23 ORANGE
6 23 lion 6 23 duck
7 23 dog 7 22 PEACH

What is confusing me most is what you mean by MAX here. dog in F2 has
rank 1. If we find the three dog's in column C:C, they have ranks 2, 4
and 7. You want the MAX of what? 2-1, 4-1 and 7-1? Or do you also have
duplicates in column F:F? But if so, how do you intend to match and
entry in Name1 with an entry in Name2, especially if the populations
are not equal?

Please reply with amendments to the data and more clear explanation of
what you want.

HTH
Kostis Vezerides

 

[Morphis]

Morphis,
what you ask can probably be done, we just have to make sure we
understand. Is this how your data look like?

Rank1 Rating1 Name1 Rank2 Rating2 Name2
1 25 cat 1 25 dog
2 25 dog 2 24 lion
3 25 fly 3 24 APPLE
4 25 dog 4 23 cat
5 24 bird 5 23 ORANGE
6 23 lion 6 23 duck
7 23 dog 7 22 PEACH

What is confusing me most is what you mean by MAX here. dog in F2 has
rank 1. If we find the three dog's in column C:C, they have ranks 2, 4
and 7. You want the MAX of what? 2-1, 4-1 and 7-1? Or do you also have
duplicates in column F:F? But if so, how do you intend to match and
entry in Name1 with an entry in Name2, especially if the populations
are not equal?

Please reply with amendments to the data and more clear explanation of
what you want.

HTH
Kostis Vezerides

Kostis this is close, except for the following:

Your Rank and Ratings columns are correct, however the names columns are
such that for each of the names column there is only one unique name for your
example in column 3 dog would only appear once. For Column 6 there will be
some names from Column 3 but more than likely NOT on the same row and with
new entries which I would presume would display an error message. Again for
your example let's take Cat he is Rank 1 from the first 3 columns and Rank 4
in the last 3 columns I want to find the difference in rank for Cat = -3 and
lets also take Lion he would be = 4 then display the MAX and MIN for the
column that would display -3 so there would be 2 results a MAX and MIN
(4,-3). Then from your example again for your example let's take 24 as the
cutoff point for Ratings I want to find MAX and MIN for > 24 and MAX and MIN
for <= 24 in Column 5 so all together there should be 4 results. It is
important to realise that the names in columns 3 & 6 are unique but can be
duplicated from each column. Hope that explains things more clearly.

M

 

[vezerid]

OK, I got it. I tried to do it without an auxiliary column but it is
too late and it is not working, so quickly you will have to settle for
an extra column next to the data. This formula, starting at G2:

=IF(ISNUMBER(MATCH(F2,$C$2:$C$8,0)),INDEX($A$2:$A$8,MATCH(F2,$C$2:$C$8,0))-D2,"")

It will leave either a blank or the difference of the rank of a name
(rank1-rank2). I.e. you are driven by Name2 as I understand from all
your posts. Copy this formula as far down as necessary. Then:

Unconditional Max/min:
=MAX(G2:G8) =MIN(G2:G8)

Conditional Max/min (ARRAY formula)
=MAX(IF(E2:E8>24,G2:G8)) =MIN(IF(E2:E8>24,G2:G8))

and
=MAX(IF(E2:E8<=24,G2:G8)) =MIN(IF(E2:E8<=24,G2:G8))

Array formulas must be committed with Shift+Ctrl+Enter

I will be going now, so I will revisit the thread in the morning. I
will see if I can find a solution without using the extra column, but
it seems we have hit something sticky here.

HTH
Kostis

 

[Morphis]

OK, I got it. I tried to do it without an auxiliary column but it is
too late and it is not working, so quickly you will have to settle for
an extra column next to the data. This formula, starting at G2:

=IF(ISNUMBER(MATCH(F2,$C$2:$C$8,0)),INDEX($A$2:$A$8,MATCH(F2,$C$2:$C$8,0))-D2,"")

It will leave either a blank or the difference of the rank of a name
(rank1-rank2). I.e. you are driven by Name2 as I understand from all
your posts. Copy this formula as far down as necessary. Then:

Unconditional Max/min:
=MAX(G2:G8) =MIN(G2:G8)

Conditional Max/min (ARRAY formula)
=MAX(IF(E2:E8>24,G2:G8)) =MIN(IF(E2:E8>24,G2:G8))

and
=MAX(IF(E2:E8<=24,G2:G8)) =MIN(IF(E2:E8<=24,G2:G8))

Array formulas must be committed with Shift+Ctrl+Enter

I will be going now, so I will revisit the thread in the morning. I
will see if I can find a solution without using the extra column, but
it seems we have hit something sticky here.

HTH
Kostis

Thanks Kostis works perfectly. Only thing that would be handy is there a way
for the results (4 values) could be linked or to return the corresponding
name in column 6? It just saves time scrolling to find the corresponding name
for the particular values. Perhaps highlight the name or something. Thanks
again.

M