Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Merge and Remove Duplicates in Excel List

I am attempting to merge and remove vertical duplicates in an excel list. The attached picture explains what I want to do more fully than any explination I can give. Please post questions if you have them so I can clarify. Thanks!


Post your answer or comment

comments powered by Disqus
How Do I Add Commas To Columns And Remove Spaces In Excel??

I need to create lists with the use of 3 columns, i.e., first name, last
name, date
so that it ends up like below when I select all three columns and then copy
and
paste to a text editor like Word Pad ...

first-name,last-name,date
first-name,last-name,date
first-name,last-name,date
first-name,last-name,date

Thank You!

Zack
www.zeel.biz

Please send me the keyboard shortcut key for merge and center cell in excel
2003.

Hi all,

I am an new to the forum and I have ended up here as so to resolve my need in vba aswell learn good things in vba and my requirement goes as follows

I have a folder and the folder has nearly has 350 text files all the text files are of similar patters

Description of text file :

Text file has at least 11 fields and at the max of 18 fields with a delimiter " " (space)

Now I wanted to load only 10th field from the text file to the excel

Since each and every file has duplicates and also considereing the limitations of excel ( I guess it cant store more than 65000+ records) after importing that field I wanted to remove duplicates and then append with 10th field of next file and remove duplicates for the whole list and on and on till 350 text files

My 10th field can be maximum of 8 bytes but it can be less than that ( but Its sure 10th field will be present in the file)

Please note : total size of the folder is around 450 MB ( Total size of all text file)

If anyone has come across this scenario and the slution used will be helpful for me )

Please let me know if anymore information is needed for the same.

Thanks in advance

I have multiple data bases in Excel I want to use for labels in mail merge
but I want to combine the data bases and remove duplicates. the compare &
merge workbooks under "tools" is greyed out.

Hi There,

I'm creating a dyamic sub routine that when placed in a Userform code module and called upon will loop through all controls within the form and remove duplicates from the listboxes or comboboxes.

i've got 95% of the way but now i just can't seem to delete the item?

here's what i have so far:

Public Sub
RemDupsBoxes()
Dim Ctrl As Control

For Each Ctrl In Me.Controls
    If TypeOf Ctrl Is MSForms.ListBox Or TypeOf Ctrl Is MSForms.ComboBox Then
                
        vaItems = Me.Controls(Ctrl.Name).List
        
        For O = LBound(vaItems, 1) To UBound(vaItems, 1) - 1
            For c = LBound(vaItems, 1) To UBound(vaItems, 1) - 1
                Select Case c
                    Case O
                        'Do Nothing
                    Case Else
                        If vaItems(c, 0) = vaItems(O, 0) Then
                            Me.Controls(Ctrl.Name).RemoveItem '???????????
                        End If
                End Select
            Next c
        Next O
        
    End If
Next Ctrl

End Sub
any help is greatly appreciated folks and thanks for taking the time to read this

also for some help i craeted one that loops through LB' & CB and orders them

FYI i used some code from John Walkenbach in this so it's not all my own work:

Public Sub OrderBoxes()
Dim Ctrl As Control

For Each Ctrl In Me.Controls
'If control is a Listbox or Combobox then Order it
    If TypeOf Ctrl Is MSForms.ListBox Or TypeOf Ctrl Is MSForms.ComboBox Then
  
'Steal code from John Walkenbach’s Excel Power Programming      
        vaItems = Me.Controls(Ctrl.Name).List
        
        For i = LBound(vaItems, 1) To UBound(vaItems, 1) - 1
            For j = i + 1 To UBound(vaItems, 1)
                If vaItems(i, 0) > vaItems(j, 0) Then
                    vTemp = vaItems(i, 0)
                    vaItems(i, 0) = vaItems(j, 0)
                    vaItems(j, 0) = vTemp
                End If
            Next j
        Next i

        'Clear the box
        Me.Controls(Ctrl.Name).Clear

        'Add the sorted array back to the box
        For i = LBound(vaItems, 1) To UBound(vaItems, 1)
            Me.Controls(Ctrl.Name).AddItem vaItems(i, 0)
        Next i
    End If
Next Ctrl
End Sub
Cheers

I have combined several mail list into one on excel workshett and have quite
a few dupicates. How can I find and delete duplicates?

I have copied several tables from Word 2003 into Excel 2003 and I am no
longer able to merge and center within the document. What disabled that
option and how do I re-enable it?

Thanks in advance,
Barb Reinhardt

Hi,
I working with Excel 2007 and would like to remove duplicates in column D, but keep the first instant.
It has to look at column A for duplicates then look at column D and remove all the duplicates, but the first one.
I need to keep the data in the other columns (A, B, C, E ...)

Original Data
Column A Column D
CH010 DUE FROM MANAGEMENT
CH010 DUE FROM MANAGEMENT
CH010 DUE FROM MANAGEMENT
CH010 SAMMY MANAGEMENT
CH010 SAMMY MANAGEMENT
CH010 AIDAN MANAGEMENT
CH010 AIDAN MANAGEMENT
CM03 SECURE MESSAGING
CM05 SECURE MESSAGING
CM05 SAFE MESSAGING
NIM010 WEAS MODEL
XAP010 OPEN FINANCIAL
XAP010 OPEN FINANCIAL
XAP010 CLOSE FINANCIAL
XAP010 CLOSE FINANCIAL

Should look like:

Column A Column D
CH010 DUE FROM MANAGEMENT
CH010
CH010
CH010 SAMMY MANAGEMENT
CH010
CH010 AIDAN MANAGEMENT
CH010
CM03 SECURE MESSAGING
CM05 SECURE MESSAGING
CM05 SAFE MESSAGING
NIM010 WEAS MODEL
XAP010 OPEN FINANCIAL
XAP010
XAP010 CLOSE FINANCIAL
XAP010

Thanks

I have a .csv file that contains a list of zip codes and associated
sales tax rates for all the various counties within the State of Ohio.
There are multiple tax rates for some of the zip codes within this
file.

The file contains 3 columns as follows:
Column A = Zip Code
Column B = County Name
Column C = Tax Rate

I need to know if it is possible to remove duplicates in Column A while
keeping the highest value in column C.

Basically, my list may contain 3 or 4 different County Names and Tax
rates for 1 specific Zip Code. I need to filter my list so that there
are no duplicate zip codes but keep the highest tax rate for each zip
code.

Example:

Zip County Rate
43110 Franklin 6.75
43110 Fairfield 6.25
43110 Licking 6.50

In this example above, the final filtered result should return the
following and delete the 2 others that had the lower tax rates:
Zip County Rate
43110 Franklin 6.75

The reason for this is for an online store that I need to upload a csv
file for sales tax rates. If I upload the csv file as it is, my online
store will charge the first rate it comes to for a specific zip code.
When it comes to collecting the sales tax, I want to be sure I collect
the highest rate per zip code thus creating the need to filter out any
duplicates containing the lower tax rates.

Any help is greatly appreciated.

Thanks In Advance
Mike

I'm trying to use Remove Duplicates in Excel 2007 and I want it to remove duplicates for all columns, no matter how many there might be. I think I'm close, but with this code I get errors:


	VB:
	
 
.. 
lastColumn = Cells(1, Columns.Count).End(xlToLeft).Column 
.. 
Redim colArray(1 To lastColumn) 
 
 
 ' Fill array will all columns
For i = 1 To lastColumn 
    colArray(i) = i 
Next i 
 
 
 ' Convert column number to letter
lastColumn = Chr(lastColumn + 64) 
ActiveSheet.Range("A1:" & lastColumn & lastRow).RemoveDuplicates Columns:=colArray, Header:=xlYes 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I've also tried these arguments with subscript errors:
Columns:=colArray() = Runtime error 9 subscript out of range
Columns:=(colArray) = Runtime error 5 invalid procedure call or arguemnt

Is there a special way to use arrays for this? Is there a plain old better way to remove duplicates?

Thanks

I have 2 data sets, based on the same original source, each with different criteria.
The same fields have different names in the 2 differents data sets and are in a different order.

Can anyone suggest how to combine the 2 data sets and remove duplicates? I can identify/map equivalent fields.

I do not have access to the original data source. I'm afraid I cannot provide a sample because it is sensitive information.

