Help finding duplicate info in Excel in different columns
Hi, I have an excel spreadsheet that looks like below:
Origin Store1 Store2 Percent Source
Warren 12345 45678 2.45 b
Warren 45678 12345 2.45 r
I want to be able to recognize and delete one of these rows as a duplicate. The info is the same just in a different column whether it be Store1 or Store2, and the source doesn't really matter. When it comes down to r or b I will keep r.
I have over 32000 rows with probably 25% of them duplicates like above and am looking for an easy way to identify and delete them.
Any ideas would be much appreciated
Software/Hardware used:
Software/Hardware used:
ASKED:
October 3, 2008 12:26 AM
UPDATED:
October 8, 2008 1:57 AM
RELATED QUESTIONS
- Pros & cons to have a typical star schema but with only one fact value field and a thousand attribute names in one of the dimension tables?
- How do I read a File to end active jobs within my CLP?
- Row by Row Stored Procedure in SQL Server 2008 R2
- Conditional Formatting - Excel 2007
- Challenge improving query performance on large tables
Answer Wiki:
I think I have a solution for your problem.
Take a <b>copy your file for safety</b>.
In the copied file You have to write macros to solve the problem.
Just <b>copy & paste the code below in a module</b>.
Then <b>first, run the macro "FindDuplicate"</b>, to mark duplicate rows.
Then check if everything is ok, <b>then run second macro "DeleteDuplicateRows"</b>, to delete the duplicate rows.
To write macro in Excel, "Tools" menu ---> "Macro" , "Visual Basic Editor"
In Visual Basic Editor, "Insert" menu ---> "Module"
In Module copy & paste the following code.
Then to run the macro in Excel, "Tools" menu ---> "Macro", "Macros..."
Then select the macro name, click on "Run"
Please tell me weather it helps or not.
Mayuri Shah
dhimay@hotmail.com
'=========================== CODE START ===========================
Dim LastRow As Integer 'last row number of data
Public Sub FindDuplicate()
'Select entire data for sorting
Range("A1").Select
LastRow = ActiveCell.SpecialCells(xlLastCell).Row
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
'Sort selected data in this order - 1. origin asc, 2. percent desc, 3. source desc (desc because u want to keep 'r')
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("D2") _
, Order2:=xlAscending, Key3:=Range("E2"), Order3:=xlDescending, Header _
:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom _
, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
Call FindInGroup
End Sub
Private Sub FindInGroup()
'Here first I have grouped and selected the rows where origin and percent are same, means within these rows we have to find duplicates of each other
Dim RowNum, RngRowStrt As Integer
Dim AColText, DColText As String
RngRowStrt = 2
Do
AColText = Range("A" & RngRowStrt).Text
DColText = Range("D" & RngRowStrt).Text
RowNum = RngRowStrt + 1
Do
If Range("A" & RowNum).Text <> AColText Or Range("D" & RowNum).Text <> DColText Then
Exit Do
End If
RowNum = RowNum + 1
If RowNum > LastRow Then Exit Do
Loop
Range("A" & RngRowStrt, "E" & RowNum - 1).Select 'selects the group data
Call BorderData
'Within selected group data searches for duplicates
'These two loops compares each row in selection with each and every row in selection
For i = RngRowStrt To RowNum - 2
For j = i + 1 To RowNum - 1
If (Range("B" & i) = Range("B" & j) And Range("C" & i) = Range("C" & j)) Or (Range("B" & i) = Range("C" & j) And Range("C" & i) = Range("B" & j)) Then
Range("G" & j) = "Duplicate"
End If
Next
Next
If RowNum > LastRow Then Exit Do
RngRowStrt = RowNum
Loop
MsgBox "All the duplicate rows are marked as ' Duplicate ', in column ' G '." & VBA.Chr(13) & "Please check it once. If you find it proper then please run the macro ' DeleteDuplicateRows ' to delete all duplicate rows permanently."
Range("A1").Select
End Sub
Private Sub BorderData()
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
End With
End Sub
Public Sub DeleteDuplicateRows()
For i = LastRow To 2 Step -1
If Range("G" & i).Text = "Duplicate" Then
Rows(i).Select
Selection.Delete Shift:=xlUp
'Rows("29:29").Select
End If
Next
Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
End Sub
*****************************************************************************************************************
THIS WORKED PERFECTLY!!!!!!!
THANK YOU SO MUCH.
'=========================== CODE END ===========================
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
