Thanks Max, that does the trick. Now I'll study for awhile to try and
understand your solution, and then see if I can get it to work on the real
data.

"Max" wrote:

One play to try ..

Assume source data in cols A to D (eg below), from row2 down, where the key
col = col B (110,90,130,..) whose values are to be autosorted in ascending
order with corresponding data in cols C and D following intact

1 110 8 7
2 130 11 8
3 90 5 6
4 110 4 4
etc

(I ignored col A which is just a serial numbering?)

In F2:
=IF(B2="","",B2+ROW()/10^10)
Leave F1 blank

In G2:
=IF(ROWS($1:1)COUNT($F:$F),"",INDEX(B:B,MATCH(SMA LL($F:$F,ROWS($1:1)),$F:$F,0)))
Copy G2 to I2. Select F2:I2, copy down to cover the max expected extent of
source data in cols B to D. Hide away col F. Cols G to I will return the
required results, all neatly bunched at the top.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"manxman" wrote:


"manxman" wrote:

Thanks for the help JLatham. I have used Vlookup extensively, but didn't
realize it could be used in that fashion. There is a problem in that the
real data table has duplicate values in Col A with unique values in Col B &
C. I have modified the illustration accordingly. Using Vlookup returns a
duplicate all the way across for Col A=110.

Modified data Desired result Result with Vlookup

A B C A B C A B C

1 110 8 7 20 90 5 6 20 90 5 6
2 130 11 8 21 110 8 7 21 110 8 7
3 90 5 6 22 110 4 4 22 110 8 7
4 110 4 4 23 130 11 8 23 130 11 8


So somehow we need to retrieve values for Col B & C that are unique to that
row.