Consecutive and Repeating Bin Numbers in Excel

Tarver

Board Regular
Joined
Nov 15, 2012
Messages
109
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
I'm trying to print warehouse bin tags. There are multiple towers that need these labels. Each Tower is eight tiers (shelves) high. The bottom tier (Tier 1) and the top Tier (Tier 8) are divided into three bins, but all other Tiers (Tier 2 - Tier 7) are divided into only two bins.

My bin numbers should show the Tower number, the Tier number, then the Bin, so for example the 5th tower, 8th tier should show three bins 05-08-01, 05-08-02, and 05-08-03.

I need to create an Excel formula that calculates these bin numbers in a column, so that my output looks like this:

04-08-02
04-08-03
05-01-01
05-01-02
05-01-03
... etc., etc.
05-07-01
05-07-02
05-08-01
05-08-02
05-08-03
06-01-01

Is there a solution to doing this where I can use formulas only?
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
MrExcelPlayground12.xlsx
ABCDEFGHI
1Towers6
2Tier12345678
3Bins32222223
40357911131518
5
601-01-0101-01-01111101
701-01-0201-01-02212102
801-01-0301-01-03313103
901-02-0101-02-01414231
1001-02-0201-02-02515232
1101-03-0101-03-01616351
1201-03-0201-03-02717352
1301-04-0101-04-01818471
1401-04-0201-04-02919472
1501-05-0101-05-0110110591
1601-05-0201-05-0211111592
1701-06-0101-06-01121126111
1801-06-0201-06-02131136112
1901-07-0101-07-01141147131
2001-07-0201-07-02151157132
2101-08-0101-08-01161168151
2201-08-0201-08-02171178152
2301-08-0301-08-03181188153
2402-01-0102-01-011921101
2502-01-0202-01-022022102
2602-01-0302-01-032123103
2702-02-0102-02-012224231
2802-02-0202-02-022325232
2902-03-0102-03-012426351
3002-03-0202-03-022527352
3102-04-0102-04-012628471
3202-04-0202-04-022729472
3302-05-0102-05-0128210591
3402-05-0202-05-0229211592
3502-06-0102-06-01302126111
3602-06-0202-06-02312136112
3702-07-0102-07-01322147131
3802-07-0202-07-02332157132
3902-08-0102-08-01342168151
4002-08-0202-08-02352178152
4102-08-0302-08-03362188153
4203-01-0103-01-013731101
4303-01-0203-01-023832102
4403-01-0303-01-033933103
Sheet27
Cell Formulas
RangeFormula
B4B4=B3
C4:I4C4=B4+C3
A6:A113A6=TEXT((INT((SEQUENCE(B1*SUM(B3:I3))-1)/18)+1),"00")&"-"&TEXT(XLOOKUP((MOD((SEQUENCE(B1*SUM(B3:I3))-1),18)+1),B4:I4,B2:I2,,1),"00")&"-"&TEXT(((MOD((SEQUENCE(B1*SUM(B3:I3))-1),18)+1)-XLOOKUP((MOD((SEQUENCE(B1*SUM(B3:I3))-1),18)+1),B4:I4,A4:H4,,1)),"00")
C6:C113C6=TEXT(E6#,"00")&"-"&TEXT(G6#,"00")&"-"&TEXT(I6#,"00")
D6:D113D6=SEQUENCE(B1*SUM(B3:I3))
E6:E113E6=(INT((D6#-1)/18)+1)
F6:F113F6=(MOD((D6#-1),18)+1)
G6:G113G6=XLOOKUP(F6#,B4:I4,B2:I2,,1)
H6:H113H6=XLOOKUP(F6#,B4:I4,A4:H4,,1)
I6:I113I6=(F6#-H6#)
Dynamic array formulas.
 
Upvote 0
Solution
This one long formula should work...
Excel Formula:
=TEXT(FLOOR((ROW(A1)-1)/18,1)+1,"00")&"-"&TEXT(LOOKUP(ROW(A1)/18-INT(ROW(A1)/18),{0,0.01,0.2,0.3,0.4,0.51,0.62,0.75,0.85},{8,1,2,3,4,5,6,7,8}),"00")&"-"&TEXT(LOOKUP(ROW(A1)/18-INT(ROW(A1)/18),{0,0.01,0.1,0.15,0.2,0.25,0.3,0.35,0.4,0.5,0.55,0.6,0.65,0.7,0.75,0.8,0.85,0.9},{3,1,2,3,1,2,1,2,1,2,1,2,1,2,1,2,1,2}),"00")
 
Upvote 0
Only way i could think of:

=LET(x,IF(MOD(SEQUENCE(180,1),18)=0,18,MOD(SEQUENCE(180,1),18)),TEXT(ROUNDDOWN(SEQUENCE(180,1,1,1/18),0),"00-")&TEXT(MID("111223344556677888",x,1),"00-")&TEXT(MID("123121212121212123",x,1),"00"))

Does you 10 towers.
 
Upvote 0
BOOM! That's outstanding! I can't thank you enough for taking the time to work that out.

I'm ashamed of the amount of time I put into this without being able to come up with a good answer. It's taken me several hours to get nowhere. Great job!
 
Upvote 0
This one long formula should work...
Excel Formula:
=TEXT(FLOOR((ROW(A1)-1)/18,1)+1,"00")&"-"&TEXT(LOOKUP(ROW(A1)/18-INT(ROW(A1)/18),{0,0.01,0.2,0.3,0.4,0.51,0.62,0.75,0.85},{8,1,2,3,4,5,6,7,8}),"00")&"-"&TEXT(LOOKUP(ROW(A1)/18-INT(ROW(A1)/18),{0,0.01,0.1,0.15,0.2,0.25,0.3,0.35,0.4,0.5,0.55,0.6,0.65,0.7,0.75,0.8,0.85,0.9},{3,1,2,3,1,2,1,2,1,2,1,2,1,2,1,2,1,2}),"00")
Great job! I like this solution a lot!
 
Upvote 0
Book1
ABCDEFGHIJK
101-01-0102-01-0103-01-0104-01-0105-01-0106-01-0107-01-0108-01-0109-01-0110-01-01
201-01-0202-01-0203-01-0204-01-0205-01-0206-01-0207-01-0208-01-0209-01-0210-01-02
301-01-0302-01-0303-01-0304-01-0305-01-0306-01-0307-01-0308-01-0309-01-0310-01-03
401-02-0102-02-0103-02-0104-02-0105-02-0106-02-0107-02-0108-02-0109-02-0110-02-01
501-02-0202-02-0203-02-0204-02-0205-02-0206-02-0207-02-0208-02-0209-02-0210-02-02
601-02-0302-02-0303-02-0304-02-0305-02-0306-02-0307-02-0308-02-0309-02-0310-02-03
701-03-0102-03-0103-03-0104-03-0105-03-0106-03-0107-03-0108-03-0109-03-0110-03-01
801-03-0202-03-0203-03-0204-03-0205-03-0206-03-0207-03-0208-03-0209-03-0210-03-02
901-03-0302-03-0303-03-0304-03-0305-03-0306-03-0307-03-0308-03-0309-03-0310-03-03
1001-04-0102-04-0103-04-0104-04-0105-04-0106-04-0107-04-0108-04-0109-04-0110-04-01
1101-04-0202-04-0203-04-0204-04-0205-04-0206-04-0207-04-0208-04-0209-04-0210-04-02
1201-04-0302-04-0303-04-0304-04-0305-04-0306-04-0307-04-0308-04-0309-04-0310-04-03
1301-05-0102-05-0103-05-0104-05-0105-05-0106-05-0107-05-0108-05-0109-05-0110-05-01
1401-05-0202-05-0203-05-0204-05-0205-05-0206-05-0207-05-0208-05-0209-05-0210-05-02
1501-05-0302-05-0303-05-0304-05-0305-05-0306-05-0307-05-0308-05-0309-05-0310-05-03
1601-06-0102-06-0103-06-0104-06-0105-06-0106-06-0107-06-0108-06-0109-06-0110-06-01
1701-06-0202-06-0203-06-0204-06-0205-06-0206-06-0207-06-0208-06-0209-06-0210-06-02
1801-06-0302-06-0303-06-0304-06-0305-06-0306-06-0307-06-0308-06-0309-06-0310-06-03
1901-07-0102-07-0103-07-0104-07-0105-07-0106-07-0107-07-0108-07-0109-07-0110-07-01
2001-07-0202-07-0203-07-0204-07-0205-07-0206-07-0207-07-0208-07-0209-07-0210-07-02
2101-07-0302-07-0303-07-0304-07-0305-07-0306-07-0307-07-0308-07-0309-07-0310-07-03
2201-08-0102-08-0103-08-0104-08-0105-08-0106-08-0107-08-0108-08-0109-08-0110-08-01
2301-08-0202-08-0203-08-0204-08-0205-08-0206-08-0207-08-0208-08-0209-08-0210-08-02
2401-08-0302-08-0303-08-0304-08-0305-08-0306-08-0307-08-0308-08-0309-08-0310-08-03
25
Sheet7
Cell Formulas
RangeFormula
A1:J24A1=LET(s,TRANSPOSE(SEQUENCE(10,24)),TEXT(INT((s-0.1)/24)+1,"00") & "-" & TEXT(MOD(INT((s-0.1)/3),8)+1,"00") & "-" & TEXT(MOD(s-1,3)+1,"00"))
Dynamic array formulas.
 
Upvote 0
That doesnt do as required JGordon
I just did it like that so it would show all the output without having to scroll ten pages to see the formula. If needed in a single column just change then just change TRANSPOSE(SEQUENCE(10,24)) to SEQUENCE(240)
 
Upvote 0
I just did it like that so it would show all the output without having to scroll ten pages to see the formula. If needed in a single column just change then just change TRANSPOSE(SEQUENCE(10,24)) to SEQUENCE(240)
Sure but it still doesnt do as required. For example there should be no 01-02-03
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,205
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top