Free Microsoft Excel 2013 Quick Reference

VBA code to find duplicate values within multiple rows and columns

May I ask your help on how to code a macro that will allow me to find duplicate values within a range (for example A1:M100) on a worksheet and color the cells with duplicate values?

Post your answer or comment

comments powered by Disqus
($20 USD) Hello,

I need this code to find duplicate numbers and not text across multiple sheets in excel. The code is already properly formatted to find data in the fields needed. Just for clairification, this code will find duplicate text (names) but I need it to find duplicate numbers and continue to count the number of times the duplicate was found and also continue to tell me what worksheets the duplicate can be found in. Also, I only want the duplicate numbers listed 1 time on the report page and not listed as many times as it was found. I want the count to specify that. I’m willing to pay $20 USD. I need this completed by 07/15 end of day. Here is a sample of the code below. I have also attached a sample file of what the code currently does.

     'This routine will go through each worksheet in the workbook and if
     'the worksheet contains names in the expected format, those names will be compared
     'to the existing list, if the name is not found it will be included to the list
     'if it is found the count will be increased and the worksheet name added to a list of
     'worksheet names for that name
    Dim myOutputRange                       As Range 
    Dim myRange                             As Range 
    Dim mySearchRange                       As Range 
    Dim i                                   As Integer 
    Dim j                                   As Integer 
    Dim k                                   As Integer 
    Dim intNumberRecordsRead                As Integer 
    Dim intNumberOfDuplicates               As Integer 
    Dim myWorksheet                         As Worksheet 
    Dim blnFoundDuplicate                   As Boolean 
    Const strStartRange = "A1" 
    Const strStartText = "Name" 
     'First lets clear the previous report results
    Set myOutputRange = ThisWorkbook.Worksheets("Duplicate Report").Range("DuplicateReportStartHeading") 
    Set myOutputRange = Range(myOutputRange.Offset(1, 0), myOutputRange.Offset(1, 0).End(xlToRight)) 
    Set myOutputRange = Range(myOutputRange, myOutputRange.SpecialCells(xlLastCell)) 
    ThisWorkbook.Worksheets("Duplicate Report").Range("DuplicateReportLastRunDate").Value = "Running Report....Wait" 
    ThisWorkbook.Worksheets("Duplicate Report").Range("DuplicateReportNumberOfRecordsRead").Value = 0 
    ThisWorkbook.Worksheets("Duplicate Report").Range("DuplicateReportNumberOfDuplicates").Value = 0 
     'Now lets go through the workbook and find each of the worksheets in the right format to create the duplicate list
    Set myOutputRange = ThisWorkbook.Worksheets("Duplicate Report").Range("DuplicateReportStartHeading") 
    Set mySearchRange = ThisWorkbook.Worksheets("Duplicate Report").Range("DuplicateReportStartHeading") 
    i = 1 
    For Each myWorksheet In ThisWorkbook.Worksheets 
        If myWorksheet.Name  "Duplicate Report" Then 
            If myWorksheet.Range(strStartRange).Value = strStartText Then 
                Set myRange = myWorksheet.Range(strStartRange) 
                j = 1 
                Do While myRange.Offset(j, 1).Value  "" 
                     'Search for the record if it already exists then update the counts
                    k = 1 
                    blnFoundDuplicate = False 
                    Do While mySearchRange.Offset(k, 1).Value  "" 
                        If mySearchRange.Offset(k, 8).Value = Trim(myRange.Offset(j, 0).Value) Then 
                            mySearchRange.Offset(k, 6).Value = mySearchRange.Offset(k, 6).Value + 1 
                            mySearchRange.Offset(k, 7).Value = mySearchRange.Offset(k, 7).Value & ", " & myWorksheet.Name 
                            intNumberOfDuplicates = intNumberOfDuplicates + 1 
                            blnFoundDuplicate = True 
                            Exit Do 
                        End If 
                        k = k + 1 
                     'If the record did not already exist then write it to the report
                    If Not blnFoundDuplicate Then 
                        myOutputRange.Offset(i, 0).Value = WorksheetFunction.Trim(myRange.Offset(j, 0).Value) 
                        myOutputRange.Offset(i, 1).Value = myRange.Offset(j, 1).Value 
                        myOutputRange.Offset(i, 2).Value = myRange.Offset(j, 2).Value 
                        myOutputRange.Offset(i, 3).Value = myRange.Offset(j, 3).Value 
                         'myOutputRange.Offset(i, 4).Value = myRange.Offset(j, 4).Value
                         'myOutputRange.Offset(i, 5).Value = myRange.Offset(j, 5).Value
                        myOutputRange.Offset(i, 6).Value = 1 
                        myOutputRange.Offset(i, 7).Value = myWorksheet.Name 
                        myOutputRange.Offset(i, 8).Value = WorksheetFunction.Trim(myRange.Offset(j, 0).Value) 
                        i = i + 1 
                    End If 
                    j = j + 1 
                intNumberRecordsRead = intNumberRecordsRead + j - 1 
            End If 
        End If 
        Set myOutputRange = Range(myOutputRange, myOutputRange.End(xlToRight)) 
        Set myOutputRange = Range(myOutputRange, myOutputRange.SpecialCells(xlLastCell)) 
        With myOutputRange 
            .Cells.Sort Key1:=.Columns(7), Order1:=xlDescending, Header:=xlYes, _ 
            Key2:=.Columns(1), Order2:=xlAscending 
        End With 
        Set myOutputRange = ThisWorkbook.Worksheets("Duplicate Report").Range("DuplicateReportStartHeading") 
    ThisWorkbook.Worksheets("Duplicate Report").Range("DuplicateReportLastRunDate").Value = Now() 
    ThisWorkbook.Worksheets("Duplicate Report").Range("DuplicateReportNumberOfRecordsRead").Value = intNumberRecordsRead 
    ThisWorkbook.Worksheets("Duplicate Report").Range("DuplicateReportNumberOfDuplicates").Value = intNumberOfDuplicates 
    MsgBox "Duplicate Report has Completed", vbInformation, "Report Complete" 
