# Solved Comparrison formula to show inaccuracies between 2 sheets

Microsoft Excel 2010 - complete product...
October 27, 2014 at 08:31:34
Specs: Windows 7
 Worksheet A``` A B C D E F 1 PO PROMISED DATE QTY ORDERED QTY RECEIVED QTY OPEN Comments 2 23023 11/7/2014 128 81 47 3 24122 10/24/2014 216 78 138 4 24123 10/24/2014 216 155 61 5 24125 12/19/2014 144 0 144 6 24126 12/19/2014 144 24 120 7 24556 11/7/2014 2000 0 2000 8 24558 10/31/2014 3000 174 2826 9 24733 11/7/2014 36 0 36 10 24734 11/7/2014 36 0 36 11 25772 10/31/2014 396 279 117 12 25773 10/31/2014 396 380 16 ```Worksheet B``` A B C D E F G 3 Due Order Ship Ship 4 Order # PO Item # Date Qty Qty Date 5 6 6046 25772 11C 10/31 396 278 10/23 7 6153 26868 11C 11/7 468 8 6184 27608 11C 11/14 396 9 6454 30411 11C 12/12 396 10 6455 30563 11C 1/9 396 11 12 6047 25773 12C 10/31 396 380 10/23 13 6154 26872 12C 11/7 468 14 6185 27613 12C 11/14 396 15 6456 30564 12C 1/9 396 16 17 6183 27606 13B 11/14 72 18 6339 29539 13B 12/5 36 19 6457 30785 13B 12/19 36 20 6458 30786 13B 1/9 36 21 31283 13B 12/19 36 22 23 6182 27607 14B 11/14 72 1 10/8 24 6459 30787 14B 12/19 36 ```So what I need is a formula in column F of worksheet A to show the due date from column D on worksheet B if it's different than the due date in column B on worksheet A, or if the Qty Shipped in column F on worksheet B is different than the Qty Received in column D on worksheet A, all based on matching the PO# in column A on worksheet A to the correct PO# in column B on worksheet B. If everything is in agreement then column F would just stay empty.message edited by NinjaKitty

See More: Comparrison formula to show inaccuracies between 2 sheets

October 27, 2014 at 10:32:50
 Edit:I changed the formula that I originally posted. One of my VLOOKUP columns was wrong. The formula below should be OK. Sorry!There was one other condition that you did not specify.You said:"If everything is in agreement then column F would just stay empty."However, you didn't say what Column F should show if there wasn't a matching PO#. The following formula should give you what you asked for, but it will also tell you if a PO# on Sheet A isn't found on Sheet B. Using a blank cell for both "No Error" and "No PO Found" would be confusing, at least to me.The first thing this formula does is lookup the PO to see if it even exists on Sheet B. If it does, then it checks both the Due Date and the Qty. If either of them don't match, it returns the Due Date from Sheet B.```=IF(ISNA(VLOOKUP(A2,B!\$B\$6:\$B\$24,1,0)),"PO Not Found", IF(OR(VLOOKUP(A2,B!\$B\$6:\$D\$24,3,0)<>B2, VLOOKUP(A2,B!\$B\$6:\$F\$24,5,0)<>D2), VLOOKUP(A2,B!\$B\$6:\$D\$24,3,0),"")) ```message edited by DerbyDad03

#1
October 27, 2014 at 09:02:34
 Please click on the link at the end of this response and read the instructions on how to use the pre tags to line up your data so that it is easier for use to read. Make sure that you use both Column letters and Row numbers to make it easier for us to discuss the layout of your spreadsheet with as little confusion and ambiguity as possible. Thanks!

Report •

#2
October 27, 2014 at 09:45:52
 Sorry about my earlier mess, I've edited it to look as I intended. Thanks for the instructions!

Report •

