Match as well as does not match array function
 

Hi friends,
I have two different sheets with two different tables. The common unique
value in these tables are Invoice No. & Part No. Now I wish to reconcile the
data for missing value or wrong value from each other table like : maching
the value of col. Invoice No. but at the same time does not matching the
value of col. Part No. of that particular value. I have tried this by using
Index match & offset array function.
{=IF(ISNA(INDEX(II!$A$1:$C$13,MATCH(I!A3&I!B3,II!$ A$1:$A$13&II!$B$1:$B$13,0),2)),OFFSET(INDEX(II!$A$ 1:$C$13,MATCH(I!A3&I!B3,II!$A$1:$A$13&II!$B$1:$B$1 3,0),1),0,1),"")} For your ready ref. I am providing the tables.

Invoice No. Part No. Qty Result
61118138 MTM0213 155
61118138 MTM0214 115
61118138 MTM0295 2000
61118138 MTM0408 155
61118141 ATM0237 32
61118141 ATM0273 32
61118141 MTM0117 216
61118141 PTM0609 70 #N/A (the returned value should have been
MTM0337)
61118142 PTM0611 70 #N/A
61118142 PTM0613 70
61118142 PTM0615 70
61118142 PTM0617 70
And the table of sheet II contents :
Invoice No. Part No. Qty
61118138 MTM0213 155
61118138 MTM0214 115
61118138 MTM0295 2000
61118138 MTM0408 155
61118141 ATM0237 32
61118141 ATM0273 32
61118141 MTM0117 216
61118141 MTM0337 70
61118142 MTM0418 70
61118142 PTM0613 70
61118142 PTM0615 70
61118142 PTM0617 70

Could you help me out by resolving this:

--
Thanks,
Vikram P. Dhemare

Match as well as does not match array function
 

Perhaps something along these lines ..

Sample data as posted assumed in sheets: I, II, within A2:C13

In sheet: I

Put in D2, array-enter (CTRL+SHIFT+ENTER):
=IF(ISNUMBER(MATCH(1,(II!$A$2:$A$13=A2)*(II!$B$2:$ B$13=B2),0)),"",B2)
Copy D2 down to D13

The above returns in D9:D10

PTM0609
PTM0611

i.e. the 2 part nos in I
which do not match the invoice-part nos in II

In sheet: II

Put in D2, array-enter (CTRL+SHIFT+ENTER):
=IF(ISNUMBER(MATCH(1,(I!$A$2:$A$13=A2)*(I!$B$2:$B$ 13=B2),0)),"",B2)
Copy D2 down to D13

The above returns in D9:D10

MTM0337
MTM0418

i.e. the 2 part nos in II
which do not match the invoice-part nos in I
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Vikram Dhemare" wrote in message
...
Hi friends,
I have two different sheets with two different tables. The common unique
value in these tables are Invoice No. & Part No. Now I wish to reconcile
the
data for missing value or wrong value from each other table like : maching
the value of col. Invoice No. but at the same time does not matching the
value of col. Part No. of that particular value. I have tried this by
using
Index match & offset array function.

{=IF(ISNA(INDEX(II!$A$1:$C$13,MATCH(I!A3&I!B3,II!$ A$1:$A$13&II!$B$1:$B$13,0)
,2)),OFFSET(INDEX(II!$A$1:$C$13,MATCH(I!A3&I!B3,II !$A$1:$A$13&II!$B$1:$B$13,
0),1),0,1),"")} For your ready ref. I am providing the tables.

Invoice No. Part No. Qty Result
61118138 MTM0213 155
61118138 MTM0214 115
61118138 MTM0295 2000
61118138 MTM0408 155
61118141 ATM0237 32
61118141 ATM0273 32
61118141 MTM0117 216
61118141 PTM0609 70 #N/A (the returned value should have been
MTM0337)
61118142 PTM0611 70 #N/A
61118142 PTM0613 70
61118142 PTM0615 70
61118142 PTM0617 70
And the table of sheet II contents :
Invoice No. Part No. Qty
61118138 MTM0213 155
61118138 MTM0214 115
61118138 MTM0295 2000
61118138 MTM0408 155
61118141 ATM0237 32
61118141 ATM0273 32
61118141 MTM0117 216
61118141 MTM0337 70
61118142 MTM0418 70
61118142 PTM0613 70
61118142 PTM0615 70
61118142 PTM0617 70

Could you help me out by resolving this:

--
Thanks,
Vikram P. Dhemare

Match as well as does not match array function
 

Thanks Mr. Max,
This is working. But I want the returned value from sheet II i.e
MTM0337
MTM0418
i.e. the matched Invoice No. & does not matched Part No.
Is it possible.
--
Thanks,
Vikram P. Dhemare


