Flagging Duplicates based on Multiple conditions

Dear Forum,

I tried using the below formula but in vain.

HTML Code: 

Dear Forum

Anyone I have tried using the sumproduct but am not sure how to compare and retain the highest weightage between the same designations as well as what if there's a tie?

Then how do I consider the record wit the Maximum information?

I think you might need VBA to accomplish this because of the problem of names being in the wrong order. Well, I expect you could do it with formulae but I'm guessing it could get quite messy.

Bump no response

Any Ideas...

ANy suggestions

Perhaps you will have to consider a non-formula solution? If your data are irregular and inconsistent then I'd have thought you will be beset by the same difficulties using a formula. And the fact that none has been forthcoming at a place like this would tend to suggest it will be arduous.

I don't know but it might help if you could attach a slightly larger sample of your data

Im really sorry pal, but due to company policy I cannot add the file...though i desperately need help as its too complex and beyong my understanding...

I would appreciate if you could atleast help me on the third point mentioned in the Duplicaton file individually...

i.e.

HTML Code: 

I will try to somehow manage the other things or even do it manually but it willl atleast reduce some time from the horrendous task of doing everything manually...

I want to compare the designations between the same name combinations and if this was conquered then compare the length of each column to remove the one with less data...

Bump no response..
Please anyone...is this possible as I have to actually sit for hours together to manually do the De-duplication...

Any small help also would be appreciated so that atleast some work can be reduced...

I have used formula and name

It is not difficult to understand, Plz see the way to solve in attach file, and see the Name by access menu: Insert \ name \ define

Success to you

Originally Posted by tigertiger

I have used formula and name

It is not difficult to understand, Plz see the way to solve in attach file, and see the Name by access menu: Insert \ name \ define

Success to you

Dear TIGER,

Im checking yor code from the Name Define....
It seems to be working but need to check for different permutaions and combinations...

But since you have reached the stage of eliminating normal designations and higher designations ...

can the same weightage issue be resolved?

Just a hint...! the same weightage would have two things to take care of it there are two complete records with the exactly the same amount of data then the first record be deemed as "unique" and if the data differs which can be compared by the length function then the one with the less data be termed as "Duplicate" and removed....

Dear Tiger,
Sorry for the delay,Im getting disconnected continously due to poor internet connectivity..

I'm not able to attach the actual file due to company security policy..so tried giving a Dummy database file...

But I'll explain and try attaching a more realisitic example...sometime later by 16thNov...

What you have achieved is really appreciable and almost like reaching the pinnacle..

But there would be 2 instances when there would be a Tie between two records when everything is same A i.e. Company Name, FName,LName combination, and also the Designation...

Now, I did not put the Address Tel1,Tel2 and Mobile Details...but if all these details are also exactly same then only one of the record has to be displayed as "Duplicate"...

Now that's a challenge to do so...you can keep the record on top as "unique" and display the other clone record as "Duplicate"...

Now in instance 2, everything is same as A i.e. Company Name, FName,LName combination, and also the Designation...

but there would be less data in one of the similar records for Address Tel1,Tel2 and Mobile Details, then in this case the record with the less details should be displayed as Duplicate....

What Im trying to do is to have all the Duplicates filtered and then removed from the database in one go as otherwise it is done manually which is not only tedious but also not reliable as human mind due to fatigue can miss some data...

Im soory that I have not attached the file but wil do so tommorow, but hope my explantion is good enough to give you an idea...

The amount of data can be compared by the =LEN() Function...

But i Dont now how to incorporate that in your magical formula...

Dear Tiger,

As promised, I have attached the file for your reference...
I have used callouts and colours extensively in the file to explain and diffrentiate between two records...

For simplicaity I have given examples comparing only two records at this juncture as your code uptill now looks quite robust...

Good lUck..

Dear TiGER...

ANy progress , your code is really wonderful I wish I could know how to do that...

Dear TIGER,

I think you have done excellent and it works perfectly, however there was a new requirement which I dont think can be made possible by Formulas...

Your code is really superb..but there's one thing which I almost forgot as achieving what you have achieved was also far-fetched for me..

Ex: If between two records one of the records is displayed as "Duplicate" based on designation but if it contains more information or complete information than the Unique record then this information needs to be copied to that other record which is Unique...automatically...

I dont think this is possible except for VBA and am not sure that also wud be possible..

