So, I have attached an Excel spreadsheet which is an example of the problem I am having.
Basically I generate two reports, which are shown in Sheet 1.
The first report is current inventory with the product code, the name of the product, and the quantity we currently have.
The second report is the same, only it only shows the product code and how many of that product were sold in a given time frame.
If a certain product wasn't sold, it doesn't appear in the second report (i.e.: no line shows Code: Alpha 2 Qty Sold: 0). This means the lists are two different lengths, which means I can't just paste the quantities in from the second report and have them all line up with the correct products in report 1.
The reason I want to do this is to establish how much of a given product I should order to cover the given time frame - the difference between quantity sold and current stock.
What I want to know is if there is some kind of formula that can go in column D that would basically look at the value in a cell in column A, find when E's value matched up, and take the number from F, putting it in D.
Then I can create an order column that has the formula: =if(D2>C2, D2-C2, 0) which produces either the difference, or a zero if the number would be 0 or negative.
Sheet 2 is what it would look like, although Sheet 3 is the ideal: if there is also a quick way to delete rows that don't have matching codes.
I don't know if this is clear, I'm happy to clarify. I've been dwelling on this all day. Any help is super appreciated.[DOUBLEPOST=1465433620,1465433470][/DOUBLEPOST]I doubt it needs saying, but the reason this would be so helpful is because there are usually hundreds of lines, not a dozen.
Basically I generate two reports, which are shown in Sheet 1.
The first report is current inventory with the product code, the name of the product, and the quantity we currently have.
The second report is the same, only it only shows the product code and how many of that product were sold in a given time frame.
If a certain product wasn't sold, it doesn't appear in the second report (i.e.: no line shows Code: Alpha 2 Qty Sold: 0). This means the lists are two different lengths, which means I can't just paste the quantities in from the second report and have them all line up with the correct products in report 1.
The reason I want to do this is to establish how much of a given product I should order to cover the given time frame - the difference between quantity sold and current stock.
What I want to know is if there is some kind of formula that can go in column D that would basically look at the value in a cell in column A, find when E's value matched up, and take the number from F, putting it in D.
Then I can create an order column that has the formula: =if(D2>C2, D2-C2, 0) which produces either the difference, or a zero if the number would be 0 or negative.
Sheet 2 is what it would look like, although Sheet 3 is the ideal: if there is also a quick way to delete rows that don't have matching codes.
I don't know if this is clear, I'm happy to clarify. I've been dwelling on this all day. Any help is super appreciated.[DOUBLEPOST=1465433620,1465433470][/DOUBLEPOST]I doubt it needs saying, but the reason this would be so helpful is because there are usually hundreds of lines, not a dozen.
