Solved: Strange find non duplicates

[cdkeito]

Hi all,

a lite problem for you:

Cartel1
	A	B	C	D
1	bla	u	1	bla
2			1	blo
3			1	bli
4	blo	du	2	bla
5			2	blo
6			2	bli
7			2	bla
8			2	blo
9	bli	tri	3	bli
10			3	bla
11	blu	quater	4	blo
12			4	bli
13	ble	sic	5	bla
14			5	blo
15			5	bli
16			5	bla
17			5	blo
18			5	bli
19			5	bla
20			5	blo
21	bly	ses	6	bli
22			6	bla
23			6	blo
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

 

[cdkeito]

the is an empty cell

 

[cdkeito]

---

 

[Peter_SSs]

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]

Cartel1
	A	B	C	D	E
1	dfdsf	a	1	sds	dsd
2			1	fdsf	fdsf
3	fdfs	b	2	dfds	fjghj
4			2	gfdg	gfdgfd
5			2	gdf	fgdf
6			2	utyu	khjk
7	hgfh	c	9	fdjhg	jyjn
8			9	bhcf	hbcgfh
9			9	nfcgh	hcnf
10	,l,ihg	a	45	jkgh,	mkghj
11			45	jm	kmkj
12			45	ghmnjfg	fjn
13	jnfhj	d	6	nfjghf	fnj
14	dfg	e	78	fmj	fhjm
15			78	jfm	,.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!

 

[cdkeito]

all cells're unmerged (like the 1st table)
thanks

 

[Peter_SSs]

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
	A	B	C	D	E
1	Column 1	Column 2	Column 3	Column 4	Column 5
2	dfdsf	a	1	sds	dsd
3			1	fdsf	fdsf
4	fdfs	b	2	dfds	fjghj
5			2	gfdg	gfdgfd
6			2	gdf	fgdf
7			2	utyu	khjk
8	hgfh	c	9	fdjhg	jyjn
9			9	bhcf	hbcgfh
10			9	nfcgh	hcnf
11	,l,ihg	a	45	jkgh,	mkghj
12			45	jm	kmkj
13			45	ghmnjfg	fjn
14	jnfhj	d	6	nfjghf	fnj
15	dfg	e	78	fmj	fhjm
16			78	jfm	,.lh
Delete Non Duplicates

Mr Excel.xls
	A	B	C	D	E
1	Column 1	Column 2	Column 3	Column 4	Column 5
2	dfdsf	a	1	sds	dsd
3	,l,ihg	a	45	jkgh,	mkghj
4
Delete Non Duplicates

 

[cdkeito]

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]

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]

I've written some 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?