|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
May 27th, 2008, 04:50 AM
posted to microsoft.public.excel.newusers
|
|||
|
|||
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 
|
|
#2
May 27th, 2008, 05:37 AM
posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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
|
|
#3
May 27th, 2008, 06:23 AM
posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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
|
|
#4
May 27th, 2008, 06:40 AM
posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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
|
|
#5
May 27th, 2008, 06:41 AM
posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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
|
|
#6
May 27th, 2008, 05:15 PM
posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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
|
|
#7
May 28th, 2008, 03:56 AM
posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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
|
|
#8
May 28th, 2008, 04:29 AM
posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