End Sub 

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

Hi. I need a way (can be a macro) to find duplicate words in a column, and then to copy those duplicated values to another column.

An example. My column looks like this:

Row1 - John Cage / Johnny Bravo / Rambo / Indiana Jones / Neo
Row2 - Frodo / Bruce Lee / Steve / Harry / John Cage

I need to find every single duplicate name, and then to copy them to another column. Problem is, I can't seem to find a macro that does that - since the macros I have look only for exact values in the rows, they don't recognize as duplicate anything in a row that simply has a partial identification with another row. For example, the macro I have does not recognize as duplicate the "John Cage"s in those two rows - because their respective rows aren't exactly equal, only two words in them are - the "John Cage"s. But I need to find those "John Cage"s.

Is there a solution for my problem?
Thank you for any advice you may be able to offer.

Hi Guys,

Please I need a vba code to find Invoice No. in a database and replace content of a cell in the same row.

I have a Sales database with 7 columns, as follows:

1. Invoice No. - Column A
2. Invoice Date - Column B
3. Month of Sale - Column C
4. Gross Amount - Column D
5, Vat Amount - Column E
6. Net Amount - Column F
7. Date Paid - Column G

I have created a Multipage Excel Userform for - (1). Sales Data Entry; and (2).Receipts Entry.

I already have a vba code for the Sales Data Entry, and the code works fine. When an invoice is generated, the Sales Data Entry part of the Multipage Userform is used to enter the sales invoice details onto the database. At this stage, what is entered in the last column i.e. Column G is the word 'UNPAID'.

The second page of the Multipage Userform is to be used to enter Receipts and update the database. That page contains only two textboxes, namely - (1) Invoice No. and (2) Date Paid, and a command button named 'Enter Receipt'.

Please I need a macro to run the page 2 of multipage userform (i.e. the 'Receipts' page) - so that when a user enters the Invoice No. and Date Paid, and clicks the 'Enter Receipts' button, the macro will make a search in Column A of the database for the Invoice No. that has been paid. If the Invoice No. is found, then replace the word 'UNPAID' that is entered against the invoice no. in Column G with the Date Paid (in dd/mm/yyyy format).

I need to clarify that each Invoice No. is unique and is never repeated in Column A (i.e. an invoice number cannot appear more than once in column A). The vba code should also be able to provide a message saying 'Invoice No. not found' if the search does not find an invoice no. that is keyed into the 'Receipts' userform (may be in error).

Thanks in advance for your kind help.


Good day everyone!!
I would really appreciate some help with a formula that can sum up Costs over multiple rows and columns based on the year to date. I attached a sample of the data I'm trying to work on.

I'm trying to do an analysis on a quarterly basis, e.g. Jan vs Mar.
For Jan vs Mar, I chose Mar in the drop down box and the Sales - Mar YTD will be shown in the Total column. For the Jan part, I used the formula "=SUM(D14:CHOOSE(E16,D14,E14,F14,G14,H14,I14,J14,K14,L14,M14,N14,O14))" with E16 showing "1" for the month of Jan.
As Sales is only on 1 row (Row 14), my colleague helped me with the above formula.

