| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 --- |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 --- |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 --- |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 --- |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Match function | Excel Worksheet Functions | |||
| Array | Excel Worksheet Functions | |||
| MEDIAN() as array function? | Excel Worksheet Functions | |||
| Code to determine if a cell contains an array function? | Excel Discussion (Misc queries) | |||
| I want to use the MATCH function with the AVERAGE function but I . | Excel Worksheet Functions | |||
Hybrid Mode
