I am trying to get a formula for material quantities
What I want to do is
If length is less than 3mts I require 2
If length is more than 3 meters than I require 3 and 1 more for every 3mts after that
e.g:
2mts = 2
3mts = 2
4mts = 3
6mts = 3
7mts = 4
9.1mts = 5 and so on
I have been trying to work with to get my stock table classified into 3 different stock statuses and it comes out, it does not work I have attached the table as it is, the formulae I wanted to use and the end result I would like to have.
View 7 Replies View RelatedSay I have a worksheet that has a column in which every cell is formated in hh:mm:ss format. The column is probably 100 cells long...so from A2 to A101 (A1 is the title of the column). Now, we need to take the data in cells A2:A101 and graph it, but the data needs to be formatted in only seconds. So, in column B, we want to convert the data from column A into seconds format.
For example,
cell A2: 00:01:46
cell B2: 106
Of course, we can't convert each cell by hand, because this needs to be done on a lot of data...worksheets upon worksheets.
create a multi level BOM in excel:
i have a formula
A=a+b+c+B
B=a+d+e
if i select A, i need excel to give 2a+b+c+d+e (and that should be in another sheet.
also i may take 50% of A +50% of B the resulting formula must appear.
i attached an exemple file.
I have made an Excel illustration to explain what I would like to embark on.
I am not sure of the code, but if there is not an idea, I may see if I can find some idea of the code.
basically I want to be able to keep track of how much vinyl material I have left after each order.
The process would be - When I order a roll of vinyl material I would input the colour ordered and cm ordered by selecting from drop down lists in the 'Vinyl Tracker' sheet. When a customer makes an order, I would select an item, size and colour from drop down lists in the 'Orders' sheet. Depending on what size is selected in the 'Orders' sheet, I would then like Excel to automatically update column 'Cm Remaining' in the 'Vinyl Tracker' sheet, however using the smaller number in the relative size column from 'Item Sizes' sheet.
E.g. If we take the first order:
World Map
Medium
Black - (M)
This would then refer to cell M5 in the 'Item Sizes' sheet (as 43.35 is less than 90).
I would then like the number which is retrieved to be taken away from the relevant cell in the 'Cm Ordered' column, depending on what colour was chosen in the 'Orders' sheet.
To make matters a bit more complex, obviously when any of the numbers in the 'Cm Ordered' column in sheet 'Vinyl Tracker' is 0, I will re-order the same vinyl roll and insert it into the sheet as per usual. How can I make it so that any new orders will take away from the latest instance of the same coloured vinyl?
I use CAD software that generates Bills Of Material. I cut & paste these to an Excel template that has column headers in row 3, for example:
U3 = Item name
V3 = Manufacturer
W3 = Reference_item_name
X3 = Reference_item_ID
Starting from row 4, I would like to add the content of columns V, W and X to column U, separated by comma's. No superfluous comma's should be added when columns are empty. It would be nice to have a macro that uses the row 3 column names, so it still works if someone changes the column order.
I have a Spreadsheet with various tabs.I want to :-
1.A Userform to add records into "Material Indent"tab.
2.Secondly,transfer rows button on Userform to shift particular rows on entering Reel no. and date to "material Usage"job desired.xlsmtab.
I need to sort the material data based on the material rank but i can't use the 'sort/filter' function. Therefore, I used the VLOOPUP function. For some reason the vlookup formula is not working could you let me know what is the problem? see attchment.
View 2 Replies View RelatedWhat i am trying to do is to to determine the common material that is
used among different model do product in a product family. I have the
column C the various part number for the product family. Each product
model is made up of different combination of the parts.
In I3:U3 i have the model number for each product. Under each are the
combination of various part that make up each model. What i need to do
is in column G conditional formatiing that if all the different model
use a particular part (part number). The respective cell in column in
the row will be color. This will help me to determine what are the
parts that are common to all the product.
Column C Column G Column I .........................Column U
Part no Common Product 1 Product 2 Product 3 Product 4
12-1234-56 no color 1 4 0 6
13-2345-45 color 2 3 2 2
14-1234-56 no color 0 2 4 2
14-1234-56 no color 0 2 2 2
I have a material spreadsheet list that contains multiple entries of the same parts throughtout the sheet. How can i get it to total quanities needed by part numbers and consolidate it to one row instead of multiple rows. quantities are in column c and part numbers are in column d and descriptions in column e.
View 3 Replies View RelatedI would like to create Bill of material from single table. I need to select multiple parameters, to expend it till the lowest level, so I will try to explain:
I have one table (ODBC) and there's all data we need. Parent number col A, Item number col B and quantity col C. First level I select main Item number A1234 (this is only thing that I should choose, everything else should be automatically), I get table with all items that are parent A1234 (let's say 10 items). Now I need to look again one level lower in same table for items that have Parent item in list of those 10 items listed earlier (let's say 30 items) and multiply their quantities with quantity of their Parent Item (total qty could be in column D). Then one level lower for items with parent items in those 30 and so on and so on. So when I choose main Item I would like to get table like below (take notice that real table has over a 100.000 items, but I want to show only Bill of material for the main item till the lowest level).
Parent Item
Item
QTY
Total QTY
A1234
B1111
5
[URL] ...
I'm flexible and ok to use VBA, SQL, Excel functions, multiple tables (how to select multiple parameters??)
I am reposting this because I do not think I did a very good job explaining what I am trying to do.
I am at a total loss on how (if it is even possible) of how to do this. So what I have done is tryed to break down each step. If someone could even get me started in the right direction many of the steps are redundant and I could work on that part myself.
I am attempt to create system to match parts that are alike in a single project, so I can create a material ordering list. This is just one step in the process (the hardest one) I will take the returned data and use it further in the process to create the actual material list. I have 2 worksheet of Data "PartsNeeded" and "PartsAvailable" with a 3rd sheet "PartsFilled" as my report Sheet.
Attach is the sample data along with a "notes tab"
I have a list of products that have individual item numbers that share some common sizes and some common materials and are ordered in different quantities. When it comes to re-ordering the raw material to remake these items, I need to know how many of each item in the same type of material and size, need to be made. The attached spreadsheet shows more clearly what I am probably failing to explain! I have tried various combinations of VLOOKUP combined with MATCH but I can’t get anything to work – I guess I’m getting a little punch drunk here having spent the whole morning trying to find a solution!
View 5 Replies View RelatedBelow is the partial copied text of a Multi Level BOM. I am trying to hide or unhide the rows with 4, 5, 6, 7, 8 and greater number of dots. The dots indicate the level of BOM.
Would a macro make the task more automated?
I have multiple invoices for ordered products style number and quantity, these invoices are keep adding as orders are coming. I need a separate work book where all items summed. shows style and summed quantity from all invoices.
for example wb1
style quantity
or12 5
or34 2
or45 4
[Code]...
wb3 same style numbers but maybe not in the same column or row, wbs are keep adding as orders are placed.
I need a separate wb
shows style and pluses all quantities
style quantity
or12 6
or34 5
or45 6
I have built a pivot table that shows parts used during a period of years. I need creating a formula that would sum each part by a particular year.
The table is as follows
A part number
B date
C total quantity
I want to display the totals on a separate sheet.
formula on the attached sheet. I need to update it now. The strengths are going to stay the same, but the weaknesses (purple headers on spreadsheet) only have to show up if the value associated with it is 3 or greater. (So traits with quantities of 1 and 2 will not show up as a weakness)
I have attached a spreadsheet wit desired results.
example attached: If data repeats in column A, then add those corresponding values in column B. Can this be done without VBA?
View 5 Replies View RelatedI have 3 worksheets: Income; Expense; Consolidate.
In the first two sheets i am entering, by dates, quantities that are getting in and out of the warehouse.
My code copies that information in the consolidated sheet.
What I need is to make a code that Calculates the "Daily Quantities" and "Rent", based on quantity in the warehouse, that I am paying each day.
I want to match the quantities of buy and sell appearing the same line but in different columns based on FIFO. In case of mismatch, the excess quantity should be transferred to a new line below the same column in which there is a mismatch.
View 2 Replies View RelatedBelow I have attached a sheet for keeping track of the number of cables a person produces per day using Drop-Down list. I would like to have a totals area automatically calculate the corresponding cells. I started the Totals area in column AF.
View 13 Replies View RelatedI need to sum a column when the quantities meet two criteria. The name in one column, and the date in another column. Probably an easy formula, but new at this and having trouble.
View 4 Replies View RelatedI have a sheet which is just a list of product codes and a sum of every order placed for each code. i.e.
Sheet2 Â AB1Prod codeOrdered2123100345650478960
then I have another sheet which is the orders placed by our customers. These orders are to go out in 6 periods each of 2 weeks.
For various reasons the amount ordered each time won't match the periods.
The sheet beforehand will look like
Sheet1 Â ABCDEFGHIJKLM1Prod codeP1 REQALLOCP2 REQALLOCP3 REQALLOCP4 REQALLOCP5 REQALLOCP6 REQALLOC212327Â 20Â 35Â 20Â 12Â 11Â 345633Â 40Â 50Â 15Â 25Â 11Â 478915Â 20Â 20Â 20Â 20Â 10Â
What I need to do is look up the quantity ordered and then allocate the ordered quantity to the periods carrying over the remainder to the next period and so with the table above the result would be
Sheet1 Â ABCDEFGHIJKLM1Prod codeP1 REQALLOCP2 REQALLOCP3 REQALLOCP4 REQALLOCP5 REQALLOCP6 REQALLOC2123272720203535201812Â 11Â 34563333401750Â 15Â 25Â 11Â 478915152020202020520Â 10Â
Both sheets are sorted by the product code ascending and it doesn't matter if it is a formula or VBA based solution as I am already using both.
The number of product codes is currently just over 400 but will grow to about 550 by the end of period 6.
I need to sum up the batch quantities for a date with variance one...
but it doesn't work... I suspect that I'm using wrong formula, it should be not SUMPRODUCT...
when I tried to use just SUM, it adds all the quantities in the colomn.
=SUMPRODUCT(--(($AB$11:$AB$100)=AK12),--($AG$11:$AG$100=1),($AD$11:$AD$100))
I'm having some problems to find a way to distribute quantities from a known average price. Here's the Example:
My Client bought 100.000 Shares from a Stock, in various prices.
Qtty Price
10041,71
30041,61
70041,78
1.50041,72
4.40041,68
5.50041,75
10.00041,94
13.50041,70
64.00041,80
Then the client wants to distribute this trades for 7 different funds at the same average price.
Qtty Fund 1: 294
Qtty Fund 2: 541
Qtty Fund 3: 1.213
Qtty Fund 4: 13.370
Qtty Fund 5: 16.582
Qtty Fund 6: 28.002
Qtty Fund 7: 39.998
I'm sending an example attached.
I have a Bill of Materials (BOM) that has been flattened from a database into an Excel format. There are many BOM levels and I need to multiply the quantity of a component by the quantity of the "parent" it reports to. I have attached a sample file with raw data and the expected result (calculated manually). My goal is to create a formula that calculates the "Ext. Qty" column in my expected result. EXT_QTY.xls
Here are some of the rules for the raw data structure:
1. First BOM level at very beginning is always "1"
2. All children of BOM level "1" have a BOM level of "2"
3. All children of BOM level "2" have a BOM level of "3"...
4. There are potentially many BOM levels (approx. 15 levels for full set of data)
5. All of the BOM level "2" items are children of the most recent BOM level "1" assembly and there will be multiple level "1" items, level "2" items, etc.
6. Data is always sequential so the children (and grandchildren) will always come after the parent
I have three values. The Quantity of items, the cost per items and the cost for the entire order. In some instances I want to give my clients items for free so I need to lower the cost per item in order to keep the entire order cost the same.
In order to ensure we are on the same page let's use the following associations:
Y = Quantity
X = Cost Per Item
Z = Order Total
So essentially I need a formula that will automatically calculate the cost per item (X) when more Quantity (Y) is added in order to keep the order total (Z) the same.
I'm using excel 2010.
I'm trying to filter a data set to see only those groups that have a subtotal count of 3 or greater. The subtotals are at each change in customer to count the number of divisions that customer is associated with.
In other words, how can I see a list of only those customers who have the permission to shop 3 or more brands?
Excel Help.xlsx
I have a list of items and quantities I need of each, such as
cat 3
dog 2
mouse 1
horse 4
snake 2
leopard 4
I'm thinking that it would be a macro that would be way to go for this project.
I want to run a macro that will make the list above the following
cat 1
cat 1
cat 1
dog 1
dog 1
mouse 1
horse 1
horse 1
horse 1
horse 1
snake 1
snake 1
leopard 1
leopard 1
leopard 1
leopard 1
So what happened was that 6 lines of information was transformed into 16 lines of information. My purpose is that I will then will exploring various combination of these items, and thus I think that splitting them up like this will make them more manageable to work with. Since the original list values will change I will not always know how many lines to set aside for the individual breakdown.