Export thread

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.

Yep, there are a bunch of functions that do what you need. I only use them occasionally so I can't just whip it out, but excel has a few functions LOOKUP and VLOOKUP and similar that treat the one table as a database, and you can search it, find the line with the same item code, and return the information a few cells over.

https://www.google.com/webhp?sourceid=chrome-instant&ion=1&espv=2&ie=UTF-8#q=excel lookup

There are surely some youtube videos as well, the functions are a little confusing at first so a text description can still leave you scratching your head.

Lookup is a horizontal search, Vlookup is a vertical.

They're used =LOOKUP(A,B:C,D) where A is the value you're trying to compare, B:C is the table of values you want to look up, and D is the number of columns/rows from the lookup column/row. I use it frequently when I'm doing baseball stats to put a particular team-oriented stat for each player.

stienman said:

Yep, there are a bunch of functions that do what you need. I only use them occasionally so I can't just whip it out, but excel has a few functions LOOKUP and VLOOKUP and similar that treat the one table as a database, and you can search it, find the line with the same item code, and return the information a few cells over.

https://www.google.com/webhp?sourceid=chrome-instant&ion=1&espv=2&ie=UTF-8#q=excel lookup

There are surely some youtube videos as well, the functions are a little confusing at first so a text description can still leave you scratching your head.

Click to expand...

Oh my goodness that's so easy and exactly what I needed.

Now I just need to figure out if I can quickly delete rows with #N/A values.

Thank you so much![DOUBLEPOST=1465438362,1465437922][/DOUBLEPOST]Ah, figured that out. Brilliant. Thank you both!