Free Microsoft Excel 2013 Quick Reference

Find and isolate duplicate criteria

I have a worksheet that tracks errors (10 different error types are tracked) that may occur on anywhere from 20 to 200 different jobs. What I need to be able to do is find all the jobs for any given day where a specific error has occurred two or more times for that day. Often a specific error will occured many times on the same job on the same day. What I need to end up with is only one row for each job that shows the Date/Time (m/d/yyy h:nn), JobNumber (number), ErrorNumber (text field) by either hiding or deleting those rows that do not meet the criteria.

I hope someone can give me some guidance. I'm stumped .


Post your answer or comment

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

how do i find and output duplicate values in multiple row or colums.
Thanks

Hi, I've spent days trying to resolve this and would appreciate any help. Have large data range up to 20,000 rows and need to find and delete duplicate headings within the rows (heading covers 9 rows separately). My code successfully finds and deletes the first row, but not the 8 subsequent rows. Error lies in the row that contains "resize" script below. I cannot use filtering as each heading row has similar text to some content that is to be kept, and the only unique row within the heading is the one I used to search "Report Date:" criteria.

Code:
Sub LRowDeleteHeadings()
Dim Firstrow As Long
Dim LastRow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long
 
With Application
CalcMode = .Calculation
    .Calculation = xlCalculationManual
    .ScreenUpdating = FalseEnd With
 
With ActiveSheet
'Set the first and last row to loop through
Firstrow = .UsedRange.Cells(14, 1).Row
LastRow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
 
'Loop from Lastrow to Firstrow (bottom to top)
For Lrow = LastRow To Firstrow Step -1
'Check the values in the A column
    With .Cells(Lrow, "A")If Not IsError(.Value) Then
 
            If Trim(.Value) = "Report Date:" Then _
                .Resize(1, 9).EntireRow.Delete
        End IfEnd WithNext LrowEnd With
 
With Application
.ScreenUpdating = True
    .Calculation = CalcModeEnd With
 
End Sub


In Excel I would like to look for and identify duplicate serial numbers either in the same spreadsheet or from two spreadsheets at a click of the mouse so that I do not have to combine the two spreadsheets into one, sort by serial number and check for duplicates manually. This eats up 30-45 minuets of my time daily, and another 45-60 minuets removing said redundancies. This is a step up from taking 6-8 hours and looking at each problem individually, but I lack the Excel knowledge and understanding to take this to the next level and make an 8-hour job a 45-60 minuit job. I also lack the knowledge to then create a macro to close those redundancies in order to make it into a 30-minuit (guess) job that I can have running on another computer while I do other work. Got to love computers, huh?

I bet this problem is most likely often encountered while making mailing address labels when bulk mailing . . . people do not want to pay for postage mailing to the same person twice. The difference is that I want to identify all the duplicate numbers in the spreadsheet and not remove them.

I have identified that Excels “vlookup” may be the very thing I need, but have no clue on how to use it.

The issue where this would be relevant is as follows -

If a First Alarm exists for the same network element (identified by serial number), as a Second Alarm, then the First Alarm is redundant and may be closed with extreme prejudice as an unacceptable waist of time.

I need to find a way to have Excel make a list of First Alarms that may be closed.

1. An Excel spreadsheet is created including a list of First Alarms (including the serial number of the network element in alarm).
2. An Excel spreadsheet is created including a list of Second Alarms (including the serial number of the network element in alarm).
3. Each alarm has a sequenced ticket number.
4. I need Excel to make a list of what serial numbers on the First Alarm spreadsheet exist on the second alarm spreadsheet and what the sequenced ticket number of the First Alarm is.
5. I will then need to figure out how to make a macro to close all those alarms, but that is another problem.

Hey Guys & Gals,

I have compiled a list of address with:

A1 Name B1 Address C1 City D1 State E1 Zip

I have some duplicates, is their an easy way find and delete them?

Thanks for the help.

Myrkk

I need a macro to search a field and delete duplicates. In my spreadsheet there may be duplicates of ID, First Name & Last Name. I have created a field to group these together so that I can find duplicates but how can I get excel to find and delete these leaving only one of the duplicates?

Thanks

Hi there, I'm hoping someone can help me with a formula as I'm not quite sure how to get this right. I have previously created a spreadsheet where I used index and match to find a value in a separate sheet and return the contents of a cell 1 row down and 6 columns across. I now need to do a similar thing but I need to match 2 different criteria instead of just 1. I have been experimenting with solutions from other posts but I can't quite figure out what I'm missing.

I've attached an example which hopefully can explain it a bit better. Basically I want a formula in the first sheet under the size column to find and match the criteria of "server name" and "path" in the second sheet (which contains exported data from an application) and then return the value in the second sheet 5 columns across. The idea being that I can export the data and paste it in the second sheet and the first sheet will update the required information from the data automatically.

