Hi,

Am Thu, 31 Dec 2015 14:47:17 +0000 (UTC) schrieb tb:

For instance, say that I have the following racks numbers and bin
locations:

01-1
01-2
01-3
01-4
01-5
01-6
02-1
02-2
02-3
02-4
02-5
02-6
03-1
03-2
03-3
03-4
03-5
03-6

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.


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

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