Macro to compare

[Fiveshorter]

Hi

I have a excel sheet in which i have to compare a UID and qty in one sheet to another sheet. I do this every morning however it is very tedious and i thought a macro would be best so i could just run it as i could get 100s of records.

Record Sheet
UID QTY Shipped
1 3
2 2
4 1

Shipment sheet
UID QTY
2 2
4 2

In record, if the UID and Qty is equal to the Shipment sheet then say "ok" else "not ok". If the uid is not a match "uid incorrect".

If anyone could assist it would be so grateful

 

[CalcSux78]

Welcome to the forum!

You could use a helper column with a simple lookup formula paired with an If statement.

Excel 2010
	A	B	C
1	UID	QTY	Verify
2	2	2	OK
3	4	2	Not OK
Shipment

Cell Formulas
Range	Formula
C2	=IF(INDEX(Record!B:B,MATCH(Shipment!A2,Record!A:A,0),0)=Shipment!B2,"OK","Not OK")

 

[Fiveshorter]

Thank you for the formula however i dont think this would be suited to what i would like to do as the formula will output N/A if the UID is not equal. In my record file i have a column called shipped and the formula works great except for the fact that if it cant find something it will output na replacing an existing result. Example:

Record (before formula) .

UID | QTY | Verify
1 . 3 . ok
2 . 4
3 . 5
4 . 1
5 . 3

Record (after formula) .

UID | QTY | Verify
1 . 3 . N\A
2 . 4 . Ok
3 . 5 . Not ok
4 . 1 . Ok
5 . 3 . Not ok