Duplicates / 2 columns

RLSSP

New Member
Joined
Nov 7, 2005
Messages
7
I have a list for example...

A B
1 Room 103 1/3/98
2 Room 103 1/3/98
3 Room 103 1/4/98
4 Ballroom 1/3/98
5 Ballroom 1/3/98


I am trying to write a formula or find a way to have it look at column A & B and delete if duplicate so I would only have based on the above information

A B
1 Room 103 1/3/98
2 Room 103 1/4/98
3 Ballroom 1/3/98
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Welcome to the board!

Try advanced filter to filter for unique records only.


As you ask for a formula:

=INDEX(A2:A$100,MATCH(0,--ISNUMBER(MATCH($A2:$A$100&$B2:$B$100,$D$1:$D1&$E$1:$E1,0)),0))

Is confirmed with Ctrl + shift + enter in D2, filled one cell right then down as far as needed.
Book1
ABCDEFG
1
2Room 1031/3/98Room 1031/3/98
3Room 1031/3/98Room 1031/4/98
4Room 1031/4/98Ballroom1/3/98
5Ballroom1/3/98
6Ballroom1/3/98
7
8
Sheet2
 
Upvote 0

RLSSP

New Member
Joined
Nov 7, 2005
Messages
7
Formula worked but computer locks up

The formula does work however there are 23011 rows of data and my computer continues to lock up when it calculates.
 
Upvote 0

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Yes the formula is very "expensive".

I guess you had better use my first suggestion; advanced filter.
 
Upvote 0

RLSSP

New Member
Joined
Nov 7, 2005
Messages
7
ADVERTISEMENT
Advance Filter

Do you mean put the formula in the criteria? I am not for sure how to do this.
 
Upvote 0
L

Legacy 51064

Guest
This code will delete all of the duplicates in column A2:A23013. It takes a minute to run the macro.

Sub TestForDups()

Dim LLoop As Integer
Dim LTestLoop As Integer
Dim LClearRange As String

Dim Lrows As Integer
Dim LRange As String
Dim LChangedValue As String
Dim LTestValue As String

'Test first 23011 rows in spreadsheet for uniqueness
Lrows = 23011
LLoop = 2

'Clear all flags
LClearRange = "A2:A" & Lrows
Range(LClearRange).Interior.ColorIndex = xlNone

'Check first 1000 rows in spreadsheet
While LLoop <= Lrows
LChangedValue = "A" & CStr(LLoop)

If Len(Range(LChangedValue).Value) > 0 Then

'Deactivate Screen Updating
Application.ScreenUpdating = False

'Test each value for uniqueness
LTestLoop = 2
While LTestLoop <= Lrows
If LLoop <> LTestLoop Then
LTestValue = "A" & CStr(LTestLoop)

'Value has been duplicated in another cell
If Range(LChangedValue).Value = Range(LTestValue).Value Then

'Delete all duplicates
Rows(CStr(LTestLoop) & ":" & CStr(LTestLoop)).Select
Selection.Delete Shift:=xlUp

End If

End If

LTestLoop = LTestLoop + 1

Wend

End If
LLoop = LLoop + 1

Wend

Range("B11").Select

End Sub
 
Upvote 0

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Re: Advance Filter

RLSSP said:
Do you mean put the formula in the criteria? I am not for sure how to do this.

No, just select your data, go to Data - Filter - Advanced filter.

In the filter box you have the option to check a check box to filter for unique records only.
 
Upvote 0

Forum statistics

Threads
1,195,681
Messages
6,011,129
Members
441,586
Latest member
rodsin76

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top