converting question

D

Dav

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$7>0)*($A$2:$A$7),($A$2:$A$7)),$A2)),"",SMALL(IF((B$2:B$7>0)*($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
 
Top