alexoigres
Board Regular
- Joined
- Dec 2, 2005
- Messages
- 184
<p>
I have a file with a lot of information and what I am trying to do is to find the top five ranking numbers between two columns. So I’m using this formula:
</p>
<p>
=LARGE(Wsheet!A:A,1) and
</p>
<p>
=INDEX(Wsheet!P:P,MATCH(LARGE(Wsheet!A:A,1),Wsheet!A:A,0),1)
</p>
<p>
Than I used the same the same idea but for the other column….. to get the other top 5
</p>
<p>
Then I apply the same idea on combine data and get the top 5 of both results…..
</p>
<p>
But this formula does not recognize priority on columns… for example…. C column do not have as you see on first table… if I add value on column C for BLUE
not its value is not 480 anymore but 40… as you see in second table…. What I want is the formula calculate that BLUE is not the top 5 because now we have
another data with value of 47 KEY…. So what I need is to have the table number three as result.. any ideas??
</p>
<table border="1" cellspacing="0" cellpadding="0">
<tbody>
<tr>
<td width="64" valign="top">
<p>
A
</p>
</td>
<td width="64" valign="top">
<p>
B
</p>
</td>
<td width="64" valign="top">
<p>
C
</p>
</td>
</tr>
<tr>
<td width="64" valign="top">
<p>
900
</p>
</td>
<td width="64" valign="top">
<p>
CAR
</p>
</td>
<td width="64" valign="top">
</td>
</tr>
<tr>
<td width="64" valign="top">
<p>
800
</p>
</td>
<td width="64" valign="top">
<p>
DOG
</p>
</td>
<td width="64" valign="top">
</td>
</tr>
<tr>
<td width="64" valign="top">
<p>
770
</p>
</td>
<td width="64" valign="top">
<p>
HOUSE
</p>
</td>
<td width="64" valign="top">
</td>
</tr>
<tr>
<td width="64" valign="top">
<p>
600
</p>
</td>
<td width="64" valign="top">
<p>
SKY
</p>
</td>
<td width="64" valign="top">
</td>
</tr>
<tr>
<td width="64" valign="top">
<p>
480
</p>
</td>
<td width="64" valign="top">
<p>
BLUE
</p>
</td>
<td width="64" valign="top">
</td>
</tr>
<tr>
<td width="64" valign="top">
</td>
<td width="64" valign="top">
</td>
<td width="64" valign="top">
</td>
</tr>
<tr>
<td width="64" valign="top">
<p>
47
</p>
</td>
<td width="64" valign="top">
<p>
KEY
</p>
</td>
<td width="64" valign="top">
</td>
</tr>
</tbody>
</table>
<table border="1" cellspacing="0" cellpadding="0">
<tbody>
<tr>
<td width="64" valign="top">
<p>
A
</p>
</td>
<td width="64" valign="top">
<p>
B
</p>
</td>
<td width="64" valign="top">
<p>
C
</p>
</td>
</tr>
<tr>
<td width="64" valign="top">
<p>
900
</p>
</td>
<td width="64" valign="top">
<p>
CAR
</p>
</td>
<td width="64" valign="top">
</td>
</tr>
<tr>
<td width="64" valign="top">
<p>
800
</p>
</td>
<td width="64" valign="top">
<p>
DOG
</p>
</td>
<td width="64" valign="top">
</td>
</tr>
<tr>
<td width="64" valign="top">
<p>
770
</p>
</td>
<td width="64" valign="top">
<p>
HOUSE
</p>
</td>
<td width="64" valign="top">
</td>
</tr>
<tr>
<td width="64" valign="top">
<p>
600
</p>
</td>
<td width="64" valign="top">
<p>
SKY
</p>
</td>
<td width="64" valign="top">
</td>
</tr>
<tr>
<td width="64" valign="top">
<p>
480
</p>
</td>
<td width="64" valign="top">
<p>
BLUE
</p>
</td>
<td width="64" valign="top">
<p>
40
</p>
</td>
</tr>
<tr>
<td width="64" valign="top">
</td>
<td width="64" valign="top">
</td>
<td width="64" valign="top">
</td>
</tr>
<tr>
<td width="64" valign="top">
<p>
47
</p>
</td>
<td width="64" valign="top">
<p>
KEY
</p>
</td>
<td width="64" valign="top">
</td>
</tr>
</tbody>
</table>
<table border="1" cellspacing="0" cellpadding="0">
<tbody>
<tr>
<td width="64" valign="top">
<p>
A
</p>
</td>
<td width="64" valign="top">
<p>
B
</p>
</td>
<td width="64" valign="top">
<p>
C
</p>
</td>
</tr>
<tr>
<td width="64" valign="top">
<p>
900
</p>
</td>
<td width="64" valign="top">
<p>
CAR
</p>
</td>
<td width="64" valign="top">
</td>
</tr>
<tr>
<td width="64" valign="top">
<p>
800
</p>
</td>
<td width="64" valign="top">
<p>
DOG
</p>
</td>
<td width="64" valign="top">
</td>
</tr>
<tr>
<td width="64" valign="top">
<p>
770
</p>
</td>
<td width="64" valign="top">
<p>
HOUSE
</p>
</td>
<td width="64" valign="top">
</td>
</tr>
<tr>
<td width="64" valign="top">
<p>
600
</p>
</td>
<td width="64" valign="top">
<p>
SKY
</p>
</td>
<td width="64" valign="top">
</td>
</tr>
<tr>
<td width="64" valign="top">
<p>
47
</p>
</td>
<td width="64" valign="top">
<p>
KEY
</p>
</td>
<td width="64" valign="top">
</td>
</tr>
<tr>
<td width="64" valign="top">
</td>
<td width="64" valign="top">
</td>
<td width="64" valign="top">
</td>
</tr>
<tr>
<td width="64" valign="top">
<p>
480
</p>
</td>
<td width="64" valign="top">
<p>
BLUE
</p>
</td>
<td width="64" valign="top">
<p>
40
</p>
</td>
</tr>
</tbody>
</table>
I have a file with a lot of information and what I am trying to do is to find the top five ranking numbers between two columns. So I’m using this formula:
</p>
<p>
=LARGE(Wsheet!A:A,1) and
</p>
<p>
=INDEX(Wsheet!P:P,MATCH(LARGE(Wsheet!A:A,1),Wsheet!A:A,0),1)
</p>
<p>
Than I used the same the same idea but for the other column….. to get the other top 5
</p>
<p>
Then I apply the same idea on combine data and get the top 5 of both results…..
</p>
<p>
But this formula does not recognize priority on columns… for example…. C column do not have as you see on first table… if I add value on column C for BLUE
not its value is not 480 anymore but 40… as you see in second table…. What I want is the formula calculate that BLUE is not the top 5 because now we have
another data with value of 47 KEY…. So what I need is to have the table number three as result.. any ideas??
</p>
<table border="1" cellspacing="0" cellpadding="0">
<tbody>
<tr>
<td width="64" valign="top">
<p>
A
</p>
</td>
<td width="64" valign="top">
<p>
B
</p>
</td>
<td width="64" valign="top">
<p>
C
</p>
</td>
</tr>
<tr>
<td width="64" valign="top">
<p>
900
</p>
</td>
<td width="64" valign="top">
<p>
CAR
</p>
</td>
<td width="64" valign="top">
</td>
</tr>
<tr>
<td width="64" valign="top">
<p>
800
</p>
</td>
<td width="64" valign="top">
<p>
DOG
</p>
</td>
<td width="64" valign="top">
</td>
</tr>
<tr>
<td width="64" valign="top">
<p>
770
</p>
</td>
<td width="64" valign="top">
<p>
HOUSE
</p>
</td>
<td width="64" valign="top">
</td>
</tr>
<tr>
<td width="64" valign="top">
<p>
600
</p>
</td>
<td width="64" valign="top">
<p>
SKY
</p>
</td>
<td width="64" valign="top">
</td>
</tr>
<tr>
<td width="64" valign="top">
<p>
480
</p>
</td>
<td width="64" valign="top">
<p>
BLUE
</p>
</td>
<td width="64" valign="top">
</td>
</tr>
<tr>
<td width="64" valign="top">
</td>
<td width="64" valign="top">
</td>
<td width="64" valign="top">
</td>
</tr>
<tr>
<td width="64" valign="top">
<p>
47
</p>
</td>
<td width="64" valign="top">
<p>
KEY
</p>
</td>
<td width="64" valign="top">
</td>
</tr>
</tbody>
</table>
<table border="1" cellspacing="0" cellpadding="0">
<tbody>
<tr>
<td width="64" valign="top">
<p>
A
</p>
</td>
<td width="64" valign="top">
<p>
B
</p>
</td>
<td width="64" valign="top">
<p>
C
</p>
</td>
</tr>
<tr>
<td width="64" valign="top">
<p>
900
</p>
</td>
<td width="64" valign="top">
<p>
CAR
</p>
</td>
<td width="64" valign="top">
</td>
</tr>
<tr>
<td width="64" valign="top">
<p>
800
</p>
</td>
<td width="64" valign="top">
<p>
DOG
</p>
</td>
<td width="64" valign="top">
</td>
</tr>
<tr>
<td width="64" valign="top">
<p>
770
</p>
</td>
<td width="64" valign="top">
<p>
HOUSE
</p>
</td>
<td width="64" valign="top">
</td>
</tr>
<tr>
<td width="64" valign="top">
<p>
600
</p>
</td>
<td width="64" valign="top">
<p>
SKY
</p>
</td>
<td width="64" valign="top">
</td>
</tr>
<tr>
<td width="64" valign="top">
<p>
480
</p>
</td>
<td width="64" valign="top">
<p>
BLUE
</p>
</td>
<td width="64" valign="top">
<p>
40
</p>
</td>
</tr>
<tr>
<td width="64" valign="top">
</td>
<td width="64" valign="top">
</td>
<td width="64" valign="top">
</td>
</tr>
<tr>
<td width="64" valign="top">
<p>
47
</p>
</td>
<td width="64" valign="top">
<p>
KEY
</p>
</td>
<td width="64" valign="top">
</td>
</tr>
</tbody>
</table>
<table border="1" cellspacing="0" cellpadding="0">
<tbody>
<tr>
<td width="64" valign="top">
<p>
A
</p>
</td>
<td width="64" valign="top">
<p>
B
</p>
</td>
<td width="64" valign="top">
<p>
C
</p>
</td>
</tr>
<tr>
<td width="64" valign="top">
<p>
900
</p>
</td>
<td width="64" valign="top">
<p>
CAR
</p>
</td>
<td width="64" valign="top">
</td>
</tr>
<tr>
<td width="64" valign="top">
<p>
800
</p>
</td>
<td width="64" valign="top">
<p>
DOG
</p>
</td>
<td width="64" valign="top">
</td>
</tr>
<tr>
<td width="64" valign="top">
<p>
770
</p>
</td>
<td width="64" valign="top">
<p>
HOUSE
</p>
</td>
<td width="64" valign="top">
</td>
</tr>
<tr>
<td width="64" valign="top">
<p>
600
</p>
</td>
<td width="64" valign="top">
<p>
SKY
</p>
</td>
<td width="64" valign="top">
</td>
</tr>
<tr>
<td width="64" valign="top">
<p>
47
</p>
</td>
<td width="64" valign="top">
<p>
KEY
</p>
</td>
<td width="64" valign="top">
</td>
</tr>
<tr>
<td width="64" valign="top">
</td>
<td width="64" valign="top">
</td>
<td width="64" valign="top">
</td>
</tr>
<tr>
<td width="64" valign="top">
<p>
480
</p>
</td>
<td width="64" valign="top">
<p>
BLUE
</p>
</td>
<td width="64" valign="top">
<p>
40
</p>
</td>
</tr>
</tbody>
</table>