Compare 2 spreadsheets based on criteria = macro?

[sfjnet]

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 sample spreadsheets to this post, but if you think you can assist with my query I can send you some small 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, 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.

 

[jc113883]

Try this. Not sure if the date check thing will work, give it a go and let me know.
You need to ensure that your blank 'results' spreadsheet is called 'sheet1'

sub DoThat
x=0
sheets("sheet1").range("A1") = "W20 Rows"
sheets("sheet1").range("B1") = "B20 Rows"

for each cell in sheets("W20").range("J:J")
    if cell.value = "" or cell.value > sheets("W20").range("H"& cell.row) +7 then
            x=x+1 
            UniqCode = sheets("W20").range("B"& cell.row)
            Sheets("Sheet1").Range("a1").Offset(x, 0) = cell.Row
            n = 1
            For each cell2 in sheets("B20").range("B:B")
                         If cell2.value = UniqCode then
                                   Sheets("Sheet1").Range("a1").Offset(x, n) = cell2.Row
                                   n = n+1
                          End If
             next
       End if
next
end sub