Find Duplicate Data with cretin critera

B

bboysen

New Member
Joined
Aug 6, 2010
Messages
44
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.
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Sort by date Sort by votes
Hi

so would you consider
F2ML-1
F2ML-2R
to be duplicate or not?

If yes, try this, copied down...
=IF(LEFT(A1,4)="F2ML",COUNTIFS($A$1:$A$8,"F2ML"&"*"),"")
 
Last edited:
Upvote 0
FDibbins said:
Hi

so would you consider
F2ML-1
F2ML-2R
to be duplicate or not?

If yes, try this, copied down...
=IF(LEFT(A1,4)="F2ML",COUNTIFS($A$1:$A$8,"F2ML"&"*"),"")
Click to expand...

Sorry I was not 100% Clear. That would not be a Duplicate but this would be.

F2ML-1
F2ML-2R
F2ML-1NW
F2ML-2

Reason being is they went back to the same weld and re-shot it and gave it a new weld number.
I would also like to add that the weld number could look like this

C2F2ML-1NW

The only common thing in each weld number is the - and the first Rig Number in this case it would be F2ML.


I am going to try your Formula and see how it works!

Thanks for the reply!
 
Last edited:
Upvote 0
OK sorry, im not sure I follow your logic :(

i would suggest you upload a sample work book, (all sensitive data removed), showing what data you are working with, a few examples of what your expected outcome is, and how you arrived at that
 
Upvote 0
FDibbins said:
OK sorry, im not sure I follow your logic :(

i would suggest you upload a sample work book, (all sensitive data removed), showing what data you are working with, a few examples of what your expected outcome is, and how you arrived at that
Click to expand...


I hear you on the logic at first I was the same way.

Here is a sample out of my master file

Excel 2010
A
F2ML-5353
F2ML-5294
F2ML-5295
F2ML-5296
G2F2ML-5353R
F2ML-5298
F2ML-5299
PI
FIELD
PI
C2F2ML-5300R
F2ML-5300
FIELD
F2ML-5302
HOG WIRE

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]11[/TD]

[TD="align: center"]12[/TD]

[TD="align: center"]13[/TD]

[TD="align: center"]14[/TD]

[TD="align: center"]15[/TD]

</tbody>
Sheet1​

Ok so A1 is a really a duplicate with A5 reason being they went back and re-shot the weld and gave it a new weld number now they have been changing them and adding things. I was looking for a way when I copy new data in to the work book I was planing on taking the Formula and converting it in to a conditional format for all the F2ML and then doing ones for the G2ML's that we have and so. It is a really big pain in the *** to explain but I will do my best.

Final out put would be something like this

Excel 2010
A
F2ML-5294
F2ML-5295
F2ML-5296
F2ML-5298
F2ML-5299
PI
FIELD
PI
FIELD
F2ML-5302

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FF0000"]F2ML-5353[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FF0000"]G2F2ML-5353R[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #FF0000"]C2F2ML-5300R[/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: #FF0000"]F2ML-5300[/TD]

[TD="align: center"]13[/TD]

[TD="align: center"]14[/TD]

</tbody>
Sheet1​

That way I can identify the weld with a number already in my overall master with out having to do a ton of search's.

Thanks for the help with this!:beerchug:
 
Upvote 0
bboysen said:
I hear you on the logic at first I was the same way.

Here is a sample out of my master file

Excel 2010
A
F2ML-5353
F2ML-5294
F2ML-5295
F2ML-5296
G2F2ML-5353R
F2ML-5298
F2ML-5299
PI
FIELD
PI
C2F2ML-5300R
F2ML-5300
FIELD
F2ML-5302
HOG WIRE

<tbody>
[TD="align: center"]1
[/TD]

[TD="align: center"]2
[/TD]

[TD="align: center"]3
[/TD]

[TD="align: center"]4
[/TD]

[TD="align: center"]5
[/TD]

[TD="align: center"]6
[/TD]

[TD="align: center"]7
[/TD]

[TD="align: center"]8
[/TD]

[TD="align: center"]9
[/TD]

[TD="align: center"]10
[/TD]

[TD="align: center"]11
[/TD]

[TD="align: center"]12
[/TD]

[TD="align: center"]13
[/TD]

[TD="align: center"]14
[/TD]

[TD="align: center"]15
[/TD]

</tbody>
Sheet1​

Ok so A1 is a really a duplicate with A5 reason being they went back and re-shot the weld and gave it a new weld number now they have been changing them and adding things. I was looking for a way when I copy new data in to the work book I was planing on taking the Formula and converting it in to a conditional format for all the F2ML and then doing ones for the G2ML's that we have and so. It is a really big pain in the *** to explain but I will do my best.

Final out put would be something like this

Excel 2010
A
F2ML-5294
F2ML-5295
F2ML-5296
F2ML-5298
F2ML-5299
PI
FIELD
PI
FIELD
F2ML-5302

<tbody>
[TD="align: center"]1
[/TD]
[TD="bgcolor: #FF0000"]F2ML-5353
[/TD]

[TD="align: center"]2
[/TD]

[TD="align: center"]3
[/TD]

[TD="align: center"]4
[/TD]

[TD="align: center"]5
[/TD]
[TD="bgcolor: #FF0000"]G2F2ML-5353R
[/TD]

[TD="align: center"]6
[/TD]

[TD="align: center"]7
[/TD]

[TD="align: center"]8
[/TD]

[TD="align: center"]9
[/TD]

[TD="align: center"]10
[/TD]

[TD="align: center"]11
[/TD]
[TD="bgcolor: #FF0000"]C2F2ML-5300R
[/TD]

[TD="align: center"]12
[/TD]
[TD="bgcolor: #FF0000"]F2ML-5300
[/TD]

[TD="align: center"]13
[/TD]

[TD="align: center"]14
[/TD]

</tbody>
Sheet1​

That way I can identify the weld with a number already in my overall master with out having to do a ton of search's.

Thanks for the help with this!:beerchug:
Click to expand...

How about PI and FIELD - aren't they also duplicates?
 
Upvote 0
Aladin Akyurek said:
How about PI and FIELD - aren't they also duplicates?
Click to expand...

Yes and no. They are describing a bend in pipe and the type of bend. That is why I just could not use the conditional formatting that excel has for duplicates because it would all be highlighted. :banghead:
 
Upvote 0
bboysen said:
Yes and no. They are describing a bend in pipe and the type of bend. That is why I just could not use the conditional formatting that excel has for duplicates because it would all be highlighted. :banghead:
Click to expand...

Yes and no? Excel cannot decide such for you, neither can I.

Do you have some list of what to exclude?
 
Upvote 0
What about this one...


Excel Workbook
AB
1NameDuplicate
2KamranKamran
3AdnanAdnan
4ImranImran
5Faizan#N/A
6Rehan
7Arsalan
8Kamran
9Adnan
10Imran
EXTRACT DUPLICATE VALUES
 
Upvote 0
Aladin Akyurek said:
Yes and no? Excel cannot decide such for you, neither can I.

Do you have some list of what to exclude?
Click to expand...
Or vice versa, do you want to search for duplicates only if they contain dash (-) in the name / tag?
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
VBA Help - Duplicate & Unwanted Data Removal
Replies
6
Views
930
MoonDancer
M
B
  • Solved
Macro to Open new tab and duplicate the data from the template, and rename new tab from cell value of template.
Replies
3
Views
568
mumps
mumps
H
  • Question Question
Finding Duplicate Values/ Different Sorting Methods
Replies
8
Views
556
DanteAmor
DanteAmor
H
  • Question Question
Removing Duplicate Entries
Replies
9
Views
343
Akuini
Akuini
R
  • Solved
Look-Up duplicate value in column S:S and return the date in B:B from the duplicate value
Replies
5
Views
755
Fluff
Fluff

Forum statistics

Threads
1,223,902
Messages
6,175,278
Members
452,629
Latest member
SahilPolekar

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top