+ Reply to Thread
Results 1 to 4 of 4

Macro to remove duplicate data in one column and keep highest value in another column

  1. #1
    Registered User
    Join Date
    11-02-2016
    Location
    Sweden
    MS-Off Ver
    2013
    Posts
    21

    Macro to remove duplicate data in one column and keep highest value in another column

    Hi,

    I have received some great help so far but have stumbled upon another code issue I can't seem to get around.

    In short I have between 25-45 workbooks with tons of data that are copied from other instances (reports). I need to filter and sort this data in an effective way in excel and doing things manually and using formulas is not time efficient for this kind of work.

    Currently I have a workbook containing multiple sheets but my focus for now is Sheet1 and Sheet2.

    On Sheet1 I currently run two macros. One to change column B text into information (is created as text from a report) and then I run a Macro (received help in here in a recent post) that checks column B in Sheet2 and removes any row in Sheet1 not containing those numbers on column B. So this would be the third macro.

    In both Sheets column B contains an employee ID.
    In Sheet1 column A I have another number identifying how old the employment is. Sheet1 looks like this:

    Nr Empl. ID
    A B
    14 123456
    133 123456
    154 123456
    1130 123456
    8971 123456
    25 234567
    134 234567
    2764 234567

    I can easily setup a macro to remove rows with duplicates. But I can't seem to be able find any solid information online on how to setup a macro that removes duplicates in column B and keeps the row with the higest value in column A.

    The result of the macro should be like this:

    A B
    8971 123456
    2764 234567

    The duplicates in column B is removed and it keeps the highest value in column A. This is important since there 10 other columns containing necessary information for other macros later on but the other macros wont work if there are duplicates in this sheet.

    Any smooth way to solve this?

    Thanks in advance.

    Regards

    /René

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Macro to remove duplicate data in one column and keep highest value in another column

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Registered User
    Join Date
    11-02-2016
    Location
    Sweden
    MS-Off Ver
    2013
    Posts
    21

    Re: Macro to remove duplicate data in one column and keep highest value in another column

    Hi,

    Let's hope I did it right with the attachment.

    So I have provided a sample workbook showing the information as described in original post and wanted result in sheet "Wanted result".

    As mentioned this is how the workbook would look like after running my first two macros. The second macro has already removed all employment IDs in column B Sheet1 that doesn't exist in column B sheet2.


    So what I need the macro to do is look for duplicates in Sheet1 column B, remove all duplicates but save the one with the highest number in column A. See wanted result in tab "Wanted result"

    /René
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    11-02-2016
    Location
    Sweden
    MS-Off Ver
    2013
    Posts
    21

    Re: Macro to remove duplicate data in one column and keep highest value in another column

    I solved this using two different macros.

    First I recorded a filter macro to first filter column B (Lowest to highest) and then filter column A (highest to lowest). After recording and polishing it looks like this:

    Sub MacroFilter1()
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("B2:B1551" _
    ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A2:A1551" _
    ), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
    .SetRange Range("A1:M1551")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With
    End Sub

    The second macro is a simple Macro saving the first for each unique number and removing all other duplicates.

    Sub RemDuplicates1()
    Dim N As Long, i As Long

    N = Cells(Rows.Count, 1).End(xlUp).Row
    For i = N To 2 Step -1
    If Cells(i, 2) = Cells(i - 1, 2) Then
    Cells(i, 2).EntireRow.Delete
    End If
    Next i
    End Sub

+ 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. Remove duplicate rows and move the column data to a new column
    By cmray58 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-22-2015, 03:46 PM
  2. [SOLVED] How to remove duplicate data but leave the row with highest value from another column
    By mgilmore77 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-22-2014, 08:59 PM
  3. [SOLVED] Remove duplicate entries from one column and output it into another column using formulas
    By alrikvincent in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 07-12-2014, 08:55 AM
  4. Remove rows with duplicate values in one column based on value of another column
    By jolleyje in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-15-2013, 06:20 AM
  5. Replies: 2
    Last Post: 01-31-2013, 07:04 PM
  6. Remove Duplicate (Prioritized on data in a different column)
    By rmikulas in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-08-2010, 08:17 AM
  7. Replies: 1
    Last Post: 05-31-2005, 08:05 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