Free Microsoft Excel 2013 Quick Reference

Filter multiple columns/rows for duplicate records

I have a spreadsheet that is basically copied/pasted data from a query that is run periodically; sometimes a duplicate row of data might get copied/pasted into the "static" list
Is there a macro or other function in Excel that I could run to filter these out.
Here's an example:
******** ******************** ************************************************************************>Microsoft Excel - Book3___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutM2M3=
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box

Rows 4 & 5 are exactly the same as 6 & 7 respectively.
I would like a way of filtering or deleting these duplicate rows automatically, as the static list is quite extensive to do manually.

And this is an ongoing spreadsheet that any user might repeat this duplicate copying/pasting.

Post your answer or comment

comments powered by Disqus
How to do a 3-Dimensional Addition if there are Multiple Columns & Rows as Criterions

Dear Forum,

I am trying to add several Columns of Data where there are Valid Numeric Entries and the Addition has to be done for a particular lookup value ROW-WISE and a Generic condition COLUMN-WISE.

I will try to explain this in concept before I make up a Dummy SHeet for this query, in-case if someone can still help...

I have to add the Amounts from 4 different Modes of Payments which are mentioned 4 Different Columns and these are spread over different rows as the payment is recorded Date-wise as well as Mode-Wise ..

And now all these columns are not not adjoining there are some other columns in between them too...

Now, I want to perform addition in such a way that the Addition if done from only those rows where the Lookup Value criteria is satisifed as well as from those columns where the Column Heading is " Amount"..

I have 4 different column headings such for these payment columns like
1 Cash Amount - Col H
2 Cheque Amount - Col L
3 RTGS/NEFT Amount - Col S
4 Credit Card Amount -Col U

And I have Lookup Criterion such as based on the Unique Plot No.. which is only in the COlumn B..

My thinking is to get the entire Range for addition i.e from COlum G till Col U and have such a condition where only the arrays of those columns which are having the last word as " Amount" to be added and which also fulfill for the particular Unique Plot NO..

I don not know whether this is possible or not..?
If yes then please can someone help me..


There are just 2 Rows which are fulfilling the conditions and the rows are ROw 2 and Row 5 and then I want all the nOs from the below
1 Cash Amount - Col H
2 Cheque Amount - Col L
3 RTGS/NEFT Amount - Col S
4 Credit Card Amount -Col U

to be added so I dont want to add 4 different col ranges but want it to be done automatically though the entire range is given from G:U

Is this possible?

Warm REgards


I would like to use Excel formulas to check reliably for the existence of duplicate records (not just duplicate cells with COUNTIF). What a couple of sites recommended was concatenation of the records with '&' into single cells, so that COUNTIF can again be used. However, this does not quite do a foolproof job of counting duplicates, as someone could enter:

| A | B |
1 | x | y |
2 | | xy |

In this example, A1 & B1 ("xy") will return the same value as A2 & B2 ("xy"), even though rows 1 and 2 are not duplicate records.

