I have the following two columns of data:
Location Value A 1 A 1 B 2 C 2 D 3 D 3 E 4 E 4 F 5 G 5
If there is a duplicate combination of
Value, it has to be DELETED. If I use the remove duplicate option, the duplicates are removed but retains one of the repeated values and that is not favorable for me. My intended output is this:
Location Value B 2 C 2 F 5 G 5
Here is what I've thought and tried so far:
I concatenated the two columns and then used
COUNTIF to get a count of the number of times a value is repeated. I can then filter the count column by only considering the counts that equal to 1. This is shown here:
However, this method of mine is not feasible dues to various other filters and sorting that has been done in my spreadsheet.
Are there any other suggestions to achieve the same results as I have?
Public Function allDuplicates() Dim a As Application Set a = Application Dim arrayRows() As Boolean maxCol = 2 firstRow = 2 Dim wks As Worksheet Set wks = ActiveSheet wks.Application.ScreenUpdating = False totalRows = wks.Cells(Rows.Count, "A").End(xlUp).Row ReDim arrayRows(totalRows) For i = firstRow To totalRows theRow = Join(a.Transpose(a.Transpose(wks.Range(Cells(i, 1), Cells(i, maxCol)))), Chr(0)) For j = i + 1 To totalRows theOtherRow = Join(a.Transpose(a.Transpose(wks.Range(Cells(j, 1), Cells(j, maxCol)))), Chr(0)) If theRow = theOtherRow Then arrayRows(i) = True arrayRows(j) = True End If Next j Next i For i = firstRow To totalRows If arrayRows(i) = True Then wks.Rows(i).Clear End If Next i Range("A1:B" & totalRows).Sort key1:=Range("A2:A" & totalRows), order1:=xlAscending, Header:=xlYes wks.Application.ScreenUpdating = True Message = MsgBox("Finished", vbInformation) End Function
Open Visual Basic /Macros, add a module under ThisWorkbook, paste the code on the right side and execute it.