Identify Duplicates Between 2 Columns Of Different Sheets

Deema · Mar 14th 2008

I have two spreadsheet in which I am looking for duplicate customer names.

1st - "Customers"
2nd - "Orders"

In column A of my Customers spreadsheet I have the following formula
=IF(ISERROR(MATCH(E2,Orders$D2:D1000,0))=FALSE, "Y","N"). This allows me to see if a name in my Customer spreadsheet (column E is a field of names) is also in my Orders spreadsheet (column D is a field of names). My end results is either a Y indicating a duplicate or a N for no duplicates.

What I am looking to do next is to place in column B of my Customers spreadsheet the date that coincides with the duplicating customer name. Sorry to be confusing. If a customers name is found in the Orders spreadsheet I need to know what date has been given to this customer (column L in the Orders spreadsheet contains the date information).

However, on numerous occasions a customers name is found in the Orders spreadsheet more than once meaning there may be more than one date which needs to be placed in column B of my Customers spreadsheet.

I am stuck as to how to gather this information. Any help would be greating appreciated.

Simon Lloyd · Mar 14th 2008

Re: Identify Duplicates Between 2 Columns Of Different Sheets

If you have your customers listed contiguously in the customers sheet how do you propose you get more than one date found for a customer in column B? the date would look like it belonged to the next customer!

Deema · Mar 14th 2008

Re: Identify Duplicates Between 2 Columns Of Different Sheets

Yes, the names in the Customer spreadsheet will only appear once and will be maintained in alphabetical order. However, the names in the Order sheet may appear more than once and each occurrence will also have a different date.

Example - CUstomer spreadsheet - name is Williams

Order spreadsheet - Williams is found twice but one date is 01/28/08 and another is 02/06/08 so on the Customer spreadsheet . . . Williams is a Y (in column A) for being found on the Orders spreadsheet and in column B you would find the dates 01/28/08, 02/06/08?

I hope this makes more sense. If not I can send an example

Simon Lloyd · Mar 14th 2008

Re: Identify Duplicates Between 2 Columns Of Different Sheets

Yes, i understand that but would you want the dates one under the other or in the same cell?, because one under the other means inserting a row(s) between your customers to allow for multiple dates!

Deema · Mar 14th 2008

Re: Identify Duplicates Between 2 Columns Of Different Sheets

I am actually looking to have them in the same cell possibly separated by a comma? Is this possible or would I have to create additional columns?

Thanks again, Denise

Simon Lloyd · Mar 14th 2008

Re: Identify Duplicates Between 2 Columns Of Different Sheets

Ok try this:

Code

Sub Find_All_Dates()
Dim Rng As Range, Rng1 As Range, MyCell As Range, Cell As Range
Set Rng = Sheets("Sheet2").Range("A1:" & Range("A65536").End(xlUp).Address)
Set Rng1 = Sheets("Sheet1").Range("A65536").End(xlUp)
For Each MyCell In Rng
With Sheets("Sheet1").Range("A:A")
.AutoFilter Field:=1, Criteria1:=MyCell
End With
For Each Cell In Sheets("Sheet1").Range("A2:" & Rng1.Address).SpecialCells(xlCellTypeVisible)
        If Cell.Value <> "" Then
        MyCell.Offset(0, 1).Value = MyCell.Offset(0, 1).Value & "," & _
        Cell.Offset(0, 1).Value
        Else: GoTo Nxt
        End If
    Next
    
Nxt:
    Next
sheets("Sheet1").autofiltermode=false
End Sub

Display More

Deema · Mar 14th 2008

Re: Identify Duplicates Between 2 Columns Of Different Sheets

Unfortunately, I know very little of VBA . . . have a few questions if you still have the time?

Sheet 1 - I should change to Customers
Sheet 2 - should change to Orders

but then I am very uncertain as to what else I need to change as when I run I get a 400 error.

These are my columns:
Customers spreadsheet
Column - Description
A - identifies a duplicate with a Y or N
B - would like the dates to drop here
E - names are in this column

Orders spreadsheet
B - names are in this column
L - dates appear in this column

Simon Lloyd · Mar 14th 2008

Re: Identify Duplicates Between 2 Columns Of Different Sheets

Code

Sub Find_All_Dates() 
    Dim Rng As  Range, Rng1 As Range, MyCell As Range, Cell As Range 
    Set Rng =  Sheets("Customers").Range("A1:" & Range("A65536").End(xlUp).Address) 
    Set Rng1 = Sheets("Orders").Range("A65536").End(xlUp) 
    For Each MyCell In Rng 
        With Sheets("Orders").Range("A:A") 
            . AutoFilter Field:=1, Criteria1:=MyCell 
        End With 
        For Each Cell In Sheets("Orders").Range("A2:" & Rng1.Address).SpecialCells(xlCellTypeVisible) 
            If Cell.Value <> "" Then 
                MyCell.Offset(0, 1).Value = MyCell.Offset(0, 1).Value & "," & _ 
                Cell.Offset(0, 1).Value 
            Else: Goto Nxt 
            End If 
        Next 
         
Nxt: 
    Next 
    sheets("Orders").autofiltermode=False 
End Sub

Display More

Customers and orders now sorted!

As for the rest figure it out!, you didnt't supply all that information when you requested a solution for which i spent my valuable free time....only to find you have changed the goal posts!

Deema · Mar 14th 2008

Re: Identify Duplicates Between 2 Columns Of Different Sheets

Thank you Simon I do appreciate the time you have spent to assist me.

Simon Lloyd · Mar 14th 2008

Re: Identify Duplicates Between 2 Columns Of Different Sheets

Look have a play around with the Offsets and the Rng & Rng1 changing the lettering, you will see what is happening when you change them and run the code from there you should be able to work it out....believe me learning and sorting it out for your self is the best way to go as you will always keep that skill!

Identify Duplicates Between 2 Columns Of Different Sheets

Participate now!

Share