For Costs (which could be 4 rows or more), the Costs - Mar YTD is also shown in the Total column. However, for the Jan part (highlighted in red), I'm stumped. I could repeat a variation of "=SUM(D14:CHOOSE(E16,D14,E14,F14,G14,H14,I14,J14,K14,L14,M14,N14,O14))" 4 times, but is there any simpler way to do this? I'd like the option of switching up the months for analysis so a simple formula would really help.

Apologies if my question is "wordy" but I could really use some help as I'm not very Excel-savvy!!

Hi All,

I have reported the above question in forum ( with the attachment.

Any help would be greatly appreciated.

Many Regards

Hi All,

Respected Members please accept my sincere thanks for all your esteemed help given through this channel.

I have attached a sample worksheet which I use in work. My aim is to find job nuumbers on sheet1 in the dataset on sheet 2(column A) and return the corresponding values iolumn B & C on sheet 2. Please note that one job number can be paid more than one time in the past months(eg. 3300023104), and therefore the requirement is to total the amount of money paid in all months, on a particular job reference. The output i require on sheet 1 is "month paid in" and "amount paid ". If a job is paid just once then simply get the month and amount on sheet 1. But i dont know if a paricular job like 3300023104 has been paid loads of times in several months, how can we list the months as well as the amount. If months is not possible then only amount can do...

My objective is to find out how much money has been paid before on the jobs mentioned on sheet 1 column A (my current month invoice). I already have a vba code to trace any duplicates within column A on sheet 1. For instance job ref 3300023104 has been repeated a few times on sheet 1.

Please advise at your convenience.

Thank you

Please note firstly I've post this on different forum but didn't get any solution after many hours of waiting, so can someone help me out here please. Where I've posted: here and here and here

I need the VBA code to find the highest and 2nd highest value in a column based on criteria in another column. So for example:
Type | Time
RaceA| 4.5
RaceB| 5.5
RaceA| 6.2
RaceA| 3.1
RaceB| 2.1
I need the VBA code to be able to find the highest and 2nd highest Time for RaceA and highlight them in different color. So in the example above, the code should loop through the time based on Type and highlight 3.1 as highest and 4.5 as second highest
I need the vba sub instead of worksheet function.
Can anyone help pls?

i am looking for 'VBA code to find System (Desktop) date format'
Could anyone help me to the code.

I am creating a file which will be used by different users having different sytem date formats i.e mm - dd - yyyy or dd - mm - yyyy &
my file calculation depends on the date calculations based on inputs provided in different formats,

I need VBA code to select particular ranges from multiple worksheets and create a new workbook which is then e-mailed once a week. Any suggestions? I am new to Visual Basic and can figure things out if I have a starting point. I cannot write from scratch.



or is it restricted such that the lookup array can only be one column ie: A:A when finding row number or one row ie: 1:1 when finding column number

I'm using the index and match functions in combination and I want the lookup array in the match functions to be the same as the lookup array in the index function.

IE: lets say I want to search vertically for a list of work activity codes and I want to search horizontally for various metrics such as work accomplished, actual hours spent and so on.

but I don't want to always be restricted to having the raw data I'm searching in the same format where activity codes are always column B and the status/metrics on those activity codes is always row 4.

I want to be able to search an entire array of multiple rows and columns (or an entire spreadsheet) for a specific reference and have it return the row number that reference is in.

then I want to be able to search an entire array of multiple rows and columns (or an entire spreadsheet) for a another different specific reference, like hours spent, and then return the column number that reference is in

then i want to have the value returned that occurs at the intersection of that row and column either using the index function or some other function

I tried something like:

=Index(A1:AR90, Match(AC312, A1:AR90, 0), Match("Hours Spent", A1:AR90, 0))

But it doesn't work.

However If i have

=Index(A1:AR90, Match("ITXRP", C:C, 0), Match("Hours Spent", 1:1, 0))

It works just fine.

However this restricts me to always having to make sure the raw data export I get is in the format where Activity Code is in column C and the metrics headers are in Row 2.

I want the Functions to work whether I have the activity codes listed in Column M and the headers in row8, or activity codes in column E and Headers in row 4 etc.- to still work regardless of what column my activity codes are in and regardless of what row the metrics headers are in

I thought maybe a nested match like Match("IXRTP", then for array using another match that would return the column, but that match would have to have a specific row selected and you could use another nested match to get the row but then you would have to select a specific column to search.

I couldn't get it to work and I don't think that would work either because it seems to me it would run into a circular logic issue

Is there a function that will search an entire array of multiple rows and columns (A1:AC90) or an entire sheet and return a specific number for the row number and do the same for the column number such that it could be used in the index function or some other function

Thanks for any input

I would like to be prevented from making twice the same entry in each row and in each column of a worksheet. I have tried Data Validation but it seems that it does not work as I am not considering a range but just the cells within a row and a column.

Thank you in advance for any suggestions?


Hello All,

I need help on solving one requirement where the data on multiple rows and columns has to be transposed to single horizontal row for each unique Id.
To be clear basically I Have the different cost data for same Id on multiple rows(Minimun 0 to max of 6 rows for a particular ID) with cost and description values in 2 columns and I want all the costs and description for a particular ID to be moved onto one single row as



I have attached the sheet with the actual data in Sheet1 and the desired data set in Sheet2.

Can anyone help me on this please.
Thanks in Advance.


I need to analyze last value of each row and get top 10 of repeating values

For example
1|112 4 8 ...... 3
2|55 54 ...... 3
3|1 7 3 2............9
4|82 977 15................................10
5|45 477............................................9
6|77 2244..........................9
7|557 4458..............................9
8|78 428...................................10
9|44 326.........................10

In result I want to get TOP
1. 9 (4 times)
2. 10 (3 times)
3. 3 (2 times)

I have a problem which I have not been able to solve.
I have 2 worksheets, 1 with 3000 rows of data consisting of 14 columns. The key Column describing a product Code. Call this main worksheet 1

I have another worksheet with product codes and descriptions that are unique to a certain supplier who supplies these products. call this worksheet 2

I need to retrieve in order to create a report the data (rows and columns) from worksheet 1 that match the product codes in worksheet 2. In other words I am trying to get only certain codes that match a certain supplier. The codes that are unique to the supplier that i want info on are in worksheet 2
I have tried the index match functions with no luck
I can provide a sample of the worksheets if needed.
Hoping you can offer a suggestion.
Dan H

how to graph when data is in rows and columns.
here is how my data looks
25 25 24 27
23 25 20 20
20 25 28 28

so how can i graph this or get the data in one column? it should be like


Need some help on this...

i have some data like this

Row 1 : a b c d e f a c b
Row 2 : 1 b 4 n j k 1 b
Row 3 : a n k o j u n k

I want the output to be like

Row 1 : a b c d e f
Row 2 : 1 b 4 n j k
Row 3 : a n k o j u

Rows without duplicate values. Can some one help with the VBA code for this?...

With regards,


I am struggling to create/find a procedure that will find hard coded values in formula. So far I have been using code that searches for special cells e.g. GoTo Specials cells for constants with Number and Text. However I can not find a way of selecting cells which are part refering to another cell and part refering to a hardcoded value or a formula that just refers to a hardcoded value i.e. a formula with no precedents.

Examples of formula I can not automatically select:


The formulas above contain a hardcoded value and its cells that contain this type of formula I want to automatically select without selecting every formula in the worsksheet.

Hope People can help

Best Regards



I have a task that pulls out information from the website. I need to write a vba code to find the last row. Since the no of rows are always not the same. I would need the excel masters help to solve it. Just a small piece of guidance would also help me.

In my work sheet I need to find the word " Function Name: Cleaning" if this is there then I should find the occurence of the word " name". If I find that then the code should copy the values in the column after name till it sees a space ( means no value). Should copy till that and paste that in another work sheet.

Am confused to consturct the if cases here. Have attached a sample file.

Any help is greatly appreciated.

Thanks in advance for the help.

A newbe question.

I have a worksheet in which the values in cells B8, H37, H42, H48 and H49 must be negative. Occasionally the values get entered as positive numbers.

I would like to use VBA code to automatically change the value from positive to negative.

It appears that the WorkSheet_Change event could be used to do this but I don't know what code to enter into the Sub.

Any help is appreciated.

Hi everybody,

I need a VBA code to copy the values in one sheet to the other
sheet. Here I'm giving my actual problem.

I have data in columns A to M which is generated by some conditional
formulas, if the condition is satisfied it will generate real values in
the cells if the condition is unsatisfied then the cell will display
"FALSE". Now what I need is a macro that copies only the cells which
are having values and paste to the other sheet spcecified and also I
need to copy column A to Column A ...So on, but I wnat to skip some
columns like E&H.

I got a macro in this group which copy and paste values&Formats to the
other sheet by selecting the range manually and then pasting the values
at the destination by running the macro.

The macro is like this.

Sub Pastesp()
with selection
pastespecial xlvalues
pastespecial xlformats
end sub.

can any body helpme to expand this code so that I can apply for my
specific problem.

Thanks and Regards


Hi everybody,

I need a VBA code to copy the values in one sheet to the other
sheet. Here I'm giving my actual problem.

I have data in columns A to M which is generated by some conditional
formulas, if the condition is satisfied it will generate real values in
the cells if the condition is unsatisfied then the cell will display
"FALSE". Now what I need is a macro that copies only the cells which
are having values and paste to the other sheet spcecified and also I
need to copy column A to Column A ...So on, but I wnat to skip some
columns like E&H.

I got a macro in this group which copy and paste values&Formats to the
other sheet by selecting the range manually and then pasting the values
at the destination by running the macro.

The macro is like this.

Sub Pastesp()
with selection
pastespecial xlvalues
pastespecial xlformats
end sub.

can any body helpme to expand this code so that I can apply for my
specific problem.

Thanks and Regards


I am trying to write VBA code to find the first occurrence of #N/A in a range, equivalent to the function ISNA() returning TRUE.

The following code does NOT work ...

Sub FindNA()For Each Cell In Range("MyRange")If Cell.Value = ISNA() Then
Range("M1").Value = Cell.Address
End IfNextEnd Sub Appreciate if someone could tell me what is wrong with the above and what the VBA equivalent of the ISNA() function is.

Thank you very much.

hi all,

I am attempting to use conditional highlighting to highlight duplicate values in a range of data in excel 2007.

Here is exactly what I want:
Column A is a function of a 'toggle switch'. So if I change conditions of the toggle switch the values in column A change.

Columns B through N have static values in them. Some columns have duplicate values within them (i.e. column B has two entries for 'iphone')

I want cells within each column (B:N) to highlight when they match a value in column A.

Column A
Column B
Column C

Ideally, this formula would be backwards compatible with excel 2003.

I'm not familiar with VB so I need it to be a function.

I uploaded an example file to show you what I am looking to do.
In the worksheet called 'stage summary', i have it set up as follows:

Range A6:A150: values are a function of the cell A2 which can be toggled to one of several different options. (example: select 'unclassified' from the drop-down menu in A2 and you will have only 1 value in range A6:A150. If you select all, you'll have 132 entries).
Array B6:M150 are autopopulated cells taken from the worksheet 'Targets'. The cells do not change in response to changes in cell A2.

What I want, is for cells in the array B6:M150 to highlight red when they are duplicates of values in the range A6:A150.

PROBLEM: Some values appear more than once in array B6:M150 (example: Exa77 appears across several different stages and twice within one stage). I don't want these values highlighted unless they match a value in column A.

Thanks very much.

May I ask how will I be able to find a blank cell within a range (for example, A1:M100) and fill the blank cell with color?

I have an array A1:F1500 is important to find if any of the rows are repeated, I attache a little example, I don't want use excel functions I want a vba code to do this task, I am using excel 2003. any executable file welcome. thanks.
HTML Code: 
12	6	1	0	2	8
1	9	21	23	17	16
28	35	35	33	26	22
29	28	22	24	22	17
6	4	2	12	26	21
2	5	1	0	4	4
0	1	9	15	24	3
1	12	18	17	22	0
5	18	21	19	23	14
5	10	11	7	7	11
1	6	20	20	19	9
22	19	1	2	3	7
28	29	27	19	5	8
2	4	9	2	16	0
6	4	14	17	10	6
6	7	4	9	11	7
5	9	4	7	10	8
16	23	20	23	7	2
7	3	13	17	3	2
3	21	9	5	5	6
1	21	19	11	8	1
2	14	19	16	11	2
3	12	18	16	13	1
1	4	3	7	0	0
2	4	8	12	11	5
8	8	11	8	1	5
1	3	25	23	21	25
1	1	9	6	25	26
3	11	16	21	2	0
5	18	36	37	18	14
2	16	18	19	1	1
2	10	4	2	0	2
16	18	13	18	15	14
14	16	26	12	12	2
5	2	0	14	11	14
1	1	2	2	16	6
0	5	6	4	8	10
7	19	17	13	5	8
7	13	19	15	2	7
6	5	14	22	11	13
8	12	24	7	10	0
10	27	29	2	9	1
8	23	22	11	13	4

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