I have a database of 6000+ addresses that includes many duplicates. I need
to delete the duplicates. The problem is the addresses weren't entered with
common formatting (Road vs. Rd, Lane vs. Ln, (555) 555-5555 vs. 555-555-5555)
which throws off excel's function for scanning and removing duplicates. The
silver lining is one column does contain a standard identifying digit that
would be the same no matter how the address, phone number, zip code or
contact name was entered. How do I remove the duplicate rows using just that
one column of data as the criteria?

Users will be entering a list of data across a row. They will be selecting
the datum from a "validated" drop-down.
We need to ensure that they don't select any duplicate entries from the
drop-down when completing their list.
I researched a 'Conditional Formatting' solution that will change the
formatting. I tried this, but when I tried to copy the Formatting on
pre-existing lists, I could not get it to not copy the Value as well(?).
Any suggestions for checking and flagging duplicates in a list?
Thanks in advance.

Hello,

Question: How to avoid duplication in excel workbook.Suppose if the name of
the company is (ABC Inc.)entered in one sheet, and while entering the same
company name(i.e. ABC Inc.) in other sheet, the excel should show a message
that the company name already exist in so and so sheet name.

Pls provide me the detailed answer along with the required tools to solve
the above problem.

Thanking You

Hi Ozgrid,
I would reckon there is a similar addin out there, and I think this is actually a built in function 2007+, but here is my own home grown addin for removing duplicate entries.

It really shouldn't need much tinkering, but it's got decent notes if you want to. It's meant to be a fully encapsulated solution, callable from either the GUI or VBA. The project is unlocked, and I hope someone out there can find some use for it. I use it every day! If you do manage to break it, or have a suggestion, let me know and I'll be happy to update it.

Features:
-The key combo Ctl-e performs a sort on the first column of the selected range.
-The first row of any duplicate entries in the first column is preserved, the remaining are discarded. Data outside the selction is not affected.
-Undo is fully supported.
-If called from GUI, has failsafe measures attempt to shrink large selections, or warn the user that the operation may take some time.
-If called from VBA it won't untoggle any application settings (events, calculation, selection, etc)

The code: (Attached as an .xla, but posting the code in case you are wary about opening strange addons )

	VB:
	
 
 '   Stores info about current selection
Type SaveRange 
    Val As Variant 
    Addr As String 
End Type 
 
Public OldWorkbook As Workbook 
Public OldSheet As Worksheet 
Public OldSelection() As SaveRange 
Option Explicit 
Public Sub DupeSort() 
     'Using a helper sub to make it visible to the macro dialog in the GUI
     'Otherwise with even an optional parameter it won't show up there.
    SortandRemoveDupes 
End Sub 
 
Public Function SortandRemoveDupes(Optional RangeToSort As Range) As Long 
     '---------------------------------------------------
     '      v2.0
     '
     'This function is meant to be called with the keyboard shortcut "CTL-E"
     'It sorts and removes duplicate rows based on the first column of the selection
     'The first of multiple entries is preserved
     '
     'To call from VBA, just pass the optional range reference
     '**Return Value = number of lines removed, -1 for error
     '
     '
     'Known issues
     '-No ability to change sort direction or key another column, must be changed programatically
     '-Formatting is not restored on an undo (if it's sorted)
     '
     '
     'Author: Demgar(Email removed from my forum post, go away bots)
     'This code is open source, feel free to edit or use however you like
     '*But please credit the author
     '
     '4/26/10 -Tre-  Added undo functionality
     '               Inverted the clearing loop - the row with the first result is kept, since it checks from the bottom
     '               Changed the way the first/last row are initialized - should speed execution up considerably for large
ranges
     '               Turned on/off application parameters for speed. Calc set back to user's state. The reversal is behind
the error checking
     '8/19/10 -Tre-  Reviewed the clearing loop and changed slightly
     '               added a check to count the selection and dump if < 2
     '               Added a check for multiple
     '               cleaned up some references using with statements
     '8/20/10 -Tre-  Moved to a seperate workbook to publish
     '               Added an optional parameter to enable it to be called from vba
     '               also return value
     '------------------------------------------------------
    Dim FirstCol As Integer, FirstRow As Integer, lastCol As Integer, lastRow As Long 
    Dim i As Long 
    Dim MyRange As Range 
    Dim calcstate As Integer, screenUpdateState As Integer, eventsState As Integer 
    SortandRemoveDupes = -1 
     'Checking to see if we can run it
    If RangeToSort Is Nothing Then 
        If TypeName(Selection)  "Range" Then Exit Function 
        If Selection.Count > 130000 Then 'Arbitrary, but would let us sort 2 entire columns pre 2003 before it's triggered
            Set MyRange = Intersect(Selection.Parent.UsedRange, Selection) 
            If MyRange.Count > 200000 Then 'Again arbitrary
                If MsgBox("Warning: This operation may take some time, would you really like to proceed?", vbExclamation,
"Warning", vbYesNo) _ 
                = vbNo Then Exit Function 
            End If 
        Else 
            Set MyRange = Selection 
        End If 
    Else 
        Set MyRange = RangeToSort 
    End If 
     'Turn things off for speed
    With Application 
        calcstate = .Calculation 
        screenUpdateState = .ScreenUpdating 
        eventsState = .EnableEvents 
         
        .Calculation = xlCalculationManual 
        .ScreenUpdating = False 
        .EnableEvents = False 
    End With 
     
    With MyRange 
        If .Count < 2 Then Goto Endy 
        If .Areas.Count  1 Then Goto Endy 
        SetUndoPublics MyRange 'define the publics used to undo
         
        FirstRow = .Row 
        lastRow = .Item(.Count).Row 
        FirstCol = .Column 
        lastCol = .Item(.Count).Column 
         
        .Sort Key1:=Cells(FirstRow, FirstCol), Order1:=xlAscending, Header:=xlNo, _ 
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ 
        DataOption1:=xlSortNormal 
         
        SortandRemoveDupes = 0 'No longer in "error state" if we get this far
         
        For i = lastRow To FirstRow + 1 Step -1 'the first row of the selection shouldn't be compared outside the selection.
             'We want to start from the bottom, so we will decrement the loop.
             'Which means a for/each won't easily work.
             
            If Cells(i, FirstCol) = Cells(i - 1, FirstCol) Then 
                Range(Cells(i, FirstCol), Cells(i, lastCol)).ClearContents 'Could be .clear if you want to kill formatting
too
                SortandRemoveDupes = SortandRemoveDupes + 1 'Return success count
            End If 
        Next i 
         
        .Sort Key1:=Cells(FirstRow, FirstCol), Order1:=xlAscending, Header:=xlNo, _ 
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ 
        DataOption1:=xlSortNormal 
    End With 
     
     'Specify the Undo Sub - this must go above the endy, in case we trigger the dump
    Application.OnUndo "Undo the Remove Dupes macro", "UndoRemoveDupes" 
Endy: 
    With Application 
        .Calculation = calcstate 'reset to the user's state
        .ScreenUpdating = screenUpdateState 
        .EnableEvents = eventsState 
    End With 
End Function 
Private Sub SetUndoPublics(MyRange As Range) 
     '8/10 - Not sure why I first set this up to loop the range instead of just saving the range object in memory
     ' It works though, and CBA to fiddle with it
    Redim OldSelection(MyRange.Count) 
    Dim Cell As Range 
    Dim x As Long 
    Set OldSheet = MyRange.Parent 
    Set OldWorkbook = OldSheet.Parent 
    For Each Cell In MyRange 
        x = x + 1 
        OldSelection(x).Addr = Cell.Address 
        OldSelection(x).Val = Cell.Formula 
    Next Cell 
End Sub 
Private Sub UndoRemoveDupes() 
     'Undoes the effect of the remove dupes sub
    Dim x As Long 
    Dim calcstate As Integer, screenUpdateState As Integer, eventsState As Integer 
    With Application 
        calcstate = .Calculation 
        screenUpdateState = .ScreenUpdating 
        eventsState = .EnableEvents 
         
        .Calculation = xlCalculationManual 
        .ScreenUpdating = False 
        .EnableEvents = False 
    End With 
