Find the duplicate from a column of a excel of text string in colm b

[SUPERVISORPTCL]

GOOD AFTERNOON DEAR TEAM:-

This is my first Thread in this forum, i attached a simple file I want to know the duplicate value in col "B" in section I already highlighted and it is a
very huge find I want to know through formula
Kindly help me
Thanks in advance

[Glenn Kennedy]

What makes them duplicates? They have the same number after UFONE.

What makes other duplicates in your data? The same number after UFONE, or the same number after XYZ, or after ABC, or what? What identifying feature can we use?

[SUPERVISORPTCL]

Thanks a lot Sir,
Actually I want to trace out the duplicate like the section have similar entries like this:
SMNBD: Police station to fazlia colony to Chowk Rehmanpura
GTN: OLT to DC-64 Salar Road Salamat Pura Kotlakhpat
Bahria OLT2 to Canal Garden Bahria Town
GTN-OLT to DC-64 Salar Road Salamat pura
FRZD: BLT-16 LDA Quarter More Walton Road to B-13 Opposite PEL Factory
BTN: OLT2 to Canal Garden Bahria town
BTN: OLT2 to Canal Garden Bahria town
SMD OLT-2 to MSAG Met Office
SMD: DC-34 Fazlia Colony to C-40 Chowk Rehmanpura & B-43 Opp. Police Station Ichra
Kahna ROB IV to Green Cap

So kindly help me

[Glenn Kennedy]

Are you saying that all of the lines in your last post are duplicates???

They have nothing in common!!! Some contain OLT... others do not... Some contain BTN... others do not.

Excel needs to have a pattern... one (or several) keywords to look for in a string that will identify strings as being duplicates. Can you provide the keywords for your data? Then I might be able to help. Without keywords... this is impossible.

[SUPERVISORPTCL]

Thanks a lot Sir,
These are the sections of an OFC cable route and when the cable is faulty from these section
in a month:
These sections and many more repeated again and again in a month like the attachment which shows the date and time,
I need like this:-

Police station to fazlia colony to Chowk Rehmanpura
DC-64 Salar Road Salamat Pura Kotlakhpat
Bahria OLT2 to Canal Garden Bahria Town
DC-64 Salar Road Salamat pura
LDA Quarter More Walton Road to B-13 Opposite PEL Factory
OLT2 to Canal Garden Bahria town
OLT2 to Canal Garden Bahria town
OLT-2 to MSAG Met Office
DC-34 Fazlia Colony to C-40 Chowk Rehmanpura & B-43 Opp. Police Station Ichra
Kahna ROB IV to Green Cap

These are the section which is duplicated and repeated so i need a formula which trace out these text string and found it.
Thanks a lot once again.

[Glenn Kennedy]

Your explanation makes no sense to me, so I'll step back at this point. Good luck!!

[SUPERVISORPTCL]

Your explanation makes no sense to me, so I'll step back at this point. Good luck!!

Sir, I already clear the point keyword like this:-

Chowk Rehmanpura
Salar Road Salamat Pura
Kotlakhpat
Canal Garden Bahria Town
Salar Road Salamat pura
LDA Quarter More Walton Road
Canal Garden Bahria town
Met Office
Chowk Rehmanpura
Police Station Ichra
Kahna ROB IV to Green Cap

These are the keywords and many more in the original file.
Thanks a lot once again.

[AliGW]

Provide an updated sample workbook with more than ONE set of duplicates identified AND the expected results mocked up manually. You know what all this means and should look like - we don't, and your explanatiosn aren't getting us any closer - sorry.

[SUPERVISORPTCL]

Thanks a lot Mom:-
I again upload a new sample:

[AliGW]

Really sorry - as there is no logic I could apply to tell Excel how to decide what is a duplicate, I am unable to help you on this occasion.

[SUPERVISORPTCL]

Your explanation makes no sense to me, so I'll step back at this point. Good luck!!

If we like a table of strings and then using the formula we pull out the strings which match this table like this:-

C-40 Chowk Rehmanpura
Salamat Pura
Canal Garden
Salamat Pura
Inside-Pel-factory
Canal Garden
Canal Garden
Met Offic
C-40 Chowk Rehmanpura
Green Cap
Salamat Pura
Shaheen Block
Met Offic
Lead Club Defence Road
Shaheen Block
Inside-Pel-factory
Shaheen Block
Green Cap
Inside-Pel-factory

Thanks for your help

[Glenn Kennedy]

One last try...

Can you now post a sample file with some data which contain/do not contain these phrases (or tell me which post to look at...)

[SUPERVISORPTCL]

Thanks a lot Sir, post#9 I think help you other wise i will be share a new data file.

[Glenn Kennedy]

OK. So I am still not sure exactly what you want.

However, your list of key words at Post 11 containe a lot of duplicates (e.g. Canal garden was there twice, etc, etc). So I cleansed it and wound up with eight unique keywords.

You never really explained (or maybe I got lost in the lengthy exchange of messages)... exactly what you wanted to see. So this may/many not be what you want.

=IFERROR(LOOKUP(2,1/(--ISNUMBER(SEARCH($R$3:$R$10,B3))),$R$3:$R$10),"")

identifies which (if any) keyword is present.

=IF(K3="","",COUNTIF(K$3:K3,K3))

incrementally counts the occurrence of each one. So the first instance of keyword XXX returns 1... the second 2.... and so on

=IF(AND(L3>1,L3<>""),"Duplicate","")

identifies the second (and subsequent) instances of each keyword.

Does this help??

[SUPERVISORPTCL]

Thanks a lot Sir, it is very near which is i needed, if you have more ideas then kindly help me.
Once again thanks for your help.

[Glenn Kennedy]

What does "very nearly" mean? Either it works or it doesn't... If you don't tell me what's not working, how can I have any ideas about fixing it...

[SUPERVISORPTCL]

What does "very nearly" mean? Either it works or it doesn't... If you don't tell me what's not working, how can I have any ideas about fixing it...

Sir, it's work fine, i mean if you have more formulas like these problems also share.
Thanks a lot for your kind help.

[Glenn Kennedy]

OK. If you have specific Q's feel free to ask them here. for now...


You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.