Hi,

Am Thu, 31 Dec 2015 19:34:14 +0100 schrieb Claus Busch:

if your racks and bins are sorted as your example then try im B1:
=IF(--RIGHT(A1,1)<5,ROW(),SUMPRODUCT(--(--RIGHT($A$1:$A$18,1)<5),--($A$1:$A$18<""))+ROW())
and copy down. Then sort by column B.

the formula is longer but more reliable:
=IF(--RIGHT(A1,1)<5,LEFT(A1,FIND("-",A1)-1)*10+RIGHT(A1,1),LEFT(A1,FIND("-",A1)-1)*100*SUMPRODUCT(N(--RIGHT($A$1:$A$25,1)<5))+MID(A1,FIND("-",A1)+1,99))

Note that the range in the SUMPRODUCT section must be exactly the range
with data.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional