Need to Create Bin Locations based on the following example

Hello, on the attached I’m trying to get to the best way to create bin locations. Please look at the example and let me know if it makes since. There will be 10 Bays, with 30 Bins in Each Bay, Level A thru E for Each Bin. All in Aisle 01. Please let me know your thoughts as this is way too much copy and pasting, and data entry, to do this manually. Thank you for all help in advance.

My Apologies, I was typing to fast, there is 3 bins in each bay not 30. Sorry for the confussion.

Originally Posted by JeteMc

Fairly involved to explain, however the process took a little over a minute to do.
Delete the number 10 from cell B28
Select cells C16:C20 and drag the fill handle of cell C20 down to cell C155,
Select cell A155 and simultaneously press the Ctrl, Shift and up arrow keys,
Simultaneously press Ctrl and D,
Select cell B6 and change the format from text to general,
Paste the following formula into the formula bar and press enter: =1*(INT(COUNTIFS(C$5:C5,"E")/3)+1)
Double click the fill handle of cell B6 to copy down,
Select cell D6 and change the format from text to general,
Paste the following formula into the formula bar and press enter: =COUNTIF(C$6:C6,"A")
Double click the fill handle of cell B6 to copy down.
Let us know if you have any questions.

Thanks a lot JeteMc, I appreciate the through explanation as i'm always trying to learn more about Excel. Thanks also for the working file.

One other question if possible JeteMc, how would I go about acheiving the following for each row?

Please Login or Register  to view this content.

Originally Posted by JeteMc

re: Post #5, Try pasting the following formula into cell E6:

Formula:

Please Login or Register  to view this content.

I have to chuckle at myself for the formula in column B, certainly it will work without the unneeded '1*' portion as in: =INT(COUNTIFS(C$5:C5,"E")/3)+1
re: Post #4, You're Welcome.Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

Thank You JeteMc, Appreciate the help! this will work