"Max" wrote:

Perhaps something along these lines ..

Sample data as posted assumed in sheets: I, II, within A2:C13

In sheet: I

Put in D2, array-enter (CTRL+SHIFT+ENTER):
=IF(ISNUMBER(MATCH(1,(II!$A$2:$A$13=A2)*(II!$B$2:$ B$13=B2),0)),"",B2)
Copy D2 down to D13

The above returns in D9:D10

PTM0609
PTM0611

i.e. the 2 part nos in I
which do not match the invoice-part nos in II

In sheet: II

Put in D2, array-enter (CTRL+SHIFT+ENTER):
=IF(ISNUMBER(MATCH(1,(I!$A$2:$A$13=A2)*(I!$B$2:$B$ 13=B2),0)),"",B2)
Copy D2 down to D13

The above returns in D9:D10

MTM0337
MTM0418

i.e. the 2 part nos in II
which do not match the invoice-part nos in I
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Vikram Dhemare" wrote in message
...
Hi friends,
I have two different sheets with two different tables. The common unique
value in these tables are Invoice No. & Part No. Now I wish to reconcile
the
data for missing value or wrong value from each other table like : maching
the value of col. Invoice No. but at the same time does not matching the
value of col. Part No. of that particular value. I have tried this by
using
Index match & offset array function.

{=IF(ISNA(INDEX(II!$A$1:$C$13,MATCH(I!A3&I!B3,II!$ A$1:$A$13&II!$B$1:$B$13,0)
,2)),OFFSET(INDEX(II!$A$1:$C$13,MATCH(I!A3&I!B3,II !$A$1:$A$13&II!$B$1:$B$13,
0),1),0,1),"")} For your ready ref. I am providing the tables.

Invoice No. Part No. Qty Result
61118138 MTM0213 155
61118138 MTM0214 115
61118138 MTM0295 2000
61118138 MTM0408 155
61118141 ATM0237 32
61118141 ATM0273 32
61118141 MTM0117 216
61118141 PTM0609 70 #N/A (the returned value should have been
MTM0337)
61118142 PTM0611 70 #N/A
61118142 PTM0613 70
61118142 PTM0615 70
61118142 PTM0617 70
And the table of sheet II contents :
Invoice No. Part No. Qty
61118138 MTM0213 155
61118138 MTM0214 115
61118138 MTM0295 2000
61118138 MTM0408 155
61118141 ATM0237 32
61118141 ATM0273 32
61118141 MTM0117 216
61118141 MTM0337 70
61118142 MTM0418 70
61118142 PTM0613 70
61118142 PTM0615 70
61118142 PTM0617 70

Could you help me out by resolving this:

--
Thanks,
Vikram P. Dhemare

Match as well as does not match array function
 

"Vikram Dhemare" wrote:
Thanks Mr. Max, This is working.

You're welcome !

But I want the returned value from sheet II i.e
MTM0337
MTM0418
i.e. the matched Invoice No. & does not matched Part No.

In Sheet: I,

Try array-entered in D2, then D2 copied down to D13:
=IF(ISNUMBER(MATCH(1,($A$2:$A$13=II!A2)*($B$2:$B$1 3=II!B2),0)),"",II!B2)

Above will return in D9:D10
MTM0337
MTM0418
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Match as well as does not match array function
 

Hi Mr. Max,

It is working. But the problem is that table II does not have the whole Inv.
No. or whole part nos.
There may have some missing entries which i have to find out. The total data
of table I is about 40000 rows & the data of table II is around 380000 rows
that means almost 2000 invoices or part nos. may have missing.
After certain rows the formula returns the 0 value instead the result.
What I want to reconcile is the sales data of "X" company against the
purchase of "Y" company (X is the supplier of Y company)
Is it possible cause the earlier formula has got limitations.
--
Thanks,
Vikram P. Dhemare


"Vikram Dhemare" wrote:

Hi friends,
I have two different sheets with two different tables. The common unique
value in these tables are Invoice No. & Part No. Now I wish to reconcile the
data for missing value or wrong value from each other table like : maching
the value of col. Invoice No. but at the same time does not matching the
value of col. Part No. of that particular value. I have tried this by using
Index match & offset array function.
{=IF(ISNA(INDEX(II!$A$1:$C$13,MATCH(I!A3&I!B3,II!$ A$1:$A$13&II!$B$1:$B$13,0),2)),OFFSET(INDEX(II!$A$ 1:$C$13,MATCH(I!A3&I!B3,II!$A$1:$A$13&II!$B$1:$B$1 3,0),1),0,1),"")} For your ready ref. I am providing the tables.