I hope that makes sense, I've added in the formulas I've been trying to get working in the attachment. Any help would be greatly appreciated, thanks!

test.xls

I have a spreadshhets that has around 6000 names and email address i need
help on fining any duplicates and then deleting the original and its duplicate

I have two columns that is use to record last name first name. I need a macro that will find matching names in the columns. i have found some examples for searching one column but have not had any luck finding someway to do 2 columns. in the example below i need it to find and highlight The names Smith John. Thanks for your time.

           A                 B

    1    Smith           John
    2    King             Dustin 
    3    Poland          Paul
    4    Smith            John


I can't figure out how to do a multi-column condition search for duplicates. I need to find and highlight the duplicate entries, but each column has many duplicate entries. For example, the rows might look like this:

Yes 14 Sure
Yes 15 Sure
Yes 14 Nope
Yes 15 Sure
No 14 Ok
No 15 Sure

And so on. I have a list several columns hundreds of rows long with blank rows throughout. This is a silly example, and the file I'm using has much more complex text entries and there are hundreds of varying entries in each column. (I just don't want to use it because it's all sensitive content) I have no idea which entries might be duplicates. In this example, Only rows 2 and 4 would be considered duplicates, because all three columns match in each.

Let me know if you have any ideas,

Thanks,

Marcus

I need help finding only consecutive duplicates based on criteria in two columns. The first column, A, is the one with all the 1's and the 3 at the end. Column B is the long series of numbers and letters starting with 3:
A B
1 3051CD2A22A1AS2M5E5CN 1 3051CD2A22A1AS2M5E5CN 1 3051CD2A22A1AS9E5M5CN 1 3051CD2A22A1AS9E5M5CN 1 3051CD2A22A1AS2B4E5M5CN 1 3051TG3A2B21AS1E5M5CNP3 1 3051CD2A22A1AS2B4E5CN 3 3051CD2A22A1AS2B4E5CN
I need a macro that checks if there are consecutive duplicate entries in both column A and B. The two bold lines are examples of the consecutive duplicates I am talking about. The last two lines are not duplicate because the numbers in column A are not the same. If there are, then it will say "YES" in a new column in both of the rows that have the consecutive entries. And it needs to be able to skip the blank rows. Any help would be much appreciated.

-J

Hi there,

I have two spreadsheets, thousands of names generating from another software program.

I can combine the lists on one worksheet, but I need a way to find duplicate names on the list and highlight them for review.

Has anyone encountered anything like this before?

I am reading "Locate Duplicates" on the MS site, but it seems to only show how to do it with numbers and on a very small scale.

Thanks!

If I have two columns with data, how do I compare these two columns to find
all duplicates and put an X on a third colum next to all duplicates? Thanks.

Hi Somebody,

I need your help with a formula or a vba code.

This is what I am trying to do.
I have a spreadsheet with 7000+ records with many columns filled. I am
trying to find repeats (duplicates) and count them and enter the count
in a new column, same row where the first unique occurrence appear.
I have about 320 codes and about 500+ numbers which need to be counted
at the moment I am doing it manually. The list is growing.
This is the example of the sheet with the result.

The Code and Number column is what I need to count.

Seq Code Number Result Count
1 V 1-32100-0001-01 3
2 V 1-32100-0001-01
3 V 1-32100-0001-01
4 STR 1-42210-0001-01 2
5 STR 1-42210-0001-01
6 STR 1-42210-0001-03 1
7 V 1-32710-0001-01 2
8 V 1-32710-0001-01
9 NV 1-32710-0001-01 2
10 NV 1-32710-0001-01
11 FE 1-33310-0001-01 2
12 FE 1-33310-0001-01
13 NV 1-33310-0001-01 1
14 NV 1-33310-0001-02 3
15 NV 1-33310-0001-02
16 NV 1-33310-0001-02

I need to know how many column B (codes) counts are in each (Column C)
number . I have similar numbers with different dulicated codes.

I hope this message is clear
Thanks
FLDS

I hope I have explained this in a correct manner.
I thank you in advance for your help.
FLDS

First post, but I have done a search and cannot find what I'm looking for (I may have worded it wrongly though?).

Anyway, what I have is two workbooks both containing similar data from two different sources.

What I need to do is check/compare a column in sheet1 of workbook1 against a column in sheet1 of workbook2 for duplicate entries, and if I find any, delete the whole row from sheet1 of workbook2.

There is a different number of rows in each workbook so I would like to know if there is a way of scrolling though each column to the end without having to state 'P2:P4192' (otherwise I will have to manually change the start/end lines each time I run it).

Thanks