#3
October 27, 2014 at 10:08:43
 There is still some confusion:Question 1:"...show the due date from column D on worksheet B if it's different than the due date in column B on worksheet A...."Column B on worksheet A contains a Promised Date, not a Due Date. Is that the date the formula should be checking?Question 2:"...or if the Qty Shipped in column F on worksheet B is different than the Qty Received in column D on worksheet A"...What do you want displayed in Column F for that condition? The Due date from Worksheet B? If so, how will you know which condition doesn't match - the Dates or the Qty? Using the same result for 2 different "error" conditions seems like it would make more work for the user.

Report •

Related Solutions

#4
October 27, 2014 at 10:19:36
 Answer 1:Yes, the Promised Date is the Due Date. I had meant to change the wording but then missed it before I posted it.Answer 2:If either condition is false then Column F should show the Due Date from Worksheet B. I'll be able to see if the date is incorrect with just a glance, so if the date's correct I'll know that it's the quantity that's the problem, which will prompt me to investigate the issue further. ...Worksheet A is something that I receive from my customer & Worksheet B is my own internal tracking sheet, comparing these two sheets is how we keep in agreement with each other...

Report •

#5
October 27, 2014 at 10:32:50
 Edit:I changed the formula that I originally posted. One of my VLOOKUP columns was wrong. The formula below should be OK. Sorry!There was one other condition that you did not specify.You said:"If everything is in agreement then column F would just stay empty."However, you didn't say what Column F should show if there wasn't a matching PO#. The following formula should give you what you asked for, but it will also tell you if a PO# on Sheet A isn't found on Sheet B. Using a blank cell for both "No Error" and "No PO Found" would be confusing, at least to me.The first thing this formula does is lookup the PO to see if it even exists on Sheet B. If it does, then it checks both the Due Date and the Qty. If either of them don't match, it returns the Due Date from Sheet B.```=IF(ISNA(VLOOKUP(A2,B!\$B\$6:\$B\$24,1,0)),"PO Not Found", IF(OR(VLOOKUP(A2,B!\$B\$6:\$D\$24,3,0)<>B2, VLOOKUP(A2,B!\$B\$6:\$F\$24,5,0)<>D2), VLOOKUP(A2,B!\$B\$6:\$D\$24,3,0),"")) ```message edited by DerbyDad03

Report •

#6
October 27, 2014 at 11:11:58
 Ok these are probably dumb questions, but for this formula to work with my worksheets, do I just remove the "B!" and replace it with the title of my other worksheet? Also my other worksheet actually has 581 rows in all, so would I just change out the "24"'s of your formula to 581's as well?message edited by NinjaKitty

Report •

#7
October 27, 2014 at 11:46:59
 Yes (remove the B but leave the !) and Yes (as long as the other sheet will never have more than 581 rows)You really should study up on the VLOOKUP function so you know what the formula does. VLOOKUP is very powerful. Once you know how it works, I'm sure you will find other uses for it, especially if you are comparing worksheets where the data isn't in the same order.

Report •

#8
October 27, 2014 at 11:58:54
 There is a good possibility that this sheet will have more than 581 rows. Is there a way that I can avoid having to adjust the "581" out each time? Of course if I do have to change one number every week it's no big deal, just being able to avoid the tedious task of comparing each order line by line every week will be a major improvement to my workload alone!

Report •

#9
October 27, 2014 at 12:08:59
 Using a much higher value won't impact performance in any amount that you would notice, even into the thousands.Or you could use Named Ranges and when the new sheet comes in just select the range and name it with whatever name you use in the formula. This of course, assumes a new workbook each time since you can't use the same name more than once in a given workbook.You can use the same size range for each VLOOKUP since it's the col_index_num argument that really matters. Therefore replacing all of the table_array arguments with the same range name should work. Once you understand how VLOOKUP works, you'll see what I mean.

Report •

#10
October 27, 2014 at 12:13:56
 Ok Great! Thanks for helping me out with this and sorry again for not knowing how to ask the question correctly in the first place! :)

Report •