Find Duplicate Data with cretin critera

[bboysen]

Ok guys and gals,

I am trying to find Duplicate Data inside of a file with 15000 records.

This is what I have

Column 1 has weld numbers that are formatted as such:

F2ML-1
F2ML-2R
CTI-1
CTI-2

Now what I looking to find is anything that as the F2ML on it with the same number.
But see the welds can be any where in the file and I just cant sort to find them.
I would use the conditional formatting but there is also other info between the welds.
Like this

F2ML-1
BND
PI
BND
F2ML-2

And so on.

Is there an easy way to find the duplicated data?

I do not know if VBA or Formula would be the best route or just copying to a different file and sorting it.

 

[bboysen]

Or vice versa, do you want to search for duplicates only if they contain dash (-) in the name / tag?

Yes that would be a common thing, and so would the F2ML or C2ML or D2ML and I only want to check between the same rig. Here is a brake down of how the weld numbers a decided.
Rig Name (F)
Spread Working On (2)
Type of Work Done (ML or TI)
-
then the next number.

But as seen before they have to go back and re-weld somethings and if it is a different crew it get like this-
C2F2ML-1212. Now on this only I am only looking to compare the First rig. In this case it would be F2ML.

All your guys help has be great.
Thanks again Guys!

 

[taurean]

Ok. Here is one way.

1. Assuming that your data is in Column A, first set autofilter and select all records that begin with F2ML. Copy these rows to a suitable column.

2. Then using these copied values as basis then apply formula as below. In my case, I copied these values to Column C.

<tbody>
[TD="class: xl63"]F2ML-5353[/TD]
[TD="class: xl63, width: 72"][/TD]
[TD="class: xl63, width: 72"]F2ML-5353[/TD]

[TD="class: xl63"]F2ML-5294[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]F2ML-5294[/TD]

[TD="class: xl63"]F2ML-5295[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]F2ML-5295[/TD]

[TD="class: xl63"]F2ML-5296[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]F2ML-5296[/TD]

[TD="class: xl63"]G2F2ML-5353R[/TD]
[TD="class: xl63"]F2ML-5353[/TD]
[TD="class: xl63"]F2ML-5298[/TD]

[TD="class: xl63"]F2ML-5298[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]F2ML-5299[/TD]

[TD="class: xl63"]F2ML-5299[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]F2ML-5300[/TD]

[TD="class: xl63"]PI[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]F2ML-5302[/TD]

[TD="class: xl63"]FIELD[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]

[TD="class: xl63"]PI[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]

[TD="class: xl63"]C2F2ML-5300R[/TD]
[TD="class: xl63"]F2ML-5300[/TD]
[TD="class: xl63"][/TD]

[TD="class: xl63"]F2ML-5300[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]

[TD="class: xl63"]FIELD[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]

[TD="class: xl63"]F2ML-5302[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]

[TD="class: xl63"]HOG WIRE[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]

</tbody>

Then in cell B1 I wrote following formula and copied down:
=IFERROR(IF(LOOKUP(999,FIND($C$1:$C$8,$A1,1),$C$1:$C$8)<>$A1,LOOKUP(999,FIND($C$1:$C$8,$A1,1),$C$1:$C$8),""),"")

 

[bboysen]

Ok. Here is one way.

1. Assuming that your data is in Column A, first set autofilter and select all records that begin with F2ML. Copy these rows to a suitable column.

2. Then using these copied values as basis then apply formula as below. In my case, I copied these values to Column C.

<tbody>
[TD="class: xl63"]F2ML-5353[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63, width: 72"]F2ML-5353[/TD]

[TD="class: xl63"]F2ML-5294[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]F2ML-5294[/TD]

[TD="class: xl63"]F2ML-5295[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]F2ML-5295[/TD]

[TD="class: xl63"]F2ML-5296[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]F2ML-5296[/TD]

[TD="class: xl63"]G2F2ML-5353R[/TD]
[TD="class: xl63"]F2ML-5353[/TD]
[TD="class: xl63"]F2ML-5298[/TD]

[TD="class: xl63"]F2ML-5298[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]F2ML-5299[/TD]

[TD="class: xl63"]F2ML-5299[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]F2ML-5300[/TD]

[TD="class: xl63"]PI[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]F2ML-5302[/TD]

[TD="class: xl63"]FIELD[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]

[TD="class: xl63"]PI[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]

[TD="class: xl63"]C2F2ML-5300R[/TD]
[TD="class: xl63"]F2ML-5300[/TD]
[TD="class: xl63"][/TD]

[TD="class: xl63"]F2ML-5300[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]

[TD="class: xl63"]FIELD[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]

[TD="class: xl63"]F2ML-5302[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]

[TD="class: xl63"]HOG WIRE[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]

</tbody>

Then in cell B1 I wrote following formula and copied down:
=IFERROR(IF(LOOKUP(999,FIND($C$1:$C$8,$A1,1),$C$1:$C$8)<>$A1,LOOKUP(999,FIND($C$1:$C$8,$A1,1),$C$1:$C$8),""),"")

That Could work. Thanks I will give it a run!

 

[bboysen]

Ok how about something like this

=IF(((MID($A2,SEARCH("-",$A2)-4,4))="F2ML"),COUNTIF($H:$H,MID($A2,SEARCH("-",$A2)-4,9))>1)

The only thing with this is that it will only work on welds with only F2ML if you add C2 to the F2ML or and R to the end of the number it give a false statement.

Also will work only with F2ML-0000