| Home |
| Search |
| Today's Posts |
|
#1
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
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
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
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
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
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
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
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| re-arrange cell data | Excel Discussion (Misc queries) | |||
| How do I arrange a formula between cells that are not together? | Excel Worksheet Functions | |||
| how to split data into columns and arrange the resulting data | Excel Discussion (Misc queries) | |||
| Excel should let me change the form data arrange direction | Excel Worksheet Functions | |||
| arrange data by criteria | New Users to Excel | |||
Linear Mode
