Adding info
I have these locations in a spreadsheet : S23160A1
S23161A1
I want to add more locations to look like this : S23160A1
S23160A2
S23160A3
S23160A4
S23160A5
S23160B1
S23160B2
S23160B3
S23160B4
S23160B5
The letters will also include C and D
I have 9000 locations is there a way I can do this without doing them one at a time?
Thanks
Last edited by norm01; 01-11-2011 at 02:16 PM.
Lack of response general indicates confusion / lack of clarity, it might be an idea to post a sample file to better illustrate requirements.
I'm guessing that A1,A2 etc might be cell references and perhaps the prefix is a sheet name but that's (at best) guesswork on my part ...
Alternatively, you could be looking to increment the strings following this 1-5 A-D pattern
Either way, more info. please.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Sorry for the confusion
These are racking locations, I need to expand on each location
so S23160A1 needs to expand to S23160A2 S23160A3 S23160A4 S23160A5 and then S23161A1 would need the same and so on for the other 9000 locations
Bottom line I am taking the one location and expanding it to 5
I am looking for an easier way to do this other than one at a time
And the 9000 locations are listed where exactly ?
At the moment we have no idea where the data is nor where the expanded locations are to be listed - this makes it hard for us to offer you anything meaningful I'm afraid.
Here's my take on it. They have a list in column A like so;
S23160A1
S23161A1
....
Looking at each entry in A as the first entry in a series of 20 where the last digit runs from 1 to 5 and the letter A goes up to D, expand the series in another column.
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Let's get the ball rolling
Try this nonsense, it will generate the Codes Numbers you ask for.
With your existing Code Numbers in A2 Down
In C2
Drag Down to Row 21
Please Login or Register to view this content.
Then across as required
You can then Copy the result and Paste Special > Values
Finally drag each column into one single column if that is what you need..
Last edited by Marcol; 01-10-2011 at 05:29 PM. Reason: Wrong attachment now corrected
If you need any more information, please feel free to ask.
However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....
Also
اس کی مدد کرتا ہے اگر
شکریہ کہنے کے لئے سٹار کلک کریں
If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.
@Marcol, if as implied there are 9000 x 20 and OP is using 2003 then you need to transpose your approach.
On that basis, assuming a setup per ChemistB's post then another alternative:
That said I am not sure 180k formula is necessarily the best way forward... VBA Arrays would normally make sense when dealing with that type of volume.Please Login or Register to view this content.
WOW, Thank you everyone,
Before I try all these solutions I want to finish up the single locations
Heres the problem with this
Columns A thru F
A B C D E F
Row 2 : B 10 010 A 1 B1010A 1
When I concatenate A2/B2/C2/D2/E2 in F2, I lose the first "0" in C2
I have attached a file of both problems
Okay, let's take this back to basics, Ground Zero I believe is the current parlance.
1/. You have a storage facility with approximately 9000 racks, These I presume currently have some sort of coding system.
Can you list these codes?
2/. Are you meaning that they have to be sub-divided, or is the warehouse facility expanding to such a size that you need at least 6 times as many racks as indicated by Sheets("ADDING LOCATIONS")?
3/. Let's assume that you are not working for Amazon and you want to sub-divide these racks.
What is the maximum bays each rack can have?
I assume that a rack is a level on one side of an aisle, and a bay is a division between physical columns or permanent uprights.
Having established these ground rules we might get somewhere.
In my experience of warehousing it will be very unlikely that the existing coding system will be consistant, it may have started that way, but I bet it has deteriorated to a point where you need to re-think the system.
How about posting a workbook that clarifies these points?
In the spreadsheet I attached (Book1) the concatenate problem is the bulk floor locations for fast moving product I need the zeros in front of the numbers for the barcodes to be scaned into out AS 400 WM system
You are right about the racking we need to divide each level of the bay (6 Levels) into 3 or 5 sub locations there in lies the problem, I can spend the hours and hours doing this by hand but there has to be a faster way with formulas to add these locations into the spreadsheet that contains the original location
So Bay S10-001-A-1 would be the bottom right corner of the bay and across the bottom would be A2,A3,A4,A5 and the level above would be S10-001-B1 , B2, B3, B4 ,B5 and so on
Thanks for sticking with this problem
Cheers!!!
Okay
Starting with the concatenate problem
Try in F1
Drag/Fill DownPlease Login or Register to view this content.
Is that what you need? (An 8 character code)
Yes that is what I needed...One thing though B1 needs to keep the zero as well (01)
I don't follow this
I get B10001A1 in F1....B1 needs to keep the zero as well (01)
What should it be?
Thats correct but if the aisle needs to start at S01 instead of S10 i will still need the zero in the left place
Give this a try
The formula in H2 Can be Dragged across to Column P if required, after that it will produce a code with more than 8 characters, I don't think you need that.
All can be dragged down as far as required.
To change the codes follow the prompts in the yellow cells.
If this generates the correct codes, we can move on from here.
This should probably replace Post#16
I have added a sheet that attempts to list the codes for any single rack.
In this sheet (Sheets("LAYOUT DETAIL")) I have attempted to diagramatically show an elevation of the rack, and my interpretation of a section layout.
1/. Is each bay accessible from adjacent aisles as shown?
2/. Is the rack numbering progression correct?
Don't worry about the formulae they are just there to make this sheet work to a degree and will probably be redundant in the final solution.
The information in Post 16 worked very well, Due to some Bays having different numbers of sub locations I was able to use that template and copy and paste to a new sheet what I needed and was able to "text to column" that info for download into AS 400
Thanks you very much for all you help
Cheers!!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks