Pulling data from two separate sheets and compiling into a new table

Hi All,

I have two sheets in a workbook outputting live data from our system and I'd like to create a third sheet that displays some of the information with some calculations too. The aim is to display every batch of material we have in the system that has both a positive stock quantity and a use by date, and then sort it in date order. I have attached a sample workbook, sheets one and two are what I get from the system, sheet three is a mock up of the output I'd like to get. Here's how I've calculated the five columns:

UseByDate - Every line in sheet 2 that has a non-zero entry in column F
Batch - Column B of sheet 2. For every entry in column F I want to pull the same row of column B
Material - I used a lookup between the sheets as column H on sheet 2 corresponds to column A on sheet 1, then taking the value in column B of sheet one to get the material number
Qty - Using columns I and J of sheet 2 subtracted from one another to get the qty of the batch that remains in stock. I only want to pull through positive quantities
Value - Unit value pulled from column V of sheet 1 and multiplied by the qty

I'm looking for a macro that we can run as the data from the system keeps updating and sheets one and two keep refreshing. I know it's quite a challenge, any help would be greatly appreciated! Thanks

Originally Posted by TPDave

UseByDate - Every line in sheet 2 that has a non-zero entry in column F
Batch - Column B of sheet 2. For every entry in column F I want to pull the same row of column B
Material - I used a lookup between the sheets as column H on sheet 2 corresponds to column A on sheet 1, then taking the value in column B of sheet one to get the material number
Qty - Using columns I and J of sheet 2 subtracted from one another to get the qty of the batch that remains in stock. I only want to pull through positive quantities
Value - Unit value pulled from column V of sheet 1 and multiplied by the qty

I think I followed the instruction, but the result in calculated column differ from yours.

Please Login or Register  to view this content.