Need help with Excel Formula

[tviss64]

I have two sheets that have the same headers, A1 Address, B1 City, C1 State, D1 Site ID.  Sheet Old has 775 items and Sheet New has 1205.  I want to compare address between the two and place the identical in sheet 3 under Address in A1 while highlighting in a color.  Then I want to take the Site ID# from Sheet New and place in Sheet 3 under Site ID and highlight in color for both.  I then want to do the same for unique address in both sheet 1 and 2 and add to sheet 4 and sheet 5 and highlight in color for both.  I have tried vlookup, conditional formating but have not been able to complete this task.  Hoping someone with more knowledge can help provide some guidance.

Thanks,

Tim

[#2]

@tviss64Attached is an example of New, Old and sheet 3, 4, 5 of what I am trying to do.

[#3]

@tviss64 Why not manually indicate in your simplified example what you would expect to see in each of Sheets 3, 4 and 5. Including the colours and the logic behind it.

[#4]

@Riny_van_EekelenGood suggestion.  I have added the example in the attached file.

[#5]

@tviss64 Great! I'm on my way out now. Gladly leave it to someone else her to resolve. If not, I'll get back to you later.

[#6]

@Riny_van_EekelenThank you!

[#7]

@tviss64 This is something that would fit Power Query (PQ) very well. Especially if you have to do this regularly and on larger data sets. Created two structured tables from the Original and New data. Queried them and did three merges that load the desired records back to your sheets 3, 4 and 5. See attached workbook.

Are you familiar with PQ? If not, the site in the link below would be a good place to start.

https://exceloffthegrid.com/power-query-introduction/