Solved: Strange find non duplicates

cdkeito

Board Regular
Joined
Aug 26, 2005
Messages
95
Hi all,

a lite problem for you:
Cartel1
ABCD
1blau1bla
21blo
31bli
4blodu2bla
52blo
62bli
72bla
82blo
9blitri3bli
103bla
11bluquater4blo
124bli
13blesic5bla
145blo
155bli
165bla
175blo
185bli
195bla
205blo
21blyses6bli
226bla
236blo
Foglio1



How do I find and delete non duplicates value in column B?

A rule say that every value in B has a different value in C, so a duplicate has the same value in B but different in C.


thannnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnks
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,735
Office Version
365
Platform
Windows
cdkeito

I'm not quite sure of your explanation. In the sample shown, which values would be deleted (if any)?

By the way, your sheet will look better on the board and be easier to read without all those things everywhere. You can avoid those by clicking the 'View Source' button in the HTML Maker (rather than the 'Please click this button to send the source to the clipbord' button) and then selecting all the resulting code to copy to your post.
 

cdkeito

Board Regular
Joined
Aug 26, 2005
Messages
95
Cartel1
ABCDE
1dfdsfa1sdsdsd
21fdsffdsf
3fdfsb2dfdsfjghj
42gfdggfdgfd
52gdffgdf
62utyukhjk
7hgfhc9fdjhgjyjn
89bhcfhbcgfh
99nfcghhcnf
10,l,ihga45jkgh,mkghj
1145jmkmkj
1245ghmnjfgfjn
13jnfhjd6nfjghffnj
14dfge78fmjfhjm
1578jfm,.lh
Foglio1


In this example, you see, b1 and b10 are the same. I need to delete rows 3 :9;13:15.

I've writen something that use countif (on rows 1,3,7,10,13,14) and a later scan that analyze the results, storing the rows' number to delete (countif = 1); but, probably, you've a better idea.

Grazie!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,735
Office Version
365
Platform
Windows
cdkeito

See if this is any use.

1. Insert a row above your data and add headings (like first sample below).
2. Select the headings and then Data|Filter|AutoFilter.
3. Click the arrow in column 2 and choose 'Custom'.
4. In the Custom AutoFilter dialog, choose 'does not equal' in the top left box and 'a' in the top right box. Click OK.
5. Select the visible data (except headings).
6. Edit|Delete Row.
7. Change column 2 filter back to all (or remove AutoFilter altogether).
8. Should end up with second sample below.

Is that what you wanted?
Mr Excel.xls
ABCDE
1Column 1Column 2Column 3Column 4Column 5
2dfdsfa1sdsdsd
31fdsffdsf
4fdfsb2dfdsfjghj
52gfdggfdgfd
62gdffgdf
72utyukhjk
8hgfhc9fdjhgjyjn
99bhcfhbcgfh
109nfcghhcnf
11,l,ihga45jkgh,mkghj
1245jmkmkj
1345ghmnjfgfjn
14jnfhjd6nfjghffnj
15dfge78fmjfhjm
1678jfm,.lh
Delete Non Duplicates
Mr Excel.xls
ABCDE
1Column 1Column 2Column 3Column 4Column 5
2dfdsfa1sdsdsd
3,l,ihga45jkgh,mkghj
4
Delete Non Duplicates
 

cdkeito

Board Regular
Joined
Aug 26, 2005
Messages
95
sorry, it' wrong.

I need, at the end, rows 1.2;10:12.
Manualy is very simple, but I need a macro to analyze hundreds of sheets.

thanks for your help.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,735
Office Version
365
Platform
Windows
cdkeito

Sorry, I mis-interpreted your previous post. I will have a look tomorrow or sometime soon if nobody else has provided a successful solution.

Are you sure you want a macro to do it?
 

cdkeito

Board Regular
Joined
Aug 26, 2005
Messages
95
I've written some code:

Code:
Sub DeleteNoDplARP()
    Application.ScreenUpdating = False
    Dim k As Integer
    Dim r As Range
    Dim f As Range
        
    k = Sheets("Report Presenze").Range("d65536").End(xlUp).Row
    Columns("E").Insert shift:=xlToRight
    For Each r In Sheets("Report Presenze").Range("a10", "a" & k)
        If r.Offset(, 2) <> "" Then
            r.Offset(, 4).Formula = "=countif(c$10:c$" & k & ",c" & r.Row & ")"
        End If
    Next
    Range("e10:e" & k).Select
    'Columns("e").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, operation:=xlNone, skipblanks:=False, Transpose:=False
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
    riga = Nothing
    For Each r In Sheets("Report Presenze").Range("a10", "a" & k)
        If r.Offset(, 2) <> "" And Not riga Is Nothing Then
            Rows(riga).Delete
            riga = Nothing
        End If
        If r.Offset(, 2) <> "" And r.Offset(, 4) = 1 And riga Is Nothing Then
            riga = r.Address(0, 0)
        End If
        If r.Offset(, 2) = "" And Not riga Is Nothing Then
            riga = riga & "," & r.Address(0, 0)
        End If
    Next
    Range(riga).EntireRow.Delete
    Columns("E").Delete shift:=xlToLeft
    For Each r In Sheets("Report Presenze").Range("a10", "a" & k)
        If r.Value = "" Then r.FormulaR1C1 = "=R[-1]C"
        If r.Offset(, 1).Value = "" Then r.Offset(, 1).FormulaR1C1 = "=R[-1]C"
        'If r.Offset(, 2).Value = "" Then r.Offset(, 2).FormulaR1C1 = "=R[-1]C"
    Next
    'Range("A10:AQ" & mylastrow).Sort Key1:=Range("B10"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
    Application.ScreenUpdating = True
End Sub
some hope?
 

Forum statistics

Threads
1,077,834
Messages
5,336,661
Members
399,095
Latest member
globz

Some videos you may like

This Week's Hot Topics

Top