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.

   Until I hear back from you,

        Best wishes,

           >>> Bill