Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!

Highlight Rows Based On Duplicate With Conditions

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Hello everybody,

I have been looking for an answer to my question but all the solutions i found where not exactly what i needed and i was not able to adapt them to my needs.

(excel file attached)
I am looking for a way to highlight the whole row, each time :
- Columns A , B , C and E are duplicates (need that they will all have the same duplicate values)
- Only IF column J is different

In the attached example, you can see that Rows 3 and 4 were highlighted but row 7 was not because it has a different value in column J

View Answers     

Similar Excel Tutorials

Highlight Duplicate Values in Excel
How to highlight duplicate values in a list. Also, how to arrange those values next to each other so they are easy ...
Highlight Every Other Row in a Selection in Excel - Table Formatting
This free Excel macro will highlight every other row in a selection of cells with a color that is specified within ...
Delete Duplicate Rows
This macro will delete rows that appear twice in a list or worksheet. If two cells are identical, this macro will ...
Easily Compare Duplicate Values in Excel
Here, I'll show you a simple technique to quickly and easily compare large lists of duplicate values.  This allows ...

Helpful Excel Macros

Delete Duplicate Rows
- This macro will delete rows that appear twice in a list or worksheet. If two cells are identical, this macro will delete
Delete Entire Rows Based on Predefined Criteria (Text)
- This macro will allow you to specify certain criteria and then to delete rows based upon that criteria. You will choose
Highlight the Column of the Selected Cell
- This macro will highlight the column of the active cell. This will fill the column of the active cell with a solid back
Highlight the Row and Column of the Selected Cell
- This macro will highlight the row and column of the active cell. This will fill the column and row of the active cell w
Highlight the Row of the Selected Cell
- This macro will highlight the row and column of the active cell. This will fill the row of the active cell with a solid

Similar Topics


I'm familiar with how to Conditionally Format cells to highlight information when there's duplicate information.

What I'm wondering is if theres a way to highlight duplicate information in different colors. I have two columns, one with a description of issues, the second is product numbers.

I want excel to search for duplicate product numbers in the worksheet and group them by color, as opposed to simply highlighting ALL duplicates.

So that when I filter for Duplicates only, I can see them listed in different colors.

Attached is a small fraction of what my worksheet looks like, there are several more duplicate rows than what's shown, so manually doing this would be hard. In the file is what I have and then also what I want it to look like.

Hi guys,

Ok, I know there are several topics discussing deleting duplicate rows. They have been helpful in me understanding how to address my problem. But here is what I intend to do

1) I have a worksheet with approx 30000 rows (could be more)
2) There are duplicate rows. Duplicate definition: row 2 is a duplicate of row 1 if the values in A1, B1, C1, D1 match values in A2, B2, C2 and D2. I have highlighted the duplicate rows in my attached example.
3) I intend on deleting the duplicates, and keep 1 instance of the duplicate. So delete all duplicates, but keep 1.
4) Before deleting , add the values in a particular cell together. (in my example worksheet, Column 5). Put total in the row that wasnt deleted.

Any help will be greatly appreciated.
A formula or makro (will impress boss ) will suffice.

Kindly find example worksheet attached.

Thank you in advance.

I am having trouple finding a macro that will highlight duplicate rows. I am only finding VBA code out there that will highlight based on one column. I need it to actually find and highlight a true duplicate Row. My spreadsheet has data in columns A thru N. Any help in getting this set up would be great. Thanks.

I have a worksheet where employees scan barcodes of ISBN and Price into two columns. Right now I have a simple conditional format to highlight duplicates within each row. Is it possible to highlight duplicate values found within say 10 rows, rather than the entire column. I want it to alert them if they mistakenly scanned the same book twice, but not if it was same title scanned hours earlier. We use office 2010 with windows 7.

Thanks for any help.

I have a spreadsheet with about 50,000 rows, listing invoices paid for the past year and a half.

I have attached a sample file, and here is what I am hoping to do

In Column A, you see a vendor id, and in columns G, I, and J I have the data I need to work with.

In the end, I need the spreadsheet to highlight any cells that are duplicate values for each vendor.

So, if Vendor A has 2 invoices paid on the same day (column G), the cell would highlight. Likewise if Vendor A has 2 invoices with the same invoice number (column I), or 2 invoices with the same dollar amount (column J). So there are three potential areas where a duplicate value may occur, and I need to catch any of those 3.

In other words, if there is an invoice date that appears twice with the same vendor, highlight. If there is an invoice number that appears twice with the same vendor, highlight. And if there is an invoice amount that appears twice with the same vendor, highlight.

I know I can set this up for one vendor using conditional formatting, and then use the format painter all the way down, but we are talking about 50,000 cells and hundreds of vendors.

Is there any way I can conditional format the entire worksheet to do this?

What I am trying to do is similar to this post, but I can't get this answer to work for me.

I hope I made my question clear. Thank you very much for your help.

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,



I'm looking to highlight duplicate rows of data based on column A.

The comparison must be case sensitive.

are duplicates

are not duplicates.

Currently I can use the default conditional formatting function to find duplicates. However it is not case sensitive. Regardless of upper or lower case, if the exact phrase is there it thinks they are duplicates.

Is there a method to also search for duplicates and highlight them but take in to account the case as well?



I've tried a few different ways and I've done my searching on the internet, but I still can't find code to get this to work. I'm trying to highlight rows if there is more than one instance of column A and B.

I've attached a sample document of my data. The first 11 rows is what I'll start with, and rows 20-30 is what I'd like it to look like in the end. You'll see that the rows are only highlighted if there are multiple accounts of rows where column A and B are duped.

Also, I realize this can probably be done without VBA, but its part of a bigger macro so thats why I'd like to keep it VBA.

Let me know if you have any questions.



Hi All,

I am looking for a macro that will check the the values in 2 columns and if a match is found sum up the qty in another column and put this value in the row what will be kept before removing the duplicated rows.

The attached file shows the data and the result expected, my current file is 40k rows and will grow.

If col B & col C are the same
Add up the values of col N, based on different dates in Col O
Put the SUM of col N duplicate rows into the row with the newest (latest) date in col O
Remove duplicate rows
The qty Ordered col G should total the Made qty col N and the Shipped qty col E

Any help would be appreciated

If you need clarification on anything please let me know.
Thank you

I have been able to find some information from Chip Pearsons site - but am looking for a bit of guidance. Using the formula seen below - I am able to highlight the second and subsequent duplicate in a specific column.


What I need to to base the highlighting on two conditions within the same row. Column C and column V

Suggestion on how to modify the above to highlight the second and subsequent duplicate entry based on two conditions within the same row?

Thanks for the View!!


I've been trying to hack together code that will let me do the following:

1) In a certain column find duplicates.
2) For those duplicates check another column for duplicates
3) If found, highlight the rows.

I have searched and found a thread which seemed an almost exact match but now I'm stuck:

The attached file should be helpful

The code I have found in the thread mentioned got me far, but now I'm stuck. Also I must admit that I do not know what I am doing. That is because the code does not have enough comments too explain what it does.

Please Login or Register  to view this content.

The result of the above code is highlighting a couple (wrong) cells. I know I should get rid of the Union declarations, but I don't know how to make sure the correct rows are highlighted. Also I do not understand how this code exactly finds duplicates in the first column, saves the rows which are dupes, and then checks for duplicates in those rows but in another column.

As far as I understand it this will get all the entries in de B column, then put them in a dictionary array. But then it starts counting (I do not understand why) and after that I just do not have the skills to understand the code... yet .

Any help would be greatly appreciated!

Hello people,

I recently started working with Excel 2007 Macros, when I had to process a spreadsheet of several thousand lines long.

Unfortunately for me, I reckon that the skills I would need to process it are that of an advanced VBA user, and therefore am quite in need of some help.

If you check out the attached file (a fraction of the real one), you'll see that there are many duplicate rows in column B, "Cons_Ref".

What I want is to identify all those duplicate values in column B, and merge those rows within the range of the duplicates. Because the values in the other columns are either duplicates, or " - - : : ", I would like the actual date/time to overwrite the " - - : :".

I would really appreciate any help you would be able to give me. If you aren't willing to do this macro for me ; I'm willing to learn and do it myself, but have not yet found any in-depth online tutorials.

Thanks in advance.

I have a column of data that could sometimes have duplicate entries. I'd like to highlight every cell that contains a duplicate but exclude certain text. If possible I'd also like to highlight each set of duplicates using a different color.



In that example I'd like it to NOT highlight "n/a" as duplicates but still highlight abc123 and vcd456 as duplicates.

I tried using conditional formatting to highlight duplicates (all duplicate sets are same color) but it also highlights "n/a" and I cant seem to prevent that.

Hi all,

I need help creating a macro where some cells in a range (Say A1:H20) are highlighted dependant on if they are duplicate of cells that are to the right of the current selected cell. (selected cell "J2", cells to match"J3:J6").

The process I want to follow for using the macro is to open the worksheet, click a cell, then the duplicate data in cells A1:H20 will be highlighted, I then view the data for assessment.
I then move down the column to J3, then the next duplicate data in cells A1:H20 will be highlighted. I will probably need to repeat this step about 10 times per sheet. The cells that I will be selecting will be only in 2 columns.

An alternative to this method would be to select all the cells with the raw data (in a single row adjacent to each other, maximum 6 cells), then the duplicates will highlight.

I have been searching for something for a while, and have come close, but still can't find the answer. Im definately not an expert at VBA, but I do have good excel skills. BTW using excel 2007.