I've got a worksheet which contains several lists with numbers
(different ranges), which I want to verify and mark for duplicates.
Now if I use the 'find all' function from excel, the popup shows a list
of cells which correspond to the looked up value. I want to use that
result in my code to decide, if there is more than 1 match. But I have
no clue, on how to access that information. If I do the following 'IF -
THEN' statement (just a littel testversion - and for all you hard core
programmers out there: I'm a bloody beginner, so please excuse if its
not state of the art), its checking for the looked up value rather than
the instances:

Sub MarkDuplicates()

Cycles = 600
CI = 3
OS = 0

Range("DataArea").Select
LV = ActiveCell()

For j = 1 To Cycles

If Selection.Find(What:=LV) > 1 Then
Application.ReplaceFormat.Interior.ColorIndex = CI
Selection.Replace What:=LV, Replacement:="",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=True
CI = CI + 1
End If

OS = OS + 1
LV = ActiveCell.Offset(OS, 0)

Next j

End Sub

Can You please help me out and let me know, how I can solve this task?
thanks in advance for any sugestions. :-)

Hello,

New to the forums and a total novice at building macros and formulas. I am working on a problem at work where I am getting an export file from one system that I need to reformat to import into another. I have the vast majority of it complete, but I have one last nagging problem. The system that creates the export that is the source file cannot suppress page breaks, so it is writing the column header on each page. What I need to do is a conditional search which looks for "String10" in column A, and then looks for duplicates for the text in column b, and deletes any duplicate rows but LEAVES the first example. For example, the source file looks like this:

A        B          C        D   
    E
String10 citrush     
         ddddddddd 4,000.00 108.3452 99999  
         ddddddddd 4,000.00 103.9335 99999  
         ddddddddd 4,000.00 104.3948 99999  
         ddddddddd 4,000.00 114.1531 99999 
String10 citrush     
         ddddddddd 4,000.00 108.3452 99999  
         ddddddddd 4,000.00 103.9335 99999  
         ddddddddd 4,000.00 104.3948 99999  
         ddddddddd 4,000.00 114.1531 99999 
String10 JSmith     
         ddddddddd 4,000.00 108.3452 99999  
         ddddddddd 4,000.00 103.9335 99999  
         ddddddddd 4,000.00 104.3948 99999  
         ddddddddd 4,000.00 114.1531 99999 
String10 JSmith     
         ddddddddd 4,000.00 108.3452 99999  
         ddddddddd 4,000.00 103.9335 99999  
         ddddddddd 4,000.00 104.3948 99999  
         ddddddddd 4,000.00 114.1531 99999
I need to eliminate the entire row of the second example where String10 is followed in Column B by Citrush and JSmith. There may be multiple duplicates for the same text also.

I am using this macro already to reformat the row with String10 if it helps. I would like to do this by macro, but could use a formula if necessary and just create a process document for the users around it:

Sub Find_and_Write3()
  With Worksheets(1).Range("a1:a500")
    Set C = .Find("String10", LookIn:=xlValues)
    If Not C Is Nothing Then
        firstAddress = C.Address
        Do
            Cells(C.Row, "B") = Cells(C.Row, "B") & Application.WorksheetFunction.Rept(" ",
10) & Format(Cells(C.Row, "C"), "mm/dd/yyyy")
            Cells(C.Row, "C") = ClearContents
            Set C = .FindNext(C)
        Loop While Not C Is Nothing And C.Address <> firstAddress
    End If
  End With
Columns(2).AutoFit
End Sub
Any help is greatly appreciated.

Hello,

I have a list of people that I update once in a while. I need to find duplicate entries at the date of birth column and highlight this. Could not find what I want online (found some macros to delete the value, not to highlight).

Thanks

Hello,

I need your help. In my company, we are using excel files to keep our data, getting reports etc... Our problem is that we need to be able to find in a column if there are duplicated entries and flag them in a way so that we can find out if we accept this duplicated entries or not. The fields contain Names and Surnames in Greek Language (but i guess that doesn't affect the whole procedure). Can you please answer me as soon as possible? It's really important..I don't want to erase the duplicated fields,i just want to flag them so that manually afterwards i can go back and check these flagged cells...

Thank you in advance

I'm looking for some help in macro. I need to find duplicate from one column compared to another column then list them consecutively in another column. I have the rough macro working but need help making it more dynamic.

The user makes a selection of values in the first column manually. It then compares it to another column that I have to manually place the range numbers in the formula. I need the range to change in the formula each time a value is added to the compare to column. I tried with the letter z but the method error keeps coming up.

Also, the output column works but they don't list without having spaces between values.

I'm working at home & I don't have my notes and worksheets that I have at my work pc and my son asked me to do this for him this weekend.

Hello ! I would really appreciate any with this. I have attached a sample worksheet with the before (example 1) and an example with the after (example 2)

What I need is a macro to

1) Identify duplicate cells

2) Draw an outline/border around blocks of duplicate cells (within columns A:F) - around the entire block of rows that contain the duplicate cells

