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.
Say 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.
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?
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 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.
What 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.
I 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!
Below 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.
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.
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.
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.
Below 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.
I 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.
I 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.
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
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.
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 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?
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.