data compare

[JIGYANSHA1985]

Sir,

I would like to tally/compare both the files, whether field CONSIGN of one file matched to other file or not ...

If field CONSIGN of one file matched with that of the other file then on CHK field of that file reflected as OK, other-wise left as blank ... If field CUST_ID against field CONSIGN of one file is matched to CUST_ID against field CONSIGN of another file, then CHK field PERFECT shall be reflected ...

Regards
Jigyansha

[HansV]

Add formulas in column G in DESPKOL.xls that concatenate CUST_ID and CONSIGN: =B2&A2 in G2 and fill down. Similarly in column J in HIMA.xls: =B2&A2 in J2 and fill down.

Then enter this formula in F2 in DESPKOL.xls:

=IF(ISNUMBER(MATCH(G2,[HIMA.xls]Sheet1!$J$2:$J$1002,0)),"PERFECT",IF(ISNUMBER(MATCH(A2,[HIMA.xls]Sheet1!$A$2:$A$1002,0)),"OK",""))

and fill down. Similarly in I2 in HIMA.xls:

=IF(ISNUMBER(MATCH(J2,[DESPKOL.xls]Sheet1!$G$2:$G$997,0)),"PERFECT",IF(ISNUMBER(MATCH(A2,[DESPKOL.xls]Sheet1!$A$2:$A$997,0)),"OK",""))

and fill down too.

[JIGYANSHA1985]

Thanks a lot for your kind reply... Sir, if in both of the files field consign only matched, then whether the same syntax shall be follow or anything else...

Thanks to you again Sir ...

[JIGYANSHA1985]

Sir, again I would like to understand the syntax, if you don't mind, pl. guide me ...

[HansV]

Sorry, I don't understand your questions.

[JIGYANSHA1985]

Sir, Why you concatenate the two fields CONSIGN with CUST_ID

[HansV]

If I understood your original question correctly, you wanted to display "PERFECT" if a row matches on both CONSIGN and CUST_ID. The concatenation is used to enable this double match.

If that is not what you wanted, please explain what you meant by "If field CUST_ID against field CONSIGN of one file is matched to CUST_ID against field CONSIGN of another file, then CHK field PERFECT shall be reflected".