How do I find duplicate values 1

[splendidmonkey]

How do I find duplicate values

Don’t say use find and replace there are over 20,000 possible duplicate codes

I have an excel sheet with 3 columns

It looks like this

Column 1

2513
244
2235
1247
1733
1020
2000
2277

Column 2

381
376
375
372
367
359
358
356

Column 3

0#CNGEQCH.LP 0#HKGARB.LP 0#HKGBDAPC.LP 0#HKGBDAPCCO.LP 0#HKGBDASD.LP
0#.DJIW.HK 0#.HSCCW.HK 0#.HSCEW.HK 0#.HSFM25W.HK 0#.HSIW.HK
/1SELH4 /1SELH5 /1SELH6 /1SELH7 /1SELH8
/A#EQCITP_t.PA /A#FOR2_t.S /A#FOR4_t.S /A#FOR5_t.S /A#IWNT.VI
/AEG04SB.AT /AEG04SL.AT /AKT04SB.AT /AKT04SL.AT /ALA04SB.AT
.AM.AS .AM.AT .AM.AVK .AM.BE .AM.BM
0#ABANA.IS 0#ACIBD.IS 0#ADANA.IS 0#ADBGR.IS 0#ADEL.IS
0#1515C2.T 0#1802C7.T 0#1815C3.T 0#1820C7.T 0#1820C8.T

Now what I need to do is search for duplicate values in column 3 to make this harder I am not looking for the whole contents of the cell in column 3 I am looking for individual codes, each cell in column 3 contains between 3 and 11 codes i.e. 1st in column 3 is 0#CNGEQCH.LP I need to find duplicates of this in other cells.
That cell also contains 0#HKGARB.LP 0#HKGBDAPC.LP 0#HKGBDAPCCO.LP 0#HKGBDASD.LP I need to find duplicate occurrences of these also and so on
This table is over 6000 rows long so I really need to do a batch job as individually it would take the rest of my life

Any help would nice

 

[RamziSaab]

are the 'codes' all sperateed by spaces?

and do you just need to search column 3 for duplicates?

 

[RamziSaab]

also what do u want to do once you have found the codes?

i think u can do what u want using the Instr() function in vba.

 

[splendidmonkey]

yes all the codes are sperateed by spaces

and yes only column 3

 

[RamziSaab]

and what do u want to do with the values once they are found? just count them?

 

[splendidmonkey]

List them
a good start would be just to list them

better would be to list them with the id in column 1 so if the same value is in two different rows in column 3 it would also list the row by coulum 1

i.e. 0#CNGEQCH.LP is in 2513 and 244

 

[RamziSaab]

ok here is somethng to start you off.

this macro will seperate all the codes into seperate columns
then it is really easy to compare them, which i am sure u can figure out

notes:
n = column where u want the listing to start - 1 (e.g if you want it to start on column 3 n =2)
strname = .Cells(myrows, COLUMN WHRE UR DATA IS) <--change this in the code

**********************************************************
Sub seperate()

With ThisWorkbook.Worksheets(&quot;Sheet1&quot
r = .UsedRange.Rows.Count
n = 1
For myrows = 1 To r
n = 1
strname = .Cells(myrows, COLUMN WHRE UR DATA IS)
For i = 1 To Len(strname)
yy = y
y = InStr(yy + 1, strname, &quot; &quot
If y = 0 Then
n = n + 1
strcode = Mid(strname, yy + 1, Len(strname))
.Cells(myrows, n) = strcode
Exit For
Else
strcode = Mid(strname, yy + 1, y - (yy + 1))
n = n + 1
.Cells(myrows, n) = strcode
End If
Next i
Next myrows
End With
End Sub
***********************************************************

tell if this works or u have problems...

 

[splendidmonkey]

Macro used provided by RamziSaab (MIS)
cheers m8

Sub seperate()

With ThisWorkbook.Worksheets(&quot;Sheet1&quot
r = .UsedRange.Rows.Count
n = 1
For myrows = 1 To r
n = 4
strname = .Cells(myrows, 4)
For i = 1 To Len(strname)
yy = y
y = InStr(yy + 1, strname, &quot; &quot
If y = 0 Then
n = n + 1
strcode = Mid(strname, yy + 1, Len(strname))
.Cells(myrows, n) = strcode
Exit For
Else
strcode = Mid(strname, yy + 1, y - (yy + 1))
n = n + 1
.Cells(myrows, n) = strcode
End If
Next i
Next myrows
End With
End Sub

Sub find_id()
With ThisWorkbook.Worksheets(&quot;Sheet1&quot

r = .UsedRange.Rows.Count
c = .UsedRange.Columns.Count
l = 2
For mycols = 5 To c
For myrows = 3 To r
strid = &quot;&quot;
For myrows2 = myrows To r
If .Cells(myrows, mycols) = .Cells(myrows2, mycols) Then
If .Cells(myrows, mycols) = &quot;&quot; Then
Else
l = l + 1
.Cells(l, 16) = .Cells(myrows, mycols)
strid = strid & .Cells(myrows2, 2) & &quot;, &quot;
.Cells(l, 17) = strid
End If
End If
Next
Next
Next
End With

End Sub