I got there in two steps, if your data had been in a colums so ended up
a b c
2 1 1
4 2 3
6 4
5
The result would be quite straightforward, but as you then want it
transposed I had to use this as an interim step
Basically you use an array like
=IF(ISERROR(SMALL(IF((B$2:B$70)*($A$2:$A$7),($A$2 :$A$7)),$A2)),"",SMALL(IF((B$2:B$70)*($A$2:$A$7), ($A$2:$A$7)),$A2))
Endered with ctrl shift enter
In the spreadsheet a2:a7 are your values 1-6
b2:b7 is your range of data under 'A'
This can then be copied to produce the interim result above
then using the offset function switching rows and columns will give the
results in the desired form
something along the lines of
=OFFSET($B$9,COLUMN()-COLUMN($A$18),ROW()-ROW($A$18))
where the data to return starts in b10 and the first cell to be
populated with the result is b18
If you send me a message with your email address I will send you the
spreadsheet which is probably clearer
Regards
Dav
--
Dav
------------------------------------------------------------------------
Dav's Profile:
http://www.excelforum.com/member.php...o&userid=27107
View this thread:
http://www.excelforum.com/showthread...hreadid=514756