Use:
=SI(NB.SI($A$1:A1;A1)>1; »Doublon »; »")
“A” being the column to look for duplicate. If you need to look for duplicate in more than one column, concatenante the columns using =(A1&B1) from the columns C as an exemple and then use:
=SI(NB.SI($C$1:C1;C1)>1; »Doublon »; »")
from columns D.
apply auto filter and remove all the row having “doublon” in the column you pasted the formula.
This is almost a reminder for me. When I need it I alsways forget the name of the utility 
So here it is… BLATavailable at http://sourceforge.net/project/showfiles.php?group_id=81910
If the url is not working anymore, just need to google blat and you’ll find thousand of link for it.
Did I say it? It’s free.
Ciao
You need to use this script in a button control.
You also need to register the Microsoft Scripting runTime dll from Tools -> Reference in your VBA Editor.
Code:
| Private Sub CommandButton1_Click() | |
| ‘Partie Filtre | |
| Selection.AutoFilter Field:=4, Criteria1:= »CDCAM » | |
| Selection.AutoFilter Field:=15, Criteria1:= »<>DEL », Operator:=xlAnd, _ | |
| Criteria2:= »<>del » | |
| ‘Range(« A1:N1000″).Select | |
| ‘Selection.Copy | |
| ‘Partie export au fichier | |
| ‘Dim fso | |
|
|
‘Set fso = CreateObject(« Scripting.FileSystemObject ») |
|
|
Dim fso As New Scripting.FileSystemObject |
|
|
Dim ts ‘TextStream |
|
|
Set ts = fso.CreateTextFile(« DriveLetter:Full Pathoutput.txt », True, True) |
|
|
Dim col As Integer |
|
|
col = 14 ‘La denriere colonne a prendre |
|
|
Dim row As Integer |
|
|
row = 500 ‘La dernier rangée a prendre. Pour sauver du temps je ne fait pas de check sur la derniere rangee occuppé. |
|
|
Dim i As Integer |
|
|
Dim j As Integer |
|
|
‘ Delim = IIf(.obCharacter, .tbDelimiter, Chr(9)) |
|
|
For i = 1 To row |
|
|
For j = 1 To col |
|
|
If j = col Then |
|
|
ts.Write CStr(Cells(i, j)) |
|
|
Else |
|
|
ts.Write CStr(Cells(i, j)) & Chr(9) ‘TAB |
|
|
End If |
|
|
Next |
|
|
ts.Write (vbCrLf) |
|
|
Next |
|
|
ts.Close |
|
|
End Sub |
Ciao.