I posted the link earlier without time to explain what I had done. Here is the method that I used so people don't need to download from the link. I used Narayank991's file for the data
[pre]
Code:
On sheet 1 - "data from source 1" - A1:C12
Container No. Destination Quantity
1102 MAS 1234
1103 SGP 2345
1104 DYH 3456
1105 KLM 4567
1106 QRT 5678
1107 FRN 6789
1108 ESP 7890
1109 ITA 1098
1111 IND 1765
1113 IND 4356
1114 AUS 3245
On sheet 2 - "data from source 2" the data to compare against - A1:C12
Container No. Destination Quantity
1102 MAS 1234
1103 SGP 3456
1104 DYH 3456
1105 KLH 4567
1106 QRT 5678
1107 FRN 6789
1108 ESP 7890
1109 ITA 1098
1110 IND 1765
1113 IND 4445
1114 AUS 3245
off to the side I have a lookup table for the index/match used in the formula - this is in Sheet1 H1:I8
0 No Match
1 Destination & Quantity Mismatch
2 Container & Quantity Mismatch
3 Quantity Mismatch
4 Container & Destination Mismatch
5 Destination Mismatch
6 Container Mismatch
7 Perfect Match
[/pre]
Formula used: enter in G2
=INDEX($I$1:$I$8,MATCH(LARGE(MMULT(--(A2:C2=Sheet2!$A$2:$C$12),{1;2;4}),1),$H$1:$H$8,1))
Using the MMULT function, which I handily just picked up with the first post from derek in "Formula Forensics 008", you can multiply two arrays(matrices) together and sum the results.
So stepping through the first cell, G2:
--(A2:C2=Sheet2!$A$2:$C$12)
This is checking the container ID, destination, quantity against the data in sheet2. Pressing F9 when this is selected gives you this:
{1,1,1;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0}
As it so happens the first row of data in sheet2 is a full match, evidenced by the three consecutive #1s separated by commas.
This result is multiplied with the second array in MMULT, {1;2;4} [notice the semi-colons - this is a requirement of MMULT, the second array must have the same number of rows (
as the first array has columns (,)]
The result of multiplying the two together would give you this (can't be seen with F9)
{1,2,4;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0}
The end result of MMULT is this:
{7;0;0;0;0;0;0;0;0;0;0}
You might want to look up matrix multiplication to see how it gets to this result, but effectively in this case, the columns are summed together, 1+2+4 = 7
The LARGE function finds the first largest value, 7 here, which pulls into the INDEX/MATCH lookup table and finds 7 "Perfect Match".
LARGE({7;0;0;0;0;0;0;0;0;0;0},1) = 7
MATCH(7,$H$1:$H$8,1) = 8
INDEX($I$1:$I$8,8) = "Perfect Match"
I don't know the technical term for the second array, {1;2;4}, but using it provides for a unique number for each unique combination of matches. If you have 4 criteria, rather than 3 in this example, the second array would be {1;2;4;8} - the next number is double the previous.
