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

Report •

✔ Best Answer
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),""))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

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!

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


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.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


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
✔ Best Answer
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),""))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

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.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


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.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


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 •

Ask Question