Invoice No. Part No. Qty Result
61118138 MTM0213 155
61118138 MTM0214 115
61118138 MTM0295 2000
61118138 MTM0408 155
61118141 ATM0237 32
61118141 ATM0273 32
61118141 MTM0117 216
61118141 PTM0609 70 #N/A (the returned value should have been
MTM0337)
61118142 PTM0611 70 #N/A
61118142 PTM0613 70
61118142 PTM0615 70
61118142 PTM0617 70
And the table of sheet II contents :
Invoice No. Part No. Qty
61118138 MTM0213 155
61118138 MTM0214 115
61118138 MTM0295 2000
61118138 MTM0408 155
61118141 ATM0237 32
61118141 ATM0273 32
61118141 MTM0117 216
61118141 MTM0337 70
61118142 MTM0418 70
61118142 PTM0613 70
61118142 PTM0615 70
61118142 PTM0617 70

Could you help me out by resolving this:

--
Thanks,
Vikram P. Dhemare

Match as well as does not match array function
 

"Vikram Dhemare" wrote:
It is working. But the problem is that table II does not have the whole Inv.
No. or whole part nos.
There may have some missing entries which i have to find out.
The total data of table I is about 40000 rows
& the data of table II is around 38000 rows
that means almost 2000 invoices or part nos. may have missing.

Try this slightly revised construct ..

In sheet: I

Put in E2, array-enter (press CTRL+SHIFT+ENTER):
=IF(ISNUMBER(MATCH(1,(II!$A$2:$A$38000=A2)*(II!$B$ 2:$B$38000=B2),0)),"",ROW())
Copy E2 to E40000

Then put in F2, normal ENTER:
=IF(ISERROR(SMALL($E:$E,ROW())),"",INDEX(A:A,MATCH (SMALL($E:$E,ROW()),$E:$E,0)))
Copy F2 to G2, fill down until blanks appear,
signalling exhaustion of data extract
[since the discrepancy is around 2000 missing rows,
fill down to say, G2010 (say), to cover the expected extent]

Cols F and G would return all the invoice-part nos in I which are missing in
II,
with all results neatly bunched at the top
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Match as well as does not match array function
 

Hi Max,
It is working as per the standards.
Thanks. Also, could we extend the formula for qty also, that means for
matching the criteria of Invoice nos., Part Nos. as well as qty.
--
Thanks,
Vikram P. Dhemare


"Max" wrote:

"Vikram Dhemare" wrote:
It is working. But the problem is that table II does not have the whole Inv.
No. or whole part nos.
There may have some missing entries which i have to find out.
The total data of table I is about 40000 rows
& the data of table II is around 38000 rows
that means almost 2000 invoices or part nos. may have missing.

Try this slightly revised construct ..

In sheet: I

Put in E2, array-enter (press CTRL+SHIFT+ENTER):
=IF(ISNUMBER(MATCH(1,(II!$A$2:$A$38000=A2)*(II!$B$ 2:$B$38000=B2),0)),"",ROW())
Copy E2 to E40000

Then put in F2, normal ENTER:
=IF(ISERROR(SMALL($E:$E,ROW())),"",INDEX(A:A,MATCH (SMALL($E:$E,ROW()),$E:$E,0)))
Copy F2 to G2, fill down until blanks appear,
signalling exhaustion of data extract
[since the discrepancy is around 2000 missing rows,
fill down to say, G2010 (say), to cover the expected extent]

Cols F and G would return all the invoice-part nos in I which are missing in
II,
with all results neatly bunched at the top
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Match as well as does not match array function
 

"Vikram Dhemare" wrote:
It is working as per the standards.
Also, could we extend the formula for qty also, that means for
matching the criteria of Invoice nos., Part Nos. as well as qty.

Think there was an error for the formula in F2 earlier. Just detected.
Sorry, my fault. The extension to bring in the qty as well is just a simple
copy across to the next col before filling down.

Here's the revised formula:

In sheet: I

(Array formulas in col E remain unchanged)

Put in F2:
=IF(ISERROR(SMALL($E:$E,ROW(A1))),"",INDEX(A:A,MAT CH(SMALL($E:$E,ROW(A1)),$E
:$E,0)))

Copy F2 to H2, then fill down until blanks appear,
signalling exhaustion of data extract

[since the discrepancy is around 2000 missing rows,
fill down to say, H2010 (say), to cover the expected extent]

Cols F to H would return all the invoice-part nos-qty in I
which are missing in II,
with all results neatly bunched at the top
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---