On Error Goto ErrorHandler: 
     '   Make sure the correct workbook and sheet are active
     ' This could be done without selecting, but we assume the undo
     ' is called from the GUI, so lets show the user what's going on
    OldWorkbook.Activate 
    OldSheet.Activate 
     'reselect their range
    Range(OldSelection(1).Addr, OldSelection(UBound(OldSelection)).Addr).Select 
     '   Restore the saved information
    For x = 1 To UBound(OldSelection) 
         'Won't restore any formatting that got sorted
        Range(OldSelection(x).Addr).Formula = OldSelection(x).Val 
    Next x 
Endy: 
    With Application 
        .Calculation = calcstate 'reset to the user's state
        .ScreenUpdating = screenUpdateState 
        .EnableEvents = eventsState 
    End With 
    Exit Sub 
     '   Error handler
ErrorHandler: 
    MsgBox "Can't undo!" & vbCr & Err.Description 
Resume Endy: 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I have a huge log file of people logging onto our web site. When I get it into excell all the information is on one line. I would like to get the IP address (99.999.999.99) out of the line and put it into a seperate cell. Then sort by IP address and removes duplicate lines.

2007-11-29 00:00:00 99.999.999.99 - 111.111.1.11 80 GET /images/WTPLOD.jpg - 200 Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.1;+SV1;+.NET+CLR+1.1.4322;+.NET+CLR+2.0.50727;+.NET+CLR+3.0. 04506.30)

Thanks
Dark Jedi

I have two columns of data that are different lengths. I want to take the data in column A and find duplicates in Column B and then have whatever is not a duplicate in Column B show up as a list in Column C.

I have copied the two columns of data into one sheet. Would there also be a way to do this across two sheets?

Thanks for the help.

Hello everyone,

I've been trying to sum merge, and delete duplicate rows in this massive spreadsheet (over 15,000 rows). Currently I've gotten to the point where the duplicate lines are eliminated but the quantity is recording the number of times the line has moved, not summing. Below is an example of what I'm looking for

EX:

Name|Location|Address|car|Days|
Bob CT 1234 TY 56
Adam MA 456 FD 100
Bob CT 1234 TY 56

to

Name|Location|Address|car|Days|
Bob CT 1234 TY 112
Adam MA 456 FD 100

My current code is an combination of what I've found:

Sub
abc()
Dim x, y, a, n, m, i, j, b()
x = 1: y = 5
a = ActiveSheet.UsedRange
n = UBound(a, 1): m = UBound(a, 2)
ReDim b(1 To n - 1, 1 To m)
With CreateObject("Scripting.Dictionary")
.comparemode = 1
For i = 2 To n
    If Not .exists(a(i, x)) Then
        .Add (a(i, x)), a(i, y)
        For j = 1 To m: b(.Count, j) = a(i, j): Next j
    Else
        b(.Item(a(i, x)), y) = b(.Item(a(i, x)), y) + a(i, y)
    End If
Next i
Range(Cells(2, 1), Cells(n, m)).ClearContents
Cells(2, 1).Resize(.Count, m) = b
End With
End Sub


This is driving me nuts but for some reason when select column A and B, click remove duplicates in excel 2007, deselect column B ( because i want the duplicates removed from column A ), it runs but leaves some duplicates.

Anyone know why? here is the file, u can run the remove duplicates yourself to see what i mean, Row 8 and 9 are exactly THE SAME in COLUMN A And it leaves it. same with other cells.

ANyone that could help would be great, THanks

What is the easiest way to find duplicate values and/or text in Excel?

find duplicates in excel 2007 from two different spreadsheets. I have to modify a report weekly and I am looking for a way to search for duplicates without having to use CTL+F. Data is added to the master and I am saving it on my desktop and cutting and pasting.

i would like to use radio buttons,check boxes and command buttons in excell...
can anyone help me for atleast the first two wtht using macros or usng Vis Basics.is it like using using list in Data validation in tools?

Users will be entering a list of data across a row. They will be selecting
the datum from a "validated" drop-down.
We need to ensure that they don't select any duplicate entries from the
drop-down when completing their list.
I researched a 'Conditional Formatting' solution that will change the
formatting. I tried this, but when I tried to copy the Formatting on
pre-existing lists, I could not get it to not copy the Value as well(?).
Any suggestions for checking and flagging duplicates in a list?
Thanks in advance.

I did an advanced search on the forums, in this topic.. is there anyway on a mac, to remove duplicates?


No luck finding an answer? You could always try Google.