PDA

View Full Version : Compare 2 spreadsheets with conditions = Macro?



sfjnet
06-25-2007, 04:18 AM
Hi - Forgive me if the answer to this is obvious but I don't really do this sort of thing normally. I need to compare info from 2 different spreadsheets and output to a third based on certain criteria. I don't think I can attach more than 1 sample spreadsheet to this post, but if you think you can assist with my query I can send you some the other sample files to use as a guide - just reply to let me know your email. What I need to do is:

(a) - starting with my sample W20 Test Page spreadsheet (attached), I need to select all rows where the Column J (Actual Date) field is either blank or shows a date with is more than 7 days later than the date shown for the same row in Column H (Original Schedule Date)

(b) - each of those rows also has a unique code number shown in Column B (Item Public Code)

(c) - I then need to look on the B20 Test Page spreadsheet to pick out any rows where the code number shown in Column B (Item Public Code) is the same as the Column B code number on the W20 Test Page for rows that meet the criteria set out (a) above.

(d) - I would then like to copy these rows to a third blank spreadsheet so that, ideally, it would first show the W20 row that meets the criteria in (a), followed by the B20 rows with matching Column B code numbers. Please note that on the B20 spreadsheet there may be several rows with the same code number as a single row on the W20 sheet. This is because the B20 rows refer to sub-items of the overall item shown on W20.

To hopefully assist with this explanation, on the W20 sample sheet I have coloured a row which meets the criteria in (a). On the B20 sample sheet I have coloured several rows with Column B code numbers that match the code number from the highlighted row on the W20 sheet. Finally I have created a Sample Results spreadsheet to show what sort of output I am looking for based on the actions above. It's probably cheeky, but on the results sheet it would be good to have the "W20" fields coloured red.

I hope this makes sense, and many thanks for reading