Match and verify data in 2 sheets

Can somebody help me with this.

I have an excel file with sheet2 that contains bill records maintained by our company and we get the same information from our supplier that we add to sheet1 , now I need to match both the bill no. and bill amount in both the sheets. If any bill no. is not there in sheet2, then in sheet3 I want that particular row pulled in. Also, if both bill numbers match then I want excel to check if the amount is the same with plus or minus 20 difference acceptable.


Sheet 1:
Bill No. | Bill Amount
12350 | 250
45856 | 300
56866 | 600
4585 | 10000

Sheet 2:
12350 | 250
45856 | 300
56866 | 550

Hoping that this problem can be solved.
Thanks

hello nobin999
please attach a dummy sheet, with an example of what you want to see as a result
regards
Peter

Originally Posted by peterjuhnke

hello nobin999
please attach a dummy sheet, with an example of what you want to see as a result
regards
Peter

Hi, I have put below the results for sheet3.

I have an excel file with sheet2 that contains bill records maintained by our company and we get the same information from our supplier that we add to sheet1 , now I need to match both the bill no. and bill amount in both the sheets. Basically, sheet1 will be master file. If any bill no. is not there in sheet2, then in sheet3 I want that particular row pulled in.

Also, if both bill numbers match then I want excel to check if the amount is the same with plus or minus 20 difference acceptable. As you can see the last record in sheet2(bill amount) of 550 does not match with bill amount of 600 in sheet1 and the difference is of 50, so I want the said row in sheet1 highlighted with some color like yellow would be great.


Sheet 1:
Bill No. | Bill Amount
12350 | 250
45856 | 300
56866 | 600
4585 | 10000

Sheet 2:
Bill No.|Bill Amount
12350 | 250
45856 | 300
56866 | 550

=============================
The result in Sheet 3:
Bill No.|Bill Amount
4585 | 10000
==========================

Thanks for your time and consideration in advance.

Hi, I have put below the results for sheet3.

I have an excel file with sheet2 that contains bill records maintained by our company and we get the same information from our supplier that we add to sheet1 , now I need to match both the bill no. and bill amount in both the sheets. Basically, sheet1 will be master file. If any bill no. is not there in sheet2, then in sheet3 I want that particular row pulled in.

Also, if both bill numbers match then I want excel to check if the amount is the same with plus or minus 20 difference acceptable. As you can see the last record in sheet2(bill amount) of 550 does not match with bill amount of 600 in sheet1 and the difference is of 50, so I want the said row in sheet1 highlighted with some color like yellow would be great.


Sheet 1:
Bill No. | Bill Amount
12350 | 250
45856 | 300
56866 | 600
4585 | 10000

Sheet 2:
Bill No.|Bill Amount
12350 | 250
45856 | 300
56866 | 550

=============================
The result in Sheet 3:
Bill No.|Bill Amount
4585 | 10000
==========================

Thanks for your time and consideration in advance.[/QUOTE]