About Bill Hermanson Expertise Please tell me WHICH EXCEL VERSION you are using!
DO NOT ASK ME me about Macros or VBA.
Please read my "instructions to questioners" in my full profile [use View Profile, at right], to help you write a question that I can understand, without having to ask you what you mean.
If your question contains any of the words THIS, IT, THAT, THOSE, or THEY, I likely won't understand IT. Please rewrite!
My Expertise: I am an expert at data manipulation, the use of incredibly complex logical statements, databases, combining tables and extracting data, all the LOGICAL, LOOKUP & REFERENCE functions, dynamic ranges, creating professional appearing spreadsheets, complex functions, integrated charts and visual displays, user interfaces.... I can make Excel do anything!
But PLEASE... NO MACRO or VBA QUESTIONS!
Experience 25 years development of complex spreadsheets for personal and professional use. I've developed hundreds (or thousands!) of spreadsheets in all fields, from complex engineering calculations to game scoring, financial analysis, scheduling, cost-of-doing-business, and analysis of home energy use. I even used Excel to assist in design of the flight computers presently on board the Hubble Space Telescope (1984-1991)
Education/Credentials BSEE Electrical Engineering, CU Boulder CO USA Use of spreadsheets since 1982 Boulder Valley School District, Life Long Learning, Instructor Owner & Operator of Excel Expert, LLC
Excel - Use Dates\Product No to split qty's between cells
Expert: Bill Hermanson - 10/21/2008
Question Hello Bill!
I have two worksheets that contain data from different sources. One worksheet contains multiple lines of data under the following column headings:
BECSTOCKCODE
VENDORCODE
MODULE_NO
REQUIRED_DATE
REQUIRED_QTY
The 2nd worksheet contains:
VENDORCODE
AVAILABLE_QTY
There is a 1-1 relationship between BECSTOCKCODE and VENDORCODE(they're different numbers from each other, but there's always 1 BECSTOCKCODE for every VENDORCODE).
On the first sheet you may see multiple lines with the same code numbers, required dates, and required qtys. Only the MODULE_NO is different in this case.
What I need to do is match the vendorcodes of each sheet, and starting with the earliest required date of the matched codes, match the required qtys from the AVAILABLE_QTY column on the 2nd sheet.
Basically the available qty on the 2nd sheet is the total amount we've received so far, and we need to split this qty up on the lines by becstockcode\vendorcode starting with the earliest required date, and in the case where two modules have the same required date, by module no. This column would be called RECEIVED_QTY, but on the 1st worksheet. I can send you an example sheet if you require for better clarification.
Thank you in advance for your assistance!
Answer Anthony,
Let me parrot back your question to you, with my comments interjected. My comments will be enclosed in ###'s.
=================================================================
There is a 1-1 relationship between BECSTOCKCODE and VENDORCODE(they're different numbers from each other, but there's always 1 BECSTOCKCODE for every VENDORCODE). ###okay I get this. No two BECSTOCKCODES are the same, and no two VENDORCODES are the same.####
On the first sheet you may see multiple lines with the same code numbers, required dates, and required qtys. Only the MODULE_NO is different in this case. ### So by 'code numbers' you mean the BECSTOCKCODE and VENDORCODE numbers, right? And, you mean there are no two MODULE_NOs that are the same.###
What I need to do is match the vendorcodes of each sheet, ### You mean between the TWO sheets, you only have two sheets, right?###
and starting with the earliest required date ### Sheet one's database is sorted by DATE REQ"D???###
of the matched codes, match ###you don't mean MATCH, you mean look-up, or compare, or subtract from, right?###
the required qtys from the AVAILABLE_QTY column on the 2nd sheet.
Basically the available qty on the 2nd sheet is the total amount we've received so far, and we need to split this qty up on the lines by becstockcodevendorcode starting with the earliest required date, and in the case where two modules have the same required date, by module no. This column would be called RECEIVED_QTY, but on the 1st worksheet.
### So what you are really trying to do is see if you have enough of each item in stock to fill the parts orders for the modules, in the order in which the modules are due to be built for delivery to the customer, right? You're trying to create an inventory database that will tell you if you have enough parts, and when you'll run out, right?####
====================================================================
Assuming that I am correct whenever I said 'right' in my ###-comments, then I'd ask if you are firm in your overall design of this. That is to say, your concept of having to look things up from sheet TWO onto sheet ONE is cast in concrete and you are unwilling to change it. So is this the way it HAS to be, or are you open to another implementation that the one you've thought of so far?
Now, I am not sure of all the details of my idea, nor even if my idea is somehow 'better' than your idea. Your idea requires you to keep a running total (somewhere)of each part as you subtract the quantity you need from the quantity on hand, and this subtraction ISN'T DONE on the sheet where the quantity on hand is kept! So after you use up some of a given part in a given module, the "quantity on hand" in sheet 2 becomes incorrect. I also suspect that you don't take into account new orders of the items on sheet 2. My idea requires that you make a lot of additions to sheet 2. You'll look up things from sheet 1 onto sheet 2, and keep a running total of the items availability on sheet 2, not sheet one.
Furthermore, I assume that both lists are constantly changing. New orders (resulting in new modules) are arriving, and new parts are being bought, thus altering the quantity on hand both up and down. In other words, neither of these is a static database, this is NOT a one-shot deal that you are trying to calculate ONCE and then its done. This workbook will be used continuously, every day, to monitor the quantities you have vs the quantities you need. Is this also correct?
With some of this in mind, I'd suggest to you that possibly you haven't thought this through completely yet. I could be mistaken about this, some of my guesses and assumptions might be completely wrong. Its difficult for me to give you an answer that makes sense when I suspect there might be a fatal flaw in your overall scheme.
HOWEVER, in general, I will give you some guidance that might help, without getting too specific. Since I don't understand your use of the word MATCH I am not sure my guidance applies.
To look things up from one table into another requires a common column in the two tables. You appear to have this in the VENDORCODE column. So for each VENDORCODE on sheet 1, you can lookup the available quantity from sheet two using the VLOOKUP function. I use this function all the time and it works great for this application. However, you're NOT changing the available quantity, and I believe you need to concoct a scheme which reduces the available quantity by the quantity you use. Simply looking it up ('matching') won't help you 'split' it.
But before I spend more time answering a question that I am not sure I understand, not which I believe is going to work, I'll ask you to A) give me more information, B) re-think your plan and make sure its right, C) confirm my guesses.
I hope you find these suggestions are constructive, and help steer your thinking. Building a fully-working inventory-management system is no easy task and there are many considerations to be considered...ha-ha. I will be glad to continue to help you getting this to work, or, you might consider hiring me to design the structure for you, in detail. We can talk about that later, if you wish.
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here
