+ Reply to Thread
Results 1 to 4 of 4

Find duplicates from a column and copy to another worksheet

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    03-04-2014
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2019
    Posts
    758

    Find duplicates from a column and copy to another worksheet

    Hello,

    On one worksheet I have a number of rows of data of schools, town names, colleges, companies etc in column D.

    I need to create a macro which finds the duplicates in the column and copies them into row A on "Sheet2".

    An added extra would be for this data in "Sheet2" to be sorted by the number of duplicates. I.e. if ASDA features 15 times this would show up at the top.

    Can this be done?

    Many thanks,

    Chris

  2. #2
    Forum Expert
    Join Date
    08-28-2014
    Location
    Texas, USA
    MS-Off Ver
    2016
    Posts
    1,796

    Re: Find duplicates from a column and copy to another worksheet

    Sub chrise()
    
    Set ws1 = Sheets("Sheet1")
    Set ws2 = Sheets("Sheet2")
    Application.ScreenUpdating = False
    For Each c In ws1.Columns(4).SpecialCells(2)
    Set d = ws1.Range(c.Offset(1, 0), ws1.Cells(Rows.Count, 4).End(3)).Find(c.Value)
    If Not d Is Nothing Then
    Set e = ws2.Columns(1).Find(d.Value)
    If Not e Is Nothing Then
    e.Offset(0, 1).Value = e.Offset(0, 1).Value + 1
    Else
    Set nr = ws2.Cells(Rows.Count, 1).End(3).Offset(1, 0)
    nr.Value = d.Value
    nr.Offset(0, 1).Value = 2
    End If
    End If
    Next
    
    ws2.Sort.SortFields.Clear
    ws2.Sort.SortFields.Add Key:=Range("B2:B" & nr.Row), Order:=xlDescending
        With ws2.Sort
            .SetRange Range("A2:" & nr.Offset(0, 1).Address)
            .Apply
        End With
    Application.ScreenUpdating = True
    End Sub
    I'm interested in starting a career working with VBA, if anyone knows of any opportunities!

  3. #3
    Forum Contributor
    Join Date
    03-04-2014
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2019
    Posts
    758

    Re: Find duplicates from a column and copy to another worksheet

    Hello,

    Many thanks for the response, much appreciated.

    I have just put it to the test but it did not work, I got an error in the 4th row of code

    For Each c In ws1.Columns(4).SpecialCells(2)


    Any advice?


    Many thanks

  4. #4
    Forum Expert
    Join Date
    08-28-2014
    Location
    Texas, USA
    MS-Off Ver
    2016
    Posts
    1,796

    Re: Find duplicates from a column and copy to another worksheet

    Is your first sheet actually named "Sheet1"? You need to change ws1 and ws2 to whatever your sheet names actually are. Like:

    Set ws1 = Sheets("ActualNameofMySheet")
    If that doesn't help, please attach a sample workbook that demonstrates the error.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 6
    Last Post: 01-29-2015, 02:54 AM
  2. Replies: 5
    Last Post: 08-01-2014, 04:30 PM
  3. Copy columns, remove duplicates and paste different worksheet and column query
    By Chris* in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-13-2014, 06:52 AM
  4. [SOLVED] Find duplicates in column A, add values from column B (possibly delete duplicates)
    By luarwhite in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-12-2013, 04:34 PM
  5. [SOLVED] Find duplicates, concatenate into different column, sum and delete duplicates
    By rosannang in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-17-2013, 11:23 AM
  6. How to find duplicates in a column using VBA and without using worksheet functions??
    By himanshu_s in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-18-2013, 03:10 AM
  7. Find duplicates in 2 columns and copy/paste to new worksheet
    By davidparkes in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 11-12-2010, 01:38 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1