Remove duplicates inside the cells (with criteria)

[julia81]

Hello,

I have a worsheet "Is" which has a column D. Column D contains of words followed by their grammatical identities in "{}". Seperator for the words is ";".
Now there are some words which are available multiple times but their grammtical identities inside "{}" differ. For example: " franc {adv.}; franc {m.}; franc {xy}; franc {??}". Now i have the word "franc" multiple times. I want to delete all those duplicates which dont have the grammatical identity "{m.}" or "{f.}". So the task is:

a) find duplicates inside the cells of Column D/worksheet "is"
b) delete all duplicates and leave only those which are followed by "{m.}" or "{f.}" . For example ( wordxy {adv.}; wordyy {adj.}; wordxy {m.}; wordxy {adj.} ==> wordyy {adj.}; wordxy {m.})
c) if there are duplicates but non of them has the grammatical identity "{m.}" or "{f.}" then delete the duplicates and leave which ever has the first position. For example ( word25 {xy.}; word1 {adv.}; word2 {n.} ==> word25 {xy}; {word1 {adv.})

I am attaching example wordlist along this thread and hope some one can help me clean this wordlist.

[MickG]

Try this:-
Results column "E".

Please Login or Register  to view this content.

Regards Mick

[julia81]

Hi Mick,

thank you very much.

I get an error when i run the code : " compiler Error: For without Next"

thanks

[Pichingualas]

It seems it should be like this:

Please Login or Register  to view this content.

[jindon]

Different method

Please Login or Register  to view this content.

[julia81]

duplicates.xlsmThank you very much! its running now but stops working on this line:

nu {n. m. invar.}; nu {m.}

it shows "runtime error 13: type mismatch"


Secondly it produces new duplicates instead of deleting the old ones (is it because the macro doesnt run to the end?)(Edited: solved! I ran the macro twice which produced new duplicates)

I am attaching a new example file with more data and hope it will run.

[MickG]

The Problem was I Cut the Code short when copying !!!
Change the line in red at the Bottom of the Code

Please Login or Register  to view this content.

Can you Sent the New File with a ".xls" extension and I'll have another look !!

[julia81]

@Jindon Hi,
thank you very much for your code. As i was posting my last answer, i didnt saw your solution. But now i tried it and it runs till the end and does what i need it to. But it leaves many cells empty (doesnt process some cells). I am attaching a file with example data where your code doesnt process the cells. I have colored red those cells which are not processed by your code. Thes cells contain no {m.} and no {f.}. Is it the reason? In these cases duplicates should be deleted and which ever is in first position should be left. For example:
acteur {n.}; comédien {adj. et n.}; comédien {n.} => acteur {n.}; comédien {adj. et n.}
OR if its easier to do following, it will be a good solution for me as well:
acteur {n.}; comédien {adj. et n.}; comédien {n.} => acteur {n.}; comédien

@MickG
thanks for the modification. I am attaching now the file in *.xls format. Thank you in advance.

[MickG]

Try this:-
Nb:- Your new file does not show the Expected result !!!

Please Login or Register  to view this content.

Regards Mick

[jindon]

Not sure if this is how you want

Please Login or Register  to view this content.

[julia81]

@Jindon
Thank you very much Jindon. My problem is solved as I first run you first macro and then the second. :-)Thaaaaanks

@MickG
It works now. Thank you very much


@Pichingualas
thank you very much for your help as well.

You both cant imagine how much time i will save through this code. I processed over 40 000 rows with this code. Thank you both ver much.

[Pichingualas]

Always glad to help, as little as my help was.