Fill Vehicle Allocation Table
Nov 26, 2006
I 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
View 9 Replies
ADVERTISEMENT
Oct 15, 2007
Basically, i want to work out the age of a large list of vehicles based on its uk registration plate number but have so far been completely out of luck,
View 13 Replies
View Related
Nov 22, 2011
If you have a data in two dimensional table. and you need to fill each cell with the value returned from another table.
Example : this the my table
Company A Compay B Company C Product 1
Product 2
Product 3
Product 4
and this my data :
Products Companies Price
Product 1 Company 8 Product 1 Company A 7 Product 1 Company B 5 Product 1 Company C 6 Product 2 Company B 6 Product 2 Company C 9 Product 3 Company A 10 Product 3 Company B 8.8
View 5 Replies
View Related
Aug 29, 2007
I currently have a table with a range of headings (row & column), and the necessary data for it. On a new worksheet, I have a table with only a few of the headings, and I was wondering if there was a macro that would automaticlly match the headings of the new sheet with the other table, and fill in the ncessary dat, as on the other sheet.
View 3 Replies
View Related
Feb 26, 2014
Mega master.xlsx
On the attached excel sheet I have a 5 week period, and two overall pages (one for the drivers, one for the trucks). My question is this. Drivers may drive different vehicles throughout the week, how, on the Truck Overall page do I calculate the formula so it deciphers each truck and puts the correct information into the right boxes?
View 5 Replies
View Related
Aug 31, 2009
I have an 'existing results table' as per my attached sample. From this table I need to create a list of 'sold' for each annual date range - i.e. I need a separate list for items sold within that date range. see my example in red. *Note. I am working with Excel07 however my attached sample is in Excel03 because I couldn't upload an XLSX file.
View 4 Replies
View Related
Jan 15, 2013
I have a problem regarding making a report in excel.
My problem is :
I have a excel file where columns are
Collapse | Copy Code
S.no. Date. From To Mode_of_Transport Depart. Arrival NameRemarks
So my job is to find the persons who arrived at interval of 30 min so that vehicle can be arranged for them.
View 5 Replies
View Related
Nov 26, 2008
I have a lookup table (see attached file) which holds the min & max value for a vehicle, the min & max size of the engine and the premium. I want a function that will search for the value of the vehicle (e.g. 100000 in cell H5) within the min value and max value columns as well as the engine size (e.g. 2000 in cell H6) in the min size & max size columns return the premium from the associated premium (which should be 13,200).
View 2 Replies
View Related
Oct 17, 2009
I need a function to return a boolean T/F to check if a 17 character string meets the following checksum validation.
This is for Vehicle Identification Numbers for vehicle made after 1981
I need a VBA function that I can call multiple times in a workbook,
Example =VIN(A1) which returns True or False.
First, find the numerical value associated with each letter in the VIN. (I, O and Q are not allowed.) Digits use their own values.
A->1B->2C->3D->4E->5F->6G->7H->8
J->1K->2L->3M->4N->5P->7R->9
S->2T->3U->4V->5W->6X->7Y->8Z->9
Second, look up the weight factor for each position in the VIN except the 9th (the position of the check digit).
1st->82nd->73rd->64th->55th->46th->3
7th->28th->1010th->911th->812th->713th->6
14th->515th->416th->317th->2
Third, multiply the numbers and the numerical values of the letters by their assigned weight factor, and sum the resulting products. Divide the sum of the products by 11. The remainder is the calculated check digit. If the remainder is 10, the calculated check digit is the letter X.
Finally, if the calculated check digit did match the 9th digit of the VIN entered by a user, the VIN passed the checksum test. It failed the checksum test otherwise.
View 9 Replies
View Related
Jul 9, 2014
Whenever or not I should use VBA or ordinary formulas in Excel in order to do Vehicle Routing With Two-Dimensional Loading Constraints case. What I need is when I have coordinates of four bottom-right corners:
X Y
24 0
30 0
10 24
8 38
2L-CVRP, what I want to know is: In reality I have set of 200 coordinates (x,y) and I need to search for lowest y (its (24,0) and (30,0)) then I choose one with maximum x ((30,0) in this case). Then if for some reason item cannot be packed there, then I shall go to next lowest y, which is 24 giving coordinate (10,24), if I cannot use this corner, I search for next lowest y with as high x as possible, etc. BTW I use Evolver in order to find satisfying solution.
View 3 Replies
View Related
Jun 3, 2014
Is this is just an example, but the logic should hold true. Data for example is in A1:C5. I want to use a SUMIFS statement that will look at type of Vehicle and Dates. So for the example in cell D1 (I wanted to add only Cars sold between May 1-2), but my current equation = 0, where I am expecting 20
=SUMIFS(C1:C5,B1:B5,B3:B4,A1:A5,A1)
View 5 Replies
View Related
Jun 11, 2014
I'm looking for a function which will check if the cell value is a valid UK registration number plate format.
True if it is, False if it isn't.
I've tried myself and had a look around but am unable to find a solution to my problem.
If also possible, if false, to make to necessary amendments to make it a valid format...ie If some lower case values, make them upper case, if there is a space entered, remove it, if a O is entered instead of a 0, correct it and show the corrected value when the function is run. For all that it can't do that with, then leave as False.
I understand the second part may not be possible, just identifying whether or not the value is a valid format. I have something similar which does this for UK postcodes, but am hitting a brick wall trying to do the same for UK registration number plates.
View 9 Replies
View Related
Jun 13, 2008
Let 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.
View 9 Replies
View Related
Jun 29, 2013
I 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?
View 8 Replies
View Related
Nov 26, 2013
I 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?
View 1 Replies
View Related
Nov 1, 2009
I 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)....
View 9 Replies
View Related
Jul 12, 2007
I 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.
View 4 Replies
View Related
Aug 4, 2014
I'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
View 2 Replies
View Related
Oct 2, 2013
I need a formula for batch allocation automatically.
See the attached file : Batch_Allocation.xlsx‎
View 5 Replies
View Related
May 23, 2012
I 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.
View 9 Replies
View Related
Apr 27, 2007
I 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 Related
Jul 24, 2008
I 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.
View 9 Replies
View Related
Feb 25, 2009
The 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 Related
Aug 31, 2012
I'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.
View 7 Replies
View Related
Jan 21, 2007
I've been trying for a while now but I can't do it so I was wondering if anyone could do it:
2) To get the "Overdue Items" page to update according to the overdue items
The database has been shortened drastically to be able to be able upload
View 9 Replies
View Related
Jun 24, 2007
I have a little problem that i need to solve in VBA(because its a part of a bigger solution in VBA). I need to complete a table.
I have:.......................
View 2 Replies
View Related
May 15, 2014
On 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%
View 1 Replies
View Related
Jan 27, 2014
I 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.
View 3 Replies
View Related
May 8, 2014
See attached file for a better understanding.
I would like to use a formula in Column 1 (highlighted) which tells me if it relates to Fund 1 or Fund 2 using the Mapping table in column H.
Using the mapping table would be nice but no need to.
Excel Question.xlsx‎
View 5 Replies
View Related
Jun 18, 2014
I'm looking to set up a table in Excel (or possibly Access that is then referenced through Excel). But end goal would be to be able to type in an Item Number and then Excel automatically fills in the Description, weight, price and other information about that item number. That way, I don't have to type in "11108.1" and "Computer Mouse" and "$15.98". I want to only type in "11108.1" and it knows to fill in the other information. Is this possible in Excel? I've searched through many functions and haven't found it so I'm thinking it may require a combination of formulas.
View 1 Replies
View Related
