Formula For Batch Allocation Or Split Up?
Oct 2, 2013I need a formula for batch allocation automatically.
See the attached file : Batch_Allocation.xlsx‎
ADVERTISEMENT
Summarise Data After Applying Allocation Split
Feb 25, 2009The task involves summarising about 10,000 lines of data into months by Plant by Process. I can achieve it by stepping through a couple of schedules but I'm sure it can be done using one formula (but I can't find it!).
View 8 Replies View RelatedComing Up With Inventory Allocation Formula
Aug 4, 2014I've been assigned a project where I'm suppose to report inventory allocation to projects at end of day every Thursday. The past 2 Thursdays, I've just literally been manually plugging in the numbers into the allocated stock column.
The spreadsheet I came up with is attached. The 1st tab is the demand showing the needed quantities per item and respective due/on-site dates. The on-hand inventory (stock) is on the 2nd tab. My current process is:
1. once items have been shipped, I delete those entries/rows from the first tab. This way, only all the outstanding/open orders remain.
2. after completing #1, I then copy and paste on-hand inventory from our database (Navision) onto the 2nd tab.
3. I manually plug in the the qty for each item depending on stock available. This is the part that I would like to streamline.
Inventory Allocation.xlsx
Excel Formula To VBA Code Batch Convert
Jul 22, 2014I use a software to find relationship between some variables. this software can export results (excel mathematical formula) to a text or excel file. I need use this formulas in my VBA codes. but I should convert about 100-150 formula each time, hence this is very time consuming and hard to do that manually. I wondering if there is any macro code or third party software for that?
excel formulas are like this:
VB:
=((1/2)*((((EXP(EXP(EXP(-0.00159049260456001*$A1)))+2.36277505628942)/(LN(1.34876400300668*$B1))+(1/2)*((13.8900474312246)+(EXP(2.22254189839997*$C1)))))+(2.56256412775789*$D1/((1/2)*((1.34876400300668*$B1)+(EXP((1/2)*((EXP(-0.000633515788838835*$E1))+(-15.3575339156491)))))*(-15.3575339156491-1.31506071323009*$F1*EXP(EXP(2.22254189839997*$C1))))))*0.00300997948851126+-0.00736105020728091)
VBA For Stock Allocation
Jun 13, 2008Let say I have 5 shops: A, B, C, D, and E
They request for stock replenishment from warehouse as follows:
A: 4 pieces
B:2 pieces
C:3 pieces
D:3 piece
E:3 piece
But since the warehouse only have 12 pieces of this product so need to allocate to the shops in rounds that is:
Round 1: one piece is allocated to each shops (Stock left=12-5=7)
Round 2: one piece is allocated to each shops (Stock left=7-5=2)
Round 3: one piece each is allocated to A and C (the priority should be B but since B just requested for 2 pieces only which has been fulfilled) since there are no more stock
how to write the scripts to do this allocation.
Auto Allocation Of Data
Jun 29, 2013I have a list of 500 'cases' for staff to work on and I want to auto allocate them to people based on their skills. Whilst I can do this easily with each person have their own page in my workbook, I'm having the issue of the same data being replicated onto multiple sheets.
The main data is on one page and each person has their own individual page for their cases for the day. Is there any way to avoid multiples being sent out whilst creating a kind of 'priority ladder' as such?
Allocation Of Permutations In Excel
Nov 26, 2013I have trying to assign cards to respondents to view. I have 16 cards total which are grouped in sets of 4. Each set (of 4) is given a color, (red, green, blue, and yellow). Each color has a 4 different shades (S1, S2, S3, S4) so I have the following 16 cards:
1: Red_S1
2: Red_S2
3: Red_S3
4: Red_S4
5: Green_S1
6: Green_S2
7: Green_S3
8: Green_S4
9: Blue_S1
10: Blue_S2
11: Blue_S3
12: Blue_S4
13: Yellow_S1
14: Yellow_S2
15: Yellow_S3
16: Yellow_S4
I will have a total of 10 respondents giving their opinions who will each see 5 of the cards, randomly. Each respondents must see each color at least once (given that 5th card will be a repeated color). Each card must be tested 3 times (given that the 2 additional cards can be any of the cards).
If there a function or cell code that I can write so that I can allocate the card #s (1-16) across my 10 respondents?
Random Distribution/Allocation
Nov 1, 2009I have a spreadsheet which has a list of staff (column B) and work categories (row 3).
Row 4 is updated each day with a count of the incoming work for each work category.
I want to be able to randomly distribute (as evenly as possible) the no of work items in Row 4 between the doers (B7:B13). Each item of work also has to be checked so I would also like to randomly distribute the checking between the checkers (B16:B18)....
Time Allocation Calendar
Jul 12, 2007I am creating a time allocation / holiday tracking calendar. 150 employes (1 per column) and then using rows (10 - 375) as a daily track. I am having trouble with two aspects and would value your assistance.
1. i have locked the top 9 rows and would like row10 to jump automatically to todays date. i.e current info directly below the titles block
2. How do i make the calendar wrap around i.e. if someone puts in for a week holiday starting dec 31st 2007, how do i make the calendar roll round to 2008.
Changing Cost Allocation Macro?
May 23, 2012I am trying to write a Macro to allocate a certain cost across thousands of orders. The number of orders will obviously change monthly. The cost is relatively small, and when divided evenly by the total orders needs to be rounded up to the nearest cent (Typically $.01 - $.02)
I then need the cost to be insered in a colum running next to a total cost column, but I need it to stop when it reaches the total cost and just start adding zeros to the rest of the orders so as not to overcharge.
Not sure if I need to incorporate solver somehow. I figured I would reference the allocated cost from a cell in the worksheet but not sure how to insert any algebraic logic into a macro.
Fill Vehicle Allocation Table
Nov 26, 2006I am reposting this as my previous post was deleted.. i dont know the real reason for the same...
The raw data in the attachment contains:
•C1 to AI1 refers to vehicle numbers.
•Column A refers to Shift Time
•Column B refers to Number of vehicles required in that particulars Shift time
The output:
•As you can see in the Sheet “Output Required”, fields marked in the color “light green” are manually allocated vehicles numbers available and required according to shift time
•The logic used behind allocation is the same vehicle should not be used before or after within the span of 3 hrs, the gap between the vehicle usage before and after should be minimum 3 hrs. And the maximum time gap can be any value.
•Vehicle usage can be maximum (ie. You can use the same vehicle by rotating within 24 hrs but keeping in mind that it is not used before 3 hrs).
• all the available vehicles must be used before the same used vehicle is used in the new shift... ie. You should utilize all the vehicles before one is duplicated
Allocation By Daterange - Single Cell
Apr 27, 2007I m creating a single cell formula in 'report'B3 that would allocate 'data'D3 appropriately by month heading if copied across. A few other threads on this topic only acheive this by creating an addtional row, not in a single cell. Since I'm going to use this on a report in Access, I just need the calculation. Is it not possible to acheive this in a single cell?
View 2 Replies View RelatedRevenue Allocation Using Combined/Nested IF Statements
Jul 24, 2008I have been asked to go through some information which has over 200 000 lines, what I need to do is as follows:
where order number is unique, revenue source = "Unique"
where order number appears more than once, check division to see if it appears in more than one division, if it does revenue source = "Cross Selling", if it does not then revenue source = "Divisional Package"
I need all of the above in formula line which will be inserted in the revenue source column
I have attached the file with a sample of the information I am using.
VLookup With 2 Criteria - Provide Log Of Staff Allocation
Aug 31, 2012I'm fairly new to excel and trying to create a simple spreadsheet to provide a log of staff allocation. I've created a first a sheet displaying a table with a week of rota. The first column lists where the staff are placed, the first row the date and inbetween the staff member covering that date. There are 5 further sheets, one for each member of staff with a simple table - first column is date, second is allocation.
Back on the first sheet below the timetable is a further grid showing on the first column staff names, and below each date a formula =VLOOKUP( B$3,David!$A:$Z,2,FALSE) pulls information from the 5 sheets displying whether a memeber of staff is on leave or there allocation on that particular day.
The problem i'm having is trying to get the 5 simple staff sheet to pull data from the Rota ie. I need say B3 to look at the date in A3, find the same date on the Rota sheet, look for the relevent staff name below it (if present), and display the allocation from the first column on the same row as the staff member.
I've tried a combination of Vlookup with Match and Index with Match but nothing seem to work.
Input Allocation Percentage Dependent On Number Of Variables
May 15, 2014On the attached sheet in column X, I am trying to input the Allocation percentage dependent on a number of variables:
Type (Bar, Blank, Coil, Sheet, Special)
Sub Fam Code (2A02, BC02-05, BC99, BZ99)
Sup Category (Arcelor, Independent, Tata/ Corus)
The allocation percentage is based on the below; anything outside of this should result in "Other".
Sheet (BC02/03/04) A=40%, T=40% & I=20%
Coil (BC02 & BC03) A=40%, T=40% & I=20%
Coil (BC04) A=30%, T=50% & I=20%
Example 1:
Type - Sheet & Coil
Sup Category - Independent
Result 20%
Example 2:
Type - Coil
Sup Category - Tata/ Corus
Sub Fam Code - BC04
Result 50%
Example 3:
Type - Coil
Sup Category - Arcelor
Sub Fam Code - BC04
Result 30%
Excel 2010 :: Fails To Release Memory Allocation
Jan 27, 2014I am running Excel 2010 32bit under Windows 7. The Excel file worked perfectly under Excel 2007 and Windows XP. The Excel file is somewhat complex with numerous links and embedded cell formulas. Occasionally, I get the following error message:
5.jpg
Before I install the 64 bit version, I attempted to chack a few other things. I opened the task manager and while viewing the process tab, I opened my Excel file. Once the file loaded, this is the allocation displayed in task manager:
1.jpg
This seemed reasonable considering the size of the Excel file (33Mb). Without changing anything, I clicked save to make sure it was working properly. While viewing the process, I watched the allocation climb, substantially. when it completed, this is what the process displayed:
2.jpg
Continuing to click the save button, without changing anything, continued to increase the memory allocation until the error message was displayed:
3.jpg
4.jpg
Closing Excel and re-openeing the file resets the memory allocation back to normal, which is a popular solution to this issue in the forums.
I did check the same issue using Excel 2007 and the memory allocation releases after each save.
I can see how installing the 64 bit version of Excel can correct this issue, and with 12Gb of Ram, I will probably use this method until I can get this fixed.
Formula To Split Digits From End Of Cell?
Mar 9, 2012I'm struggling to find a good formula or code produce the below. I have a column that contains a text string followed by spaces then a number. I need to sparate these into two separate cells.
OldNew NewD-EUROBANK PROPERTIES REAL E 4D-EUROBANK PROPERTIES REAL E4D-ACTIVE INVESTMENT SA 12-SYNT 567D-ACTIVE INVESTMENT SA 12-SYNT567RSK HYDRO-NWKR 25(NORWAY REG 1RSK HYDRO-NWKR 25(NORWAY REG1ADRILL LTD 1ADRILL LTD 1OFFSHORE LIMITED 1OFFSHORE LIMITED 1TRATA PLC 2TRATA PLC 2
Formula To Split Characters In Cell
Jan 14, 2009In A1 cell i have the following text
"CAMPBELL # ERASCO O ZMKE # UNGARISCHE GULASCHSUPPE # 390 ML"
A2: CAMPBELL
A3: ERASCO O ZMKE
A4: UNGARISCHE GULASCHSUPPE
A5:390 ML
Split Contents In A Cell With A Formula
Jul 13, 2009I want to split the contents in a cell using a formula.
For Example, I have ***PavanKumar*** in A1
I want ***Pavan*** in A1 and ***Kumar*** in B1
Formula To Split Cell Contents
Aug 27, 2009Using XL07, some of my data looks like this
TY+/*-
PX/*-
TFM/*+
In an adjacent column, I am seeking to display the letters, numbers or symbols that appear before '/'. I would normally solve this little problem with something like =left(A5,2) or some combination of left and len. This method fails when the formula finds a len of 8 when the previous highest len was 7.
Split Text Across Columns Formula
Dec 9, 2006I got a question regarding a what formula can i use to act like the Split method in programming. I would like if this can be done with a formula not programming cos i am a programmer Basically i got a column full of data in the following format:.dddd.ddddddd.ddddd. Now the number of the "d" can be random in between the dots. So i would like to be able to split the line by looking at the "." .There is one thing though that each line does have 3 or 4 "." characters.
View 3 Replies View RelatedBatch Changes To Graphs
Mar 20, 2007How can I make batch changes to graphs in the same excel workbook (but on different worksheets?)?
In particular, if I have a bunch of graphs, how can I make bulk changes to color, size, axes titles, etc.?
Batch File
Oct 28, 2008I know this is an excel forum but i'm struggling with something. i need to create a batch file that maps a network drive so far my searches have uncovered this
View 3 Replies View RelatedSumif Formula Needs To Split 2 Criterias Of Addition
Aug 6, 2009you guys very kindly helped me with a spreadsheet a couple of months ago, but i now need to adapt it for another dept. I have completed as much as I can.
I need column C and E in the 'totals tab' to only calculate contract and upgrade sales respectively (found in 'service orders' tab). I also need Scott's and ash's individual sales to be calculated in corrisponding tabs. Most of the formulas are in place so just need them tweaked slightley.
Formula To Split Cell At First Break - Space
Nov 13, 2012Formula to split a cell at the first break/space and to keep the rest of the cell contents together? For example to separate addresses from the street and street name.
Eg.
A1
1111 AAAA BBB
into
B1 C1
111 AAAA BBB
I have tried to use text to columns but as the cell contains three or four words I don't want to have to rejoin cells afterwards.
Batch Image Saving
Sep 15, 2009Added pictures of the items to include on quotes (the pictures are in a column to the right of all the other details - (these pictures only exist in Excel)).
Our new quoting software tool needs a link to the filename - however, our pictures don't have logical filenames!
Is there a way we can do a batch save as of all these images so it ends up as 'part#'.jpg within folder X?
There are about 6,000 products and will be a bit of zombie work we don't really want to do!
Batch Tool Or Code
May 24, 2007i have about 500 excel files that are built to specific format, only some data changes.
I need to make same changes to all the 500 files, so i'm asking that is there any kind of tool (free/commecial) that can batch changes to all files?
SwitchBoard To Run Batch Files
Oct 11, 2007I have made a userform that works as a switchboard, and when you click on a button it should run a code that runs a batch file.
but for some reason the code does not work, can someone show me where i am going wrong...
Private Sub CommandButton1_Click()
Shell "c:dataexcel oolkit downloadscombine files.bat"
End Sub
Macro Batch Save As
Mar 29, 2002Is there a macro to batch save many xls files to text (prn) files?
View 9 Replies View RelatedFormula To Split Text Into Groups Of 30 Characters But Control As Well
May 28, 2014I have previous been given the below macro from here that splits a string of text in one cell into groups of 30 and then puts them into several cells, works perfectly.
What I would like to incorpate now is the ability to overide the point at which it splits the text.
i.e. if the inputter puts a "|" (for example) in the original text, this will cause a split in the text and it will restart its 30 count from this point onwards.
Example of what I would like to achieve:
Cell A1 = I would like to change this string of text into groups of 30, where this appears | I would like it to start a new split of 30s from this point on wards and again if another one of these | appears in the text.
Cell B1 = I WOULD LIKE TO CHANGE THIS
Cell C1 = STRING OF TEXT INTO GROUPS OF
Cell D1 = 30, WHERE THIS APPEARS
[Code]....