Duplicates / 2 columns

[RLSSP]

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

 

[fairwinds]

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
	A	B	C	D	E	F	G
1
2	Room 103	1/3/98		Room 103	1/3/98
3	Room 103	1/3/98		Room 103	1/4/98
4	Room 103	1/4/98		Ballroom	1/3/98
5	Ballroom	1/3/98
6	Ballroom	1/3/98
7
8
Sheet2

 

[RLSSP]

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.

 

[fairwinds]

Yes the formula is very "expensive".

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

 

[RLSSP]

Advance Filter

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

 

[Legacy 51064]

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

 

[fairwinds]

Re: Advance Filter

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.

 

[RLSSP]

THANK YOU!!!

It worked SO EASY!