Anyways, I mentioned that...

Thanks for all your help I have already rated you...

Originally Posted by e4excel

Dear TIGER,

Your code is really superb..but there's one thing which I almost forgot as achieving what you have achieved was also far-fetched for me..

Ex: If between two records one of the records is displayed as "Duplicate" based on designation but if it contains more information or complete information than the Unique record then this information needs to be copied to that other record which is Unique...automatically...

I dont think this is possible except for VBA and am not sure that also wud be possible...

YES, this is impossible to tackle the requirement by formula because the data (information: Address,Tel1,Tel2, and Mob1) are factors to form the formula. That means the data can not be a result ( this information needs to be copied to that other record which is Unique...automatically...).

Using VBA can solve the problem because of the result from SUB can become static information after addressing to the problem.

HOWEVER, I think we can make a suggestion for choosing the appreciate data (such as Address,Tel1,Tel2, and Mob1) in beside columns (ex: N O P Q) or a beside column (ex N - which show the row of record containing more information ). Do you think so?

TWO confused idea:

First:
There is a case:
+ Assuming record A and record B are formulated "Unique" and "Duplicate" by designation. Nevertheless, record B contains more information (in total length) than record B

+ Record A contains more information of Tel1 than Record B do,while Record A have less information of Mod1 than Record B do.

Which information we choose? Do We choose by comparing length of information in total of in each of kind information (Address,Tel1,Tel2, OR Mob1) for recording result????

Second
And another confused idea: You only base on the total length of data to access to their quality, is that away right??? (I am confusing about this way)

.

I will explain in details about the requirement..if you think that can be possible thru VBA as I dont know to use VBA except a few recorded macros..

Thanks for the help..

Originally Posted by shg

e4excel,

I will change your code tags to quote tags as appropriate. Please help us to keep the forum reasonably tidy.

Dear Shg,

Thank you doing it and bringing it to my notice...but I do not still know which are the Quote tags...

I always used "<>" from the tray...

I assumed it to be the same...now in this same thread I have selected "Quote" while replying to the thread but otherwise I used to select the "<>".

Please advise I too dont want the Moderators time to be invested in such a trivial issue such as changing the tags..

So thanks and sorry again..

Originally Posted by e4excel

Dear TIGER,


I have records in excess of 30,000-50,000.
Will this technique fail and then in that case you have cleverly also mentioned a solution of a SUB...

Please explain what is a SUB ?

I know its very tricky and very difficult to set a formula for the same...

Oh, too many record is exists in your data. I think that it is impossible to use my formula to address to that problem
You should change to dealing by VBA, SUB is a program is written by VBA. What is a SUB? You can draw the answer from the link: http://www.anthony-vba.kefra.com/vba/vbabasic1.htm

Originally Posted by e4excel

I will explain again since you have actually spent a lot of brain-cells on this..
These records are entered by simple Data entry people and some data may be from the previous year now the data from all the years is merged and then duplications has to be removed...

In certain cases if there's new information it actually needs to be rteained alongwith the existing information..
For ex:- If there is different data then that data needs to be added/appended to the existing data so we have more data but in case of same data ib both the records then keep only one set of data..and it works wonderfully with your formula..

I see, do you master VBA?

Originally Posted by e4excel

I agree that its not the most reliable method but i was not smart enough to reach the level you reached to understand it before...
Actually the data needs to be comparable more than the length...

Ok, we should not focus on total of items' length but focus on length of each item such as Address,Tel1,Tel2, OR Mob1

I do not know VBA at all except a few small recorded macros...
But is this possile to be done by VBA after using your magical formulas?

You have given me very good solution but the requirement itself is very complex to copy and compare the data...

Originally Posted by e4excel

I do not know VBA at all except a few small recorded macros...
But is this possile to be done by VBA after using your magical formulas?

YES, of course

However, if we use VBA, we should deal with all by VBA (classify unique or duplicate and update data) instead of using both the formula and VBA.

Originally Posted by e4excel

You have given me very good solution but the requirement itself is very complex to copy and compare the data...

I think that it is not complex, but applying that formula will impossible for your large data (with 30,000-50,000 records)

You should solve the problem by programing in VBA
Now I am busy, I will try to help you soon.
You should post a file with more cases and detail of your data (of course may be examples but like as your real data)
.

.