cheers in advance.

Hi all,

I am trying to do the following. I need to find duplicate pairs and then highlight the max value for each pair. I assume this can be done with a conditional formatting formula but can't seem to get it to work.

So my table would look like this:

Will have duplicate pairs / values to be highlighted
aa 1
aa 2
bb 3
bb 4
cc 5
cc 6
dd 7
dd 8
ee 9
ee 10

So I want the formula to compare rows 1 and 2 that have "aa" and then highlight the 2 be/c it is >1. Same thing with "bb", where 4 would be highlighted be/c 4>3 and so on down the list.


I have attached a sample worksheet of what I am looking for. It is color coded.

For rows 2, 3, 4, 5 - column D and E are empty.
For rows 5, 6, 7, 8 - column D and E containt text.
I'm looking for matches in column A and column B.

What I want to do is search my entire spreadsheet. For any row where D and E are empty, I want to search for duplicates in column A and B. If a duplicate is found, I want column D and column E from that row to populate into the empty D and E row.

In my example, for example: column A and B have duplicates in row 2 and row 6. Because of that, I want row 6's column D and column E to populate in row 2's column D and column E. In row 5, column A has a duplicate with row 9, but their columns B do not. In this case, I do not want any info to populate.

The colors in my spreadsheet show how I want the data to match up based on what I stated above.

My real spreadsheet is much bigger (roughly 4,000 rows), and not every row has a duplicate.

I hope I am being clear. If not, please let me know and I will try and explain better.

I really appreciate any help someone could provide!



I have a worksheet with a named range that varies from one row to several hundred rows long depending on data retrieved with a query. Each row has data in columns A thru H. I would like to examine the range, indetify consecutive duplicate rows and highlight them in light orange.

The named range is columns A thru H, rows 1 thru n. The rows are sorted on column A. I am trying to highlight the condition where columns B thru H are identical in two or more consecutive rows. There are no duplicates in Column A.

Can anyone help?

When I format any column of my spreadsheets to highlight duplicate values, my filtering slows to a crawl. It is entirely frustrating!!!!! I noticed the same issue on the last version of Excel and I just got a new HP with the I7 Processor so I don't think it is the computer. Is anyone else having this issue? Granted my spreadsheets can be hundreds of thousands of rows in length, however even when I shorten the rows to 2k I still have the same problem. Is there any better way to highlight duplicate values within a column that will not slow my filtering down??


I have a spreadsheet that is aranged by duplicates in column B I'm trying to find rogue duplicates...
...I can't use the standard conditional formatting "highlight duplicates" option as there are supposed to be duplicates on the sheet but all duplicates are supposed to be grouped by rows...
and I can't filter as they need to be kept in date order, Column A

I need a Conditional Formatting Formula or VBA code that will highlight duplicates that have been seperated

sorry I'm as bad at explaing as i am at excel.... I've attached an example
Any help would be seriously apprecited.

Hi all..
In excel 2007 the data is deleted if found duplicate, however I like to highlight an entire row with Red color (fill cell), both the duplicated rows......

Cells to check for duplicate values are all in column B.

Is Possible...?

Hi folks,

I'd appreciate any help on this as I am a novice and really don't want to do this manually!

I have a spreadsheet with over 100 rows and 100 columns. I want to highlight any columns which contain the exact same values. The entire column needs to be identical to the other highlighted column. So for example:

If Column A is the exact same as Column B and Column D then highlight all of the columns in orange.
If Column F and Column C contain the same values then highlight all of the columns in brown.
If Column G does not have a match then don't highlight.

An example of what I mean is attached.

Thanks for any help,

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.

Thanks in advance!!

Hi, Thanks for any help in advance,

I have attached an example sheet of how I would like it to work (if its possible) - i have colored the cells to show the desired effect of the conditional formatting but there are no formulas in place.

what im trying to get is this - I want to highlight a row of data that contains duplicates in separate columns/tables, so columns A-D is a table of client information, column E is blank and column F is copied & pasted data from another sheet of Client ID's i've already checked off. so i would like the client ID numbers to trigger a duplicate conditional formatting and highlight the rows duplicates are in so I can tell which ID's i have already checked from a crossover sheet.

I dont use VBA so if there are any formulas for this would be so very helpful!!

I have a problem when i have to find duplicating words in two columns. It doesn't highlight the duplicated words if they are in combination with other words. For example, if in cell A1 is the word "diet plan" and in the cell B2 is the word "diet recipe" it won't highlight the word "diet" as a duplicate. I am using this way to find duplicates - Conditional Formatting>Highlight cells rules>Duplicate values... but it doesn't work.

I want to highlight all type words from column A that have duplicates anywhere in column B and not just the words that are in the same rows.

How to do this?


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.