Duplicate text values in a column

[AFZAL SOHAIL]

I have data file in which a coloumn c have the following text strings, you see that sr.no.1 & sr.no.13, sr.no.2 and sr.4 are the same, I want to identify through formula the conditional formatting is not working because some are order is not same but they are the same faults, kindly help me

1	8041-GTN to DC-44 Firdous Market
2	FZRD: OLT-1 to C-5 Pak Arab Society
3	8061-ARZ (2/0/7) to C-3/4 Bhobhtian Chowk
4	FRZD: OLT-1 to C-5 Pak Arab Society
5	SMD: MSAG-37 GOR-III to C-32 Cricket House GOR-II & C-3 Rehmania Park
6	C-13 Truck Stand Adda to SMD: OLT-1
7	Bhobhtian Chowk to 8061-ARZ (2/0/7) and C-3/4
8	8061-ARZ (11/0/7) to C-3/4 Bhobtian Chowk Defence Road
9	SMD: OLT-1 to C-13 Truck Stand Adda
10	FZRD: FZRD OLT-1 to MSAG-58 Near Advance Fashion
11	Firdous Market to 8041-GTN DC-44
12	8061-ARZ (2/0/7) to C-3/4 Bhobhtian Chowk Defence Road
13	FZRD: OLT-1 to MSAG-58 near Advance Fashion

 

[Joe4]

Welcome to the Board!

I do not see any duplicates in your list.

Lines 1 & 13:
8041-GTN to DC-44 Firdous Market
FZRD: OLT-1 to MSAG-58 near Advance Fashion

Lines 2 & 4:
FZRD: OLT-1 to C-5 Pak Arab Society
FRZD: OLT-1 to C-5 Pak Arab Society

However, the formula you WOULD use for Conditional Formatting would be to select Range C1:C13, and then use this formula:

=COUNTIF($C$1:$C$13,$C1)>1

 

[AFZAL SOHAIL]

Thanks a lot Sir for your response:
Lines 2&4 the same
FZRD: OLT-1 to C-5 Pak Arab Society
FRZD: OLT-1 to C-5 Pak Arab Society
Similarly the Lines 1&11 the same:-
8041-GTN to DC-44 Firdous Market
Firdous Market to 8041-GTN DC-44
But line 1 started from 8041-GTN Line # 11 started from Firdous Market they are the same but when I will use conditional formatting it will not applicable,
so will be trace out manually, I need the formula which trace out like these duplicates strings please help me.

 

[Joe4]

Those lines are NOT the same.
"FRZD" is NOT the same as "FZRD"

and lines 1 and 11 are not even close:
1: 8041-GTN to DC-44 Firdous Market
11: Firdous Market to 8041-GTN DC-44

Both of those lines have similar components, but they do not match completely.
If you want lines which are similar to be treated as duplicates, then you must supply us with the logic/rules for determining whether or not two lines are are match.
What exactly are we matching on? A specific code? Specific words?
You need to clearly define the rules we are to use to determine if there is a match or not.

 

[AFZAL SOHAIL]

I will try to under stand you Sir, Actually it is a Fiber Cable cut report which shows the break of cable from two ends e,g: A-
end and B-end it is only for you explain like this:- I check this and pulled it out manually from the report.

