Comparison of Two Excel Files

[StephenAdms]

I would like assistance comparing data from D8:D5160 on Sheet2 to D8:D6245 on Sheet1. I'm trying to pull the unique identifier (serial number) from Sheet2's column and try to find it in Sheet1's column. The data in both column D's are not in the same order so it will have to go line by line on Sheet2 and look for each serial number on Sheet1.

If the serial number is present, I would like it to color the entire row grey in Sheet 1 and sort the greyed-out serial numbers to the non-grey from top to bottom respectively.

Thank you for your assistance! I have tried doing it several ways and have not been fruitful. I have also searched the forums but could not find a result.


-- removed inline image ---

 

[StephenAdms]

Use conditional formatting with the following formula as an idea

=not(iserror(vlookup(Sheet1!D8,Sheet2!$D$8:$D$6245,1,false))

Thanks, this worked very well. Now, maybe you can help me with one more item. The cells that contain the color I need zero information on from Sheet 2, however, the ones that do not have a color I need information on.

This is my question:

1) Search Column D on Sheet1 for non-colored cell. Say D10 comes back as non-colored.
2) I then want to look up the order code in E10 and find it on Sheet2 in the E column.
3) If something comes back, I would like to take the value in Sheet2, column F and place it in Sheet1, column G.
4) If nothing comes back, just put #N/A.