Note:

The example has 20 rows of information in the range to consider, but that is variable. It could be 20 or it could be 200. But the duplicate cells to find will always be in column C

Hi,

I have a bunch of files in folder A, that get distributed to about 10
other folders. Each file gets placed in only a single folder. All that
is done via a Macro,

I am fine with the macros to sort the files and distribute to the
various folders. BUT, what I'd like to do is before a file from A gets
placed into the destination folder, I'd like to scan that destination
folder to see if any filenames have the same 15 characters as the file
currently in A. If I do find that, it means that it is a duplicate
file (note that for legacy reasons the first 15 chars will be the same,
but after that it could be different - even for duplicate files).

So if I find a match with the first 15 characters, then I want to
delete the smaller file and end up with the bigger file in the
destination folder.

I hope this is clear..

Thanks!

phil

I am looking for a way to perform a find, and then copy multiple cells. The code below is what I have this far:


	VB:
	
 
Dim basebook As Workbook 
Dim mybook As Workbook 
Dim mybook2 As Workbook 
Dim sourceRange As Range 
Dim destrange As Range 
Dim SourceRcount As Long 
Dim N As Long 
Dim rnum As Long 
Dim rnum2 As Long 
Dim rnum3 As Long 
Dim MyPath As String 
Dim SaveDriveDir As String 
Dim FileName1 As Variant 
Dim FileName2 As Variant 
Dim FileName3 As Variant 
 
SaveDriveDir = CurDir 
MyPath = "C:Documents and SettingscgravleeDesktopOrganics" 
ChDrive MyPath 
ChDir MyPath 
 
FileName1 = Application.GetOpenFilename(filefilter:="Excel Files (*.csv), *.csv", _ 
MultiSelect:=True) 
 
If IsArray(FileName1) Then 
    Application.ScreenUpdating = False 
    Set basebook = ThisWorkbook 
    rnum = 16 'This tells what row
     
    For N = LBound(FileName1) To UBound(FileName1) 
        Set mybook = Workbooks.Open(FileName1(N)) 
        Set sourceRange = mybook.Worksheets(1).Cells.Find(What:="4-Bromofluorobenzene", After:=ActiveCell, LookIn:= _ 
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _ 
        xlNext, MatchCase:=False, SearchFormat:=False) 
        SourceRcount = sourceRange.Rows.Count 
        Set destrange = basebook.Worksheets(1).Cells(rnum, "A") 
         
        rnum = 5 'This tells what row
        basebook.Worksheets(1).Cells(rnum, "A").Value = "Standard ID: " + mybook.Name 
         
        sourceRange.Copy destrange 
         
        mybook.Close False 
        rnum = rnum + SourceRcount 
    Next 
End If 
 
If IsArray(FileName1) Then 
    Application.ScreenUpdating = False 
    Set basebook = ThisWorkbook 
    rnum = 17 'This tells what row
     
    For N = LBound(FileName1) To UBound(FileName1) 
        Set mybook = Workbooks.Open(FileName1(N)) 
        Set sourceRange = mybook.Worksheets(1).Cells.Find(What:="1,2-Dichloroethane-d4", After:=ActiveCell, LookIn:= _ 
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _ 
        xlNext, MatchCase:=False, SearchFormat:=False) 
        SourceRcount = sourceRange.Rows.Count 
        Set destrange = basebook.Worksheets(1).Cells(rnum, "A") 
         
        sourceRange.Copy destrange 
         
        mybook.Close False 
        rnum = rnum + SourceRcount 
    Next 
End If 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The code above will actual perform the find, and copy what is in the criteria of the search. However, I am trying to find a way to perform the search, copy the data that is in the search creteria plus data that is in another cell. The data in the other cell will always be different, so I cannot use that in a find criteria. The other data will always be found in the "E" column. I was thinking that possible there might be a way to perform the find, copy that entire row, and then just delete the columns that are not needed. However, I have been unsuccessful in my attempts.

I would appreciate any suggestions anyone can offer. If someone has a better way of performing this task, I will be more than happt yo give it a try.

If any further information is needed, please let me know.

Hello,
I regularly have to add a few new lines to what is in fact a very simple data base I've had running for a long time in Excel.
About 1200 lines now, one line per person.
I add a dozen or so lines (i.e. people) at a time in a different colour.
When I re-sort the whole thing I run my eye down the list to spot partial double (i.e. duplicate) entries (the new ones in their own colour helps ).
Then I delete the double entries one by one.
Pretty stoooopid, in'it?

How can I do this better, faster and more accurately with Excel to find just two duplicated data (NAME and ID NUMBER) in a person's line (there are 15 columns altogether)?
Or: Where and what can I search for (I've just spent nearly an hour trying to find an answer by myself, but don't really know what to search for)?

Many thanks in advance,
Brian


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