|
An Excel Formula know how to re-arrange the data
The 2 data sets are assumed running in A3 and in B3 down, viz.:
In A3 down is: A, B, C, D and E In B3 down is: 35, 45, 13, 8 and 3. The result which I want is to list the data E, D, C, A and B from the cell C3 to C7. I want a formula which know how to rank the data in which the Excel formula know B has the highest value of 45 and E has the lowest value of 3. Many thanks, Wilchong -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...l-new/200805/1 |
An Excel Formula know how to re-arrange the data
Try this entered in C3 and copied down:
=INDEX(A$3:A$7,MATCH(SMALL(B$3:B$7,ROWS(C$3:C3)),B $3:B$7,0)) If there are duplicate numbers then a different approach will be needed. -- Biff Microsoft Excel MVP "wilchong via OfficeKB.com" <u43231@uwe wrote in message news:84c26fc5dfe09@uwe... The 2 data sets are assumed running in A3 and in B3 down, viz.: In A3 down is: A, B, C, D and E In B3 down is: 35, 45, 13, 8 and 3. The result which I want is to list the data E, D, C, A and B from the cell C3 to C7. I want a formula which know how to rank the data in which the Excel formula know B has the highest value of 45 and E has the lowest value of 3. Many thanks, Wilchong -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...l-new/200805/1 |
An Excel Formula know how to re-arrange the data
Dear T. Valko,
Many thanks for your suggested formular! Really appreciate it! Wilchong T. Valko wrote: Try this entered in C3 and copied down: =INDEX(A$3:A$7,MATCH(SMALL(B$3:B$7,ROWS(C$3:C3)), B$3:B$7,0)) If there are duplicate numbers then a different approach will be needed. The 2 data sets are assumed running in A3 and in B3 down, viz.: In A3 down is: A, B, C, D and E [quoted text clipped - 10 lines] Wilchong -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...l-new/200805/1 |
An Excel Formula know how to re-arrange the data
Dear T. Valko
I have use your suggested formula and it has proven very efficient if there is no repeat value in B col. May I ask how to revise your suggested formula if A and B have the same value of 45? Many thanks, Wilson T. Valko wrote: Try this entered in C3 and copied down: =INDEX(A$3:A$7,MATCH(SMALL(B$3:B$7,ROWS(C$3:C3)), B$3:B$7,0)) If there are duplicate numbers then a different approach will be needed. The 2 data sets are assumed running in A3 and in B3 down, viz.: In A3 down is: A, B, C, D and E [quoted text clipped - 10 lines] Wilchong -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...l-new/200805/1 |
An Excel Formula know how to re-arrange the data
You're welcome!
-- Biff Microsoft Excel MVP "wilchong via OfficeKB.com" <u43231@uwe wrote in message news:84c33ed46a5ea@uwe... Dear T. Valko, Many thanks for your suggested formular! Really appreciate it! Wilchong T. Valko wrote: Try this entered in C3 and copied down: =INDEX(A$3:A$7,MATCH(SMALL(B$3:B$7,ROWS(C$3:C3)) ,B$3:B$7,0)) If there are duplicate numbers then a different approach will be needed. The 2 data sets are assumed running in A3 and in B3 down, viz.: In A3 down is: A, B, C, D and E [quoted text clipped - 10 lines] Wilchong -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...l-new/200805/1 |
An Excel Formula know how to re-arrange the data
Try this array formula** :
=INDEX(A$3:A$7,MATCH(SMALL(B$3:B$7+ROW(B$3:B$7)/10^10,ROWS(C$3:C3)),B$3:B$7+ROW(B$3:B$7)/10^10,0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "wilchong via OfficeKB.com" <u43231@uwe wrote in message news:84c3667d16b11@uwe... Dear T. Valko I have use your suggested formula and it has proven very efficient if there is no repeat value in B col. May I ask how to revise your suggested formula if A and B have the same value of 45? Many thanks, Wilson T. Valko wrote: Try this entered in C3 and copied down: =INDEX(A$3:A$7,MATCH(SMALL(B$3:B$7,ROWS(C$3:C3)) ,B$3:B$7,0)) If there are duplicate numbers then a different approach will be needed. The 2 data sets are assumed running in A3 and in B3 down, viz.: In A3 down is: A, B, C, D and E [quoted text clipped - 10 lines] Wilchong -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...l-new/200805/1 |
An Excel Formula know how to re-arrange the data
Dear T.Valko,
Many thanks for your advice! Wilchong T. Valko wrote: Try this array formula** : =INDEX(A$3:A$7,MATCH(SMALL(B$3:B$7+ROW(B$3:B$7)/10^10,ROWS(C$3:C3)),B$3:B$7+ROW(B$3:B$7)/10^10,0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Dear T. Valko I have use your suggested formula and it has proven very efficient if [quoted text clipped - 17 lines] Wilchong -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...l-new/200805/1 |
An Excel Formula know how to re-arrange the data
You're welcome!
-- Biff Microsoft Excel MVP "wilchong via OfficeKB.com" <u43231@uwe wrote in message news:84ce899a10002@uwe... Dear T.Valko, Many thanks for your advice! Wilchong T. Valko wrote: Try this array formula** : =INDEX(A$3:A$7,MATCH(SMALL(B$3:B$7+ROW(B$3:B$7 )/10^10,ROWS(C$3:C3)),B$3:B$7+ROW(B$3:B$7)/10^10,0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Dear T. Valko I have use your suggested formula and it has proven very efficient if [quoted text clipped - 17 lines] Wilchong -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...l-new/200805/1 |
| All times are GMT +1. The time now is 02:19 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com