Cable cut detail	A-End	B-End
GTN: C-3 ICI near CM House to DC-57-Akbar Shaheed Chowk KLP	GTN: C-3 ICI near CM House to	DC-57-Akbar Shaheed Chowk KLP
GTN: DC-57 Akbar Shaheed Chowk KLP to C-3 ICI near CM House	GTN: DC-57 Akbar Shaheed Chowk KLP to	C-3 ICI near CM House
ZTE 10G: LT-LHR-Bahria OLT-2 to LT-LHR-AA and BB Bahria	ZTE 10G: LT-LHR-Bahria OLT-2 to	LT-LHR-AA and BB Bahria
BTN: BTN OLT-2 to AA and BB Block BTN	BTN: BTN OLT-2 to	AA and BB Block BTN
BTN: NEAR LEAD CLUB DEFENCE ROAD to 612 SHAHEEN Block Bahria Town	BTN: NEAR LEAD CLUB DEFENCE ROAD to	612 SHAHEEN Block Bahria Town
BTN: 612 Shaheen Block to Leads Club Bahria Town	BTN: 612 Shaheen Block to	Leads Club Bahria Town
SMD: C-11 Mehboob street to C-32 Cricket house GOR2 to MSAG-37GOR-III	SMD: C-11 Mehboob street to	Cricket house GOR2 to MSAG-37GOR-III
SMD: C-32 Cricket House to MSAG-37 GOR-III & C-11 Mehboob Street	SMD: C-32 Cricket House to	GOR-III & C-11 Mehboob Street
SMD: SMD OLT to MSAG-51 Afghani Road Mozang	SMD: SMD OLT to	to MSAG-51 Afghani Road Mozang
SMD: SMD Afghani Road Mozang to OLT	SMD: SMD Afghani Road to	Mozang OLT
SMD: B-27 Syed Plaza FZRD to C-29 Pakistani Chowk Ichra	SMD: B-27 Syed Plaza FZRD to	to C-29 Pakistani Chowk Ichra
SMD: MSAG-29 Pakistani Chowk to B-27 Syed Plaza Ferozpur Road	SMD: MSAG-29 Pakistani Chowk to	B-27 Syed Plaza Ferozpur Road
FZRD: 7009-Ufone to 7301-Ufone	FZRD: 7009-Ufone to	to 7301-Ufone
7009-Ufone to 7301-Ufone	7009-Ufone to	to 7301-Ufone
FZRD: C-48A HBFC Society to C-18 Aashyana Housing Society	FZRD: C-48A HBFC Society to	C-18 Aashyana Housing Society
WTN: 8062-WTN HW PTN to MSAG-38 Nespak Society	WTN: 8062-WTN HW PTN to	MSAG-38 Nespak Society
WTN: C-38 Nespak Society to 8062-WTN HW PTN	WTN: C-38 Nespak Society to	8062-WTN HW PTN
FZRD: C-18 Aashyana Housing Society to C-48A HBFC Society	FZRD: C-18 Aashyana Housing Society to	C-48A HBFC Society
FZRD: C-48A HBFC Society to C-18 Aashyana Housing Society	FZRD: C-48A HBFC Society to	C-18 Aashyana Housing Society

 

[Joe4]

So, do you actually have those "A-End" and "B-End" columns, like you show in your last image, or not?
If so, can we add helper columns like those?

In your last image, which records would you want highlighted?

Lastly, are you using Excel 2016 or Excel 2021 to do this (you have both listed)?

 

[AFZAL SOHAIL]

1. I have not A-end and B-end i show it only to understand you these are the stations names and have two ends
2.I want to highlight the same match the station name and occur again and again means duplicat
3. In my PC I have dual operating system so I have Excel-2016 and Excel-2021 and I am using both seperately

 

[Joe4]

OK, let's say the data is in the range A2:A20do the following:
1. Select the range A2:A20
2. Go to Conditional Formatting from the Data menu
3. Select "New Rule"
4. Select "Use a formula to determine which cells to format"
5. Enter in the following formula:

=COUNTIFS(A$2:A$20,"*"& LEFT(A2,FIND(" to ",A2)-1) &"*") + COUNTIFS(A$2:A$20,"*"& MID(A2,FIND(" to ",A2)+4,LEN(A2)) &"*")>2

6. Click the Format button
7. Choose your desired formatting
8. Click OK.

This should highlight all rows where the value on either side of the " to " word appears more than once in your data, like this:

Note if your make any changes to the range, be sure that the range in step 1 matches the search range in the formula.

 

[AFZAL SOHAIL]

Thank a lot Mr. Joe4 It is a good solution.
If I want to match Col-A with Col-B and Col-C, what is the formula for this.
Again thanks:
For example like this

Cable cut detail Col-A	A-End Col-B	B-End Col-c
GTN: C-3 ICI near CM House to DC-57-Akbar Shaheed Chowk KLP	GTN: C-3 ICI near CM House to	DC-57-Akbar Shaheed Chowk KLP

 

[Joe4]

I have data file in which a coloumn c have the following text strings, you see that sr.no.1 & sr.no.13, sr.no.2 and sr.4 are the same, I want to identify through formula the conditional formatting is not working because some are order is not same but they are the same faults

1. I have not A-end and B-end i show it only to understand you these are the stations names and have two ends

If I want to match Col-A with Col-B and Col-C, what is the formula for this.

Your posts are very confusing, as shown from the quotes above. I thought you said that you do NOT have the "A-end" and "B-end" columns, are said that you posted that just for understanding sake, but now you say you want to match them. I am guessing that perhaps language is a barrier, and you are not able to clearly state what you are after. So let's go about this another way.

I want you to post 2 screen prints:

Screen Print 1:
Post an example of what your original data looks like. DON'T add anything in for "understanding purposes" - just post your original data, as you have it.

Screen Print 2:
Using the data from screen print one, post EXACTLY what you want the final output to look like.
If you want data split into multiple columns, shows this.
Also if you want Conditional Formatting, make sure the appropriate cells are colored in this screen print.

If you can show me those two things, I can probably give you what you need to turn Screen Print 1 into Screen Print 2.