How to remove duplicates if?

July 11, 2018 at 20:15:59
Specs: Windows 7
hello, i would like be able to delete duplicates from column "loc" if line in column "task" is equal to "C" using macro.


loc task
M1 C
M1 C
M2 P
M2 P
M1 L
M2 E


See More: How to remove duplicates if?

Reply ↓  Report •

#1
July 12, 2018 at 05:45:46
First, a posting tip:

If you click on the How-To link at the bottom of this post, you can read the instructions on how to post data in this forum so that the format is retained. Please edit your data so that is conforms to that standard and includes column letters and row numbers so that there is no confusion regarding your data layout.

Second, I'm not sure what you mean by "duplicates" if task is C.

M1 is "duplicated" for the 2 C's but it also appears with L. Should both M1's for the C's be deleted or just 1 of the M1 next to the C or something else? IOW, what M1 (or M1's) does "duplicate" apply to?

Third, you say that you want to "delete duplicates from column loc". Are you asking for the cell itself to be cleared or for the entire row to be deleted or for the cell to be deleted with the other cells shifting up, etc.

IOW what exactly should be "deleted"?

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#2
July 12, 2018 at 10:20:20
deleted = cell cleared

and this should solve your doubts

input :

loc	task
M1	C
M1	C
M1	C
M2	P
M2	P
M1	L
M2	E

output :

loc	task
	C
	C
M1	C
M2	P
M2	P
M1	L
M2	E


Reply ↓  Report •

#3
July 12, 2018 at 10:35:09
Column letters? Row numbers? Should I make up my own?

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

Related Solutions

#4
July 12, 2018 at 10:59:04
input :

      
	A	B
1 	M1	C
2 	M1	C
3 	M1	C
4 	M2	P
5 	M2	P
6 	M1	L
7 	M2	E

output :

	
	A	B
1	 	C
2	 	C
3	 M1	C
4	 M2	P
5	 M2	P
6	 M1	L
7	 M2	E


Reply ↓  Report •

#5
July 12, 2018 at 12:34:15
Try this:

Sub delDups()
Dim lastRw As Long
Dim mc As Range
Dim firstAddress As String

'Determine last Row with data
   lastRw = Cells(Rows.Count, 2).End(xlUp).Row

'Insert Helper column to Concatenate Columns A&B
  Columns(3).Insert shift:=xlToRight
    Range(Cells(2, 3), Cells(lastRw, 3)).Formula = "=A2&B2"
    Range(Cells(2, 3), Cells(lastRw, 3)) = Range(Cells(2, 3), Cells(lastRw, 3)).Value
  
'Search for M1C in Helper column, Delete dups until only 1 is left
   With Columns(3)
    Set mc = .Find("M1C", after:=Cells(1, 3))
     If Not mc Is Nothing Then
        firstAddress = mc.Address
        Do
         If WorksheetFunction.CountIf(Columns(3), "M1C") <> 1 Then
          Cells(mc.Row, 1).ClearContents
          Cells(mc.Row, 3).ClearContents
         Else
          Columns(3).Delete shift:=xlToLeft
          GoTo DoneFinding
         End If
           
         Set mc = .FindNext(mc)
          If mc Is Nothing Then
            GoTo DoneFinding
          End If
        Loop While mc.Address <> firstAddress
      End If

DoneFinding:
   End With

End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

Ask Question