PDA

View Full Version : Solved: Merging data from two spreadsheets



reza_doang
05-25-2010, 07:58 PM
I have two spreadsheets that have data on line time (exported from different programs). I need to combine the two for an accurate down time report. The first spreadsheet has the date in column A and each time the line was shut down in column B and the avg line speed in column C. The second spreadsheet has the date in column A, the product that was run in col B, the time the product started running in col C and the time the product stopped running in col D.
Worksheet 1 Worksheet 2
Date Time Speed Date Product Start Time End Time
May 4 5:15 700 May 4 apples 5:15 7:12
May 4 6:20 450 May 4 pears 7:13 23:00
May 4 7:12 289 May 5 peaches 9:46 15:45
May 5 10:10 650 May 6 oranges 15:46 23:00

I am trying to find a way to merge the two documents to get an accurate picture of how much down time there is when running a specific product. Because the same date may be repeated numerous times on each worksheet and there are no individually unique columns to do a vlookup on, I can't think of an automated way to compile the data in time/product order. If there is a way to match the dates and do some type of "if" formula that looks at time on ws1 to see if it is less than the end time of ws2 but greater than the start time, then bring the product and start and end times from ws2 into columns D, E & F of ws1 next to the correct row, it would really help. Or maybe there is another way to tackle it??

Any help would be greatly appreciated!