How find Duplicate entries across 2 Columns in Access and line them up?

[maximus007]

Hi everybody!

I'm new to this forum.

There is an issue that I cannot resolve in MS Access:

I have a large files with multiple columns that has 2 columns that randomly contain duplicated data.

I would like to filter out the duplicated entries in these 2 columns without spoiling the data integrity - and so the duplicates appear on the same row.

Please help me out with this, I have been searching for a solution for days without any success. I think what I want it pretty much like conditional formatting in excel, but unfortunately the file size is really large to be filtered in excel.

Thank you very much!

[June7]

You want to show only one record but not delete the duplicate? You want to show all the fields or just these two? If the other fields are not duplicate data, how do we know which one to show?

[losstww]

One method would be to reiterate through a recordset comparing column 1 against column 2 and then clearing column 2 of the duplicate data. This method assumes that the duplicate data is in the same row, just different column

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb

Set rst = db.Openrecordset("tableName or Query")

with rst
Do While Not rst.EOF
.Edit
If rst!Column1 = rst!Column2 Then
rst!Column2 = ""
End If
.Update
rst.MoveNext
Loop

[maximus007]

Thank you for your above replies!

The duplicates are in same columns, but different rows.


Want to check for duplicates in 2 columns, but display all columns only with duplicate results(actually the duplicates aren't a problem but exactly what I need to see, since I'm putting together 2 different groups of data that contain similar records, if there is an easier way to put them on the same line I would appreciate your comments),
The first column to be compared has duplicates, the second one doesn't.

Please let me know if I can clarify this further.
Thank you!

[June7]

That confuses me even more. You 'want to check for duplicates in 2 columns' but then state 'first column to be compared has duplicates, the second one doesn't'.

Re: 'if there is an easier way to put them on the same line', review http://allenbrowne.com/func-concat.html