Fuzzy Logic

[James Draper]

Help Needed

I would class myself as a confident user of excel but I have real got stuck with this one.

Back ground, Two sets of data tables, identical formats however,lines will be estimated at around 250,000 per year, 6 years of data.

What 'm trying o investigate is if we have an duplications, but we need to consider a number of options:

1. Number ( Does it match another one by 90%+, so I the number is 10 digits long, does another number with 9 matching characters :
a. Same Value
b. Same Date

2. Value (Does it match another value where
a. Date Same

What I'm trying to create is a duplication report, but it has to work looking at data table one, data table two, the data table one V Data Table Two.

An advice ?

Thanks
James

[Bob Phillips]

You could try conditional formatting with a formula along the lines of

=AND(SUMPRODUCT(--(MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1)=MID(Sheet2!B2,ROW(INDIRECT("1:"&LEN(Sheet2!B2))),1)>=0.9)),C2=Sheet2!C2,D2=Sheet2!D2)