I thought about using a special character such as a pipeline '|' and using input validation to block pipeline entry (though I'm not sure how to do this yet). Alternatively, I could insert a 'password' between each concatenated element that the user will not be likely to enter. However, I am looking for a foolproof way to check for duplicate records that does not involve password values or blocking the entry of any enterable character.

Any ideas?

Does anyone know a formula (not VBA) to return all non-blank values from multiple columns/rows into a single column?

1 xx yy zz
2 ss tt
3 uu

would return:


Does anyone know a VBA Macro to return all non-blank values from multiple columns/rows into three columns in the format below in a different sheet?
Example in Sheet1 (2 "header" columns + up to 50 columns of numerical and text data - starting at A2):
Prog 1 12/3/2010 123 234 456
Prog 2 15/3/2010 xyz
Prog 3 16/3/2010 321 abc
would return 3 columns to Sheeet2 (starting at A2):
Prog 1 12/3/2010 123
Prog 1 12/3/2010 234
Prog 1 12/3/2010 456
Prog 2 15/3/2010 xyz
Prog 3 16/3/2010 321
Prog 3 16/3/2010 abc
There are up to 50 columns of the numbers/text (column 3 onwards) and the blanks are always at the end of the rows. The number of rows varies.

Hi guys, how can i search for duplicate records in 2 excel spread sheet

What can i do to eliminate the duplicates? or is there any functions which can help me to identify the duplicates records?


Sheet 1 Sheet 2
Material Material
SC10609 SC10609
SC08002 SC10623
SC08001 SC03006
SC08003 SC01016
SC08011 SC10610
SC09001 SC02021
SC08004 SC11290
SC08005 SC10334
SC10623 SC11379
SC03008 SC10516
SC03007 SC07011
SC10417 SC11287
SC10450 SC11274
SC10433 SC11385
SC10452 SC11131
SC10762 SC02026
SC10543 SC02030
SC10538 SC11295
SC03006 SC10512
SC10359 SC01002
SC10469 SC11377
SC10141 SC08026
SC10142 SC11071
SC01016 SC10626
SC10241 SC03009
SC10610 SC03011
SC10355 SC10419
SC10586 SC10475
SC02021 SC10477
SC10358 SC10370
SC10244 SC11301
SC11282 SC10184
SC10269 SC10841
SC11077 SC11283
SC10597 SC10263
SC10596 SC05000
SC10340 SC10188
SC10317 SC11011
SC10600 SC10755
SC10279 SC11009
SC02007 SC10909
SC10601 SC11347
SC10115 SC10619
SN10004 SC11349
SN10339 SC11350
SN10406 SC11351
SN10408 SC11352
SN10409 SC11353
SN10411 SN10002
SN10415 SN10005
SN10534 SN10893
SN10783 SN11200
SN10839 SN11622
SN10845 SN11635
SN10849 SN11669
SN10850 SN11684
SN11180 SN11804
SN11196 SN11850
SN11200 SN11853
SN11361 SN11857
SN11612 SN11858
SN11665 SN11859
SN11688 SN11863
SN11797 SN11864
SN11801 SN11878
SN11853 SN11881
SN11857 SN11918
SN11858 SN11919

duplicate help.xls


I have seen a few other threads with similar issues as to mine but I have been unable to apply them correctly due to slight differences.

I have created and attached a simplified spreadsheet to show my issue as it is now.

I have multiple columns and rows of data. The first column has part name/numbers and each other column has numerical values associated with the part number, (each column is for a different group, for clarity my example has only one of these columns as I can replicate any solution to the first column).

The aim is to have a front sheet displaying only the values (and corresponding part) above a certain amount e.g. 90%.

I first tried an IF but the problem was I would end up with 100 lines with the values that fit the requirements but empty spaces where they didn’t meet the requirements.

I then tried using LARGE which worked, (I added an IF in and limited the amount of rows so although I will still had blanks I didn’t have a face full of them).
This works fine, LARGE is capable of handling duplicates however I need the associated part to be displayed and this is where the sheet falls over as it gets stuck on the duplicates and doesn’t skip onto the next part name.

This is the thread I have been trying to use
It only seems to work against a single duplicate and also where the duplicate is fixed… My spreadsheet will be updated on a regular basis, all values and parts will change so I need to formula to be able to work without having to be edited. I have considered filters and macros but this sheet really needs to be useable by anyone and everyone without them having to fiddle with it so a set formula is the best option really.

If anyone can help I would be most grateful as this is getting quite frustratin!!

I have a folder which has 200 files. I have extracted data from these files based on autofilter criteria. But there are many duplicate records extracted for the criteria. I need only unique records . Below are the codes. Where to I add the criteria for search records:

     'Note: This example use the function LastRow
    Dim basebook As Workbook 
    Dim mybook As Workbook 
    Dim rng As Range 
    Dim rnum As Long 
    Dim mnum As Range 
    Dim FNames As String 
    Dim MyPath As String 
    Dim SaveDriveDir As String 
    SaveDriveDir = CurDir 
    MyPath = "C:Data" '

I have exported data to a spreadsheet that has multiple names and phone numbers in each row. Can someone help me with a function that will allow me to:
1. Check one of the phone numbers (Cell B2) against all the others in the row for duplicates (Cells E2,F2,G2,I2,J2).
2. Insert "Dup" or "No Dup" in a new cell.

This will allow me to filter out the "Dups".

This link will provide access to the screenshot, which describes the above:

Thank you for your assistance!!!

Hello, I’m having some trouble with my code checking if the value already exists and if so displaying a message. The value to be checked is on one sheet in cell A4, it checks a different sheets database column A for the record and if it exists displays a message, if not then writes the data to the next empty row. Here is the code I have been using without and luck. Can someone tell me what I’m doing wrong please?

[SIZE=3][FONT=Calibri]     If MsgBox("This will save the data and clear the sheet, do you want to continue?", vbOKCancel) =
vbOK Then[/FONT][/SIZE] 
[SIZE=3][FONT=Calibri]     Dim iRow As Long[/FONT][/SIZE] 
[SIZE=3][FONT=Calibri]     Dim ws As Worksheet[/FONT][/SIZE] 
[SIZE=3][FONT=Calibri]     Dim ws2 As Worksheet[/FONT][/SIZE] 
[SIZE=3][FONT=Calibri]     Dim rng As Range[/FONT][/SIZE] 
[SIZE=3][FONT=Calibri]     Set ws = Worksheets("TBDB")[/FONT][/SIZE] 
[SIZE=3][FONT=Calibri]     Set ws2 = Worksheets("TUBDB")[/FONT][/SIZE] 
[SIZE=3][FONT=Calibri] 'check if data already there[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]     On Error Resume Next[/FONT][/SIZE] 
[SIZE=3][FONT=Calibri] 'find date[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]     Set rng = ws.Columns(1).Find(ws2.Range("A4").Value, LookIn:=xlWhole)[/FONT][/SIZE] 
[SIZE=3][FONT=Calibri]     On Error Goto 0[/FONT][/SIZE] 
[SIZE=3][FONT=Calibri]     If Not rng Is Nothing Then[/FONT][/SIZE] 
[SIZE=3][FONT=Calibri]         iRow = rng.Row[/FONT][/SIZE] 
[SIZE=3][FONT=Calibri]         If ws2.Range("a4").Value = ws.Cells(iRow, "A").Value Then[/FONT][/SIZE] 
[SIZE=3][FONT=Calibri]             MsgBox "Duplicate"[/FONT][/SIZE] 
[SIZE=3][FONT=Calibri]             Exit Sub[/FONT][/SIZE] 
[SIZE=3][FONT=Calibri]         End If[/FONT][/SIZE] 
[SIZE=3][FONT=Calibri]     End If[/FONT][/SIZE] 
[SIZE=3][FONT=Calibri]            ActiveWorkbook.Sheets("TBDB").Unprotect "password"[/FONT][/SIZE] 
[SIZE=3][FONT=Calibri] 'find first empty row in database[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]     iRow = ws.Cells(Rows.Count, 1).End(xlUp).Row[/FONT][/SIZE] 
[SIZE=3][FONT=Calibri] 'copy the data to the database[/FONT][/SIZE]
[FONT=Calibri][SIZE=3]ws2.Range("A2").Resize(, 29).Copy[/SIZE][/FONT] 
[FONT=Calibri][SIZE=3]ws.Cells(iRow + 1, 1).PasteSpecial (xlPasteValues)[/SIZE][/FONT] 
[SIZE=3][FONT=Calibri]ActiveWorkbook.Sheets("TUBS").Unprotect "password"[/FONT][/SIZE] 
[SIZE=3][FONT=Calibri] 'clear cells[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]     Range("InputTub").Value = ""[/FONT][/SIZE] 
[SIZE=3][FONT=Calibri]     ActiveWorkbook.Sheets("TUBS").Protect "password"[/FONT][/SIZE] 
[SIZE=3][FONT=Calibri]     ActiveWorkbook.Sheets("TBDB").Protect "password"[/FONT][/SIZE] 
[SIZE=3][FONT=Calibri] End If[/FONT][/SIZE] 
[FONT=Calibri][SIZE=3]End Sub[/SIZE][/FONT] 

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

Hi all,

I have an excel spreadsheet that should have one record for each artifact in a museum collection. The problem is that the museum has consolidated this information from several different sources into one spreadsheet and now there are many duplicate records. They want all the duplicate records removed so that there is just one record for each artifact, BUT there may be different pieces of information in each of the duplicate records. So I want to do the following:

- sort records based on Accession Number (column A)
- find duplicate Accession Number records
- determine which fields (columns) within a duplicate record are unique and concatenate those entries into one master record for each Accession Number
- delete the duplicate Accession Number records

In the attached sample sheet, for Accession Number 66-1-100, we have 6 duplicate records. In the columns, we have information which in some of the records is duplicated, in some it is unique and in some it is missing completely. The museum wants just one master record for each Accession Number and they want all the data from the duplicate records concatenated into one and all the duplicates and blanks discarded.

What I've done so far:
- used the Subtotal feature in Excel to insert a line below each set of Accession Numbers and Count the number of records with matching Accession Numbers.
- from another source, I found the following bit of VBA code which removes duplicate entries from each column within the duplicate records. For the range, I use the OFFSET function to select the cells within the group of duplicate records. In this case B47 is where the Count results of the Subtotal feature go and C47 is my current location.

    Dim rngSrc As Range 
    Dim NumRows As Integer 
    Dim ThisRow As Integer 
    Dim ThatRow As Integer 
    Dim ThisCol As Integer 
    Dim J As Integer, K As Integer 
    Application.ScreenUpdating = False 
    Set rngSrc = ActiveSheet.Range("OFFSET(C47,-$B47,0,1,1):OFFSET(C47,-1,0,1,1)") 
    NumRows = rngSrc.Rows.Count 
    ThisRow = rngSrc.Row 
    ThatRow = ThisRow + NumRows - 1 
    ThisCol = rngSrc.Column 
     'Start wiping out duplicates
    For J = ThisRow To (ThatRow - 1) 
        If Cells(J, ThisCol) > "" Then 
            For K = (J + 1) To ThatRow 
                If Cells(J, ThisCol) = Cells(K, ThisCol) Then 
                    Cells(K, ThisCol) = "" 
                End If 
            Next K 
        End If 
    Next J 
    Application.ScreenUpdating = True 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
- and also from another source, I have a user-defined function that concatenates & adds a delimiter between each data element in each column of the duplicate records.

    Dim r As Range 
    For Each r In myRange 
        If Len(r.Text) > 0 Then 
            Concat = Concat & r & myDelimiter 
        End If 
    Next r 
    If Len(myDelimiter) > 0 Then 
        Concat = Left(Concat, Len(Concat) - Len(myDelimiter)) 
    End If 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
So here are my questions... I'd really like to have this set up so that it runs automatically through each column for each set of duplicate records, but to do that I think I need to:
- tell my DelDups macro code to loop through the column range for each record
- include my CONCAT formula in the VBA macro
- and once all is done, I want the duplicate records to be deleted, leaving only one record for each Accession Number that includes all the data that was in each of the duplicate records.

I don't really know how to do any of the three things above and I have the feeling there's a much easier way to do all this! However, I am a total VBA nocie and I'm a bit stumped! Thanks in advance for any help.

The sample I am attaching has the first 50 or so records. The complete excel file has about 27,000 rows and 69 columns. The museum believes there should be around 4,000 records once all the duplicates are removed.

I am looking for a macro that will transpose a long single column of data (each record is seperated by a blank cell in the column) into seperate rows (each row representing a speperate record) This data set only has nine fields per record, no formulas. I dont want to do this by hand.


I have a txt file that has multiple rows of data that i want to condense into one row with multiple columns in a XLS file. For Example

TXT file:
field1: record1-field1number
field2: record1-field2text
field3: record1-field3text
field 4 text
takes up 2 lines

field1: record2-field1number
field2: record2-field2text
field3: record2-field3text
field 4 text
takes up 2 lines

DESIRED XLS FILE ( | denotes next cell)

record1-field1number | record1-field2text | record1-field3text
record2-field1number | record2-field2text | record2-field3text

How can I check for a duplicate record from a userform? I have tried several
codes from the site, but couldn't find any that addressed multiple fields.

Example: A spreadsheet made to work/look like a form is called a transmittal
letter(DA_Form_200). More or less a cover sheet and a means to track what was
sent and when. A userform allows the user to input a short description of
each record (an Officer Evaluation Report) i.e. last, ssn, rank, startdate,
enddate. The macro in the userform places each short description onto the
spreadsheet (Page 2 of the 'DA_Form_200')in it's own row under the headers
referenced above, which also sorts by last and startdate. (makes it easier
for me and others to see who was mailed and when). At the same time the code
sends the same 'short decr records' to another spreadsheet (Date_Sent) which
just collects all the 'short descr records' so that I only have to look one
place if necessary.

When entering a 'short descr record' (from userform), how do I check for a
duplicate record (last, startdate and enddate fields) in both the 'Page 2' of
the worksheets labeled DA_Form_200 and the worksheet labeled Date_Sent and
have a message box informing me of the duplicate record ask me if I want to
proceed or not?

I hope I did not confuse anyone.

Hi Rylo,

Hi Rylo,
I hv gone thru' the posts. I hv a very similar kind of reqmt as of FNJTravel. (Refer thread My reqmt is given below :

(a) Have data from A to S. Some of the data are unique & some are duplicate.
(b) I will check for a unique value in column D (assume it is employee ID).
(c) If I found a duplication in Column D value, I will retain the 1st row. And add the value of Column C, E, F (add the values in both the rows in the columns C,E,F). For columns, G, H, I, I will append the texts by inserting a "/". For example, for employee ID 121, i find 2 records. I will retain the first record as it is and from the 2nd record, I will add the value of Record1 + Record2 for column C, E, F and append the value of Record1 & Record2 as Programme Manager / Delivery Manager. Here the Programme Manager is the value in REcord 1 & Delivery Manager is from Record 2.
(d) Finally remove the duplicate record.

For FNJTravel, you have appended the data from the duplicate record, at the end of the first record. For me, I need to add the values in appropriate column & delete the duplicate record. thats it.

A sample file is attached in the given thread (refer above). The expected result sheet has marking on my exact requirement.

Hope you could help me out in achieving this.


Hi Everyone,
I would like to seek help filtering some of my Data using Macro.
Typically on my Excel Sheet, I have Data on Columns A, B and C.
Could you help me filter and delete the row if there's a duplicates on Column C?

Doing some reporting and I could really save so much time if there's a way to automate this using a Macro script. Kindly help. Thank you in advance.

(I have attached my dummy work sheet).


Hello, thought I would post to see if anyone can help. I have a rather large set of data but the format is far from the way a easily understandable format. current the labels are listed in Rows in column A and the data is in B. so the transpose option didn't work.

The number of data for label3 and 4 can change but each record has a Label1 and Label2. LAbel 2 is normally an email but not all the data is clean.

In Excel the data Excel the data is arranged like this:
Label2,email address
Label2,email address

looking for something like this so label3 one cell per record and label4 same something in between each like CSV example below

I need a macro to search column A on sheet one for duplicates and each row that is a duplicate copy entire row to a sheet named duplicates. Can anyone help?


I have a sheet that has multiple rows for each record, and I want to consolidated in one row. the number of records is variable, sometimes there are 3 rows for each records, sometimes there's only one. I'm sending aan example of the input and the desire output.

11 2 ee ff EM 1 2
11 2 ee ff DE 2 3
11 2 ee ff DE 3 4
12 1 ee ff EM 5 2

11 2 ee ff EM 1 2 DE 2 3 DE 3 4
12 1 ee ff EM 5 2

Please can you help me.


Simple one but tricky.. Help me in doing this:

Lets say I have 6 columns out of 15 with part of the header text as "locale". Now I want the macro to find all those 6 columns by searching "locale" in row A1 for headers and filter the columns with 2 criteria (equals "en" OR "blank")

For example sheet1 has:

A1-Code1 A2-Bx Locale A3-Code2 A4-Locale
B1-32432 B2-fr B3-435634 B4-ar
C1-36546 C2-en C3-67854 C4-"blank"
D1-65875 D2-"blank" D3-6532 D4-dh

After the macro it has to be:

A1-Code1 A2-Bx Locale A3-Code2 A4-Locale
C1-36546 C2-en C3-67854 C4-"blank"
D1-65875 D2-"blank" D3-6532 D4-dh

Any code please....

Thanks in advance...


I have a list of data which i need to filter down but have no idea where to start. The data has a list of accounts in column B, a list of currencies in column D, and a yes or no in column G. For each account a currency appears more than once, thus the account number is also repeated. However i need each account to currency to appear only once rather than be duped. I'm not sure wither a vlookup would be suitable or usingadvanced filter? I've attached a sample of the data before and after. As there are 14000 rows i don't really want to manually got through it and delete what i dont need.


Hello all,

I'm been working on a side project and am in need of help. I have a spreadsheet of data which I've been runing a series of macros on to "clean" the data, formating in a way which is easier to look at and compare later on. The issue presently is that I need to find duplicate values in a certain column, given a certain date and type. In other words, (1) check of duplicates in Date, if found (2) check those rows for duplicates in Type, if found (3) check for duplicates in Amount, if found (4) then Cut those rows which follow the above guildlines and move them to sheet 2. (to clarify - I need to cut out both parts of the duplicate, rather than just one)

If you can provide any help, that would be great. Attached is a spreadsheet with sample data and the items which I wish to dig out are highlighted in yellow.


Hello - I am trying to create a macro that will look at data and if
there is a specific value in any one of the 4 columns...keep that row
and delete all the others. I have this code working to consider one
column...but I cannot make it work to look at multiple columns for its
selection. Here is my one column code.

Dim rng As Range, cell As Range, del As Range
Set rng = Intersect(Range("G2:G9000"), ActiveSheet.UsedRange)
For Each cell In rng
If (cell.Value) "MSC" Then
If del Is Nothing Then
Set del = cell
Else: Set del = Union(del, cell)
End If
End If
Next cell
On Error Resume Next
del.EntireRow.Delete '

I have not been able to use G$ as it then deletes my header.
To span the multiple columns looking for MSC in any one of the cells in
E, F, G, or H I have tried replacing the G2 with E2:H9000 or E$:H$ and
if I do this - nothing happens, it's like the piece of code is skipped.

Any help would be greatly appreciated, I have been frustrated with this
for a few days now.

In my sheet I have it search for duplicate records and give those records the same id. Is there anyway to combine data into one record. For example, I have a person with a value in column Points1, on another row a value in column Points2, and another row a value in column Points3. I want take those values from the duplicate records and put them all in one record. I've attached a sample.

Unfortunately my strengths do not lie in VBA for Excel. But I have a problem which needs resolving using a macro because this spreadsheet scenario happens often. I have three columns (Record ID, Comment, Results). The Results column is added to capture concatenation of the Comments column. Using VBA, I want to concatenate Comments for each Record ID in the Results column on the row for that Record Id where the rows above have no Record Id. Each comment should stack in order with a carriage return between them. This should happen for each row where a Record ID exists and continue for the length of the spreadsheet until it finds the last row with a Record ID and should stop after concatenating its results. Can anyone help?

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