Hi
I receive data (A1:E2) which is sorted alphabetically accordng to name:
Aaron Bob Con Dan Ed
3-Jan-13 5-Jan-13 3-Jan-13 1-Jan-13 2-Jan-13
I use the SMALL function to sort the data in date order e.g: A6= =SMALL($A$2:$E$2,1) = 1/1/13, B6= =SMALL($A$2:$E$2,5) = 2/1/13, etc:
1-Jan-13 2-Jan-13 3-Jan-13 3-Jan-13 5-Jan-13
I then use the INDEX & MATCH functions to place the names under the sorted dates e.g. A7= =INDEX($A$1:$E$1,MATCH(A6,$A$2:$E$2,0)) = "Dan", B7= =INDEX($A$1:$E$1,MATCH(B6,$A$2:$E$2,0)) = "Ed", etc:
1-Jan-13 2-Jan-13 3-Jan-13 3-Jan-13 5-Jan-13
Dan Ed Aaron Aaron Bob
The problem I have with this formula is that if there is a duplicate date (i.e. 3/1/13), the INDEX/MATCH formula returns a second "Aaron" rather than "Con".
Does anyone know how to modify this formula so it will return "Con" and not a second "Aaron"? Please note that duplicate dates may recur across the row.
I have searched this and other forms but the formulas always assume the data is in multiple rows rather than multiple columns.
Thanks
R
I receive data (A1:E2) which is sorted alphabetically accordng to name:
Aaron Bob Con Dan Ed
3-Jan-13 5-Jan-13 3-Jan-13 1-Jan-13 2-Jan-13
I use the SMALL function to sort the data in date order e.g: A6= =SMALL($A$2:$E$2,1) = 1/1/13, B6= =SMALL($A$2:$E$2,5) = 2/1/13, etc:
1-Jan-13 2-Jan-13 3-Jan-13 3-Jan-13 5-Jan-13
I then use the INDEX & MATCH functions to place the names under the sorted dates e.g. A7= =INDEX($A$1:$E$1,MATCH(A6,$A$2:$E$2,0)) = "Dan", B7= =INDEX($A$1:$E$1,MATCH(B6,$A$2:$E$2,0)) = "Ed", etc:
1-Jan-13 2-Jan-13 3-Jan-13 3-Jan-13 5-Jan-13
Dan Ed Aaron Aaron Bob
The problem I have with this formula is that if there is a duplicate date (i.e. 3/1/13), the INDEX/MATCH formula returns a second "Aaron" rather than "Con".
Does anyone know how to modify this formula so it will return "Con" and not a second "Aaron"? Please note that duplicate dates may recur across the row.
I have searched this and other forms but the formulas always assume the data is in multiple rows rather than multiple columns.
Thanks
R