Email:      Pass:    Pass?


Advertisements


Free Excel Forum

Find Duplicate Over A Range.

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

In sheet 2's A column I have about 700 rows of numbers. In sheet one in G column I have over 2000 rows of numbers. Tried Conditional Formatting>Highlight Cell Rules>Text That Contains...then selected the range of 700 rows in the other sheet. No luck--it seems I can only highlight one cell at a time for this to work and it's rather impractical.

Anyone know of a way to highlight duplicates in a column from a range in another column?
I bet it's something simple that I overlooked.


Similar Excel Video Tutorials

Helpful Excel Macros

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
Reverse Row or Column Order in a Worksheet
- This macro reverses the order of a selected row or column in excel. It will completely reverse the data.
Highlight Cells with Text or Formulas (non-empty cells)
- This macro will highlight all cells in excel which are not empty. This means that if a cell contains formulas, text, nu

Similar Topics







I need the cells in a column (Range) to highlight if they are duplicate numbers only and ignore any text. I tried =countif(A2:A10,A2)>1 and then formatted it to highlight red just to see it highlight duplicates and all it did was highlight every cell within that range red. So what formula would I use in conditional formatting to highlight only numeric duplicates?


Thanks,
Jay


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 in column B and not just the words that are in the same rows.

How to do this?


Thanks!


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?


Thanks!

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.

ex:

abc123
vcd456
gjt856
n/a
869lfl
yty847
n/a
abc123
vcd456

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.


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?


Hey guys, first post, but hoping to get some quick help. I have an excel sheet in which I have columns of dates. These range from anywhere to a few hundred to a few thousand dates in each column. What I'm trying to do is find the duplicates from ALL 3 columns. I can use the highlight cell rules, but that doesn't give me the duplicates for all 3 columns. If there is a date in 2 of the columns, but not the 3rd, its not useful to me. What I need to get as an end result is the remaining dates which apply to all the columns to be selected so I can easy copy and paste them instead of going through and manually selecting thousands of dates. I tried using a macro to select the cells with the "red text" which was applied from the conditional formatting, but that didn't work for me. Thank in advanced, and sorry if this is confusing.

Bryant

Can I have a cell in 'Column A' notify me of duplicates in 'Column P'? I used the Conditional Formatting -> Highlight Cells Rules -> Duplicate Values to highlight the duplicates in Column P, but it's very time consuming when searching for a possible match in 20,000 cells. So if I could have a cell in a different column notify me of the duplicates, that would be preferred.


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

Hey,

I have a challenging excel problem.

I have a column A, with over 6000 cells, and a column B with 320 cells. All these cells contain text, not values. I want to be able to highlight text in cells in column A, that is found in ANY of the cells in column B.

I am using Excel 2007, therefore cannot use the Formula is option in conditional formatting. I have not found a way to be able to use conditional formatting to achieve this. Is there a way to highlight specific text containing ANY cells in column B?

The only way I can think to get around it now is to waste lots of space, and have all 320 text items in each cell of column B, then to highlight cells in column B that contain duplicate text in column A. I would much prefer a tidier method.

Can anyone help?


I'd like to highlight the rows that have any text in Column C. (For example, in the range of A1:J10, Cell C1, C5 contain text and other cells in Column C are blank. I'd like to highlight Row 1 and Row 5).

Could anyone tell me how I can do it? Thank you!


Hi There,

I have a spreadsheet which lists Part Numbers and how many weeks away thety are from their due date. However, I am trying to conditionally format the rows that that if;

"Arrears" is in any row in column F = Highlight Red

"Weeks 14 - 17" is in any row in column F = Highlight Blue

"Weeks 18 - 22" is in any row in column F = Highlight Yellow

"Weeks 23 - 26" is in any row in column F = Highlight Green

I have tried to get just one condition working (=$F5="Weeks 14 - 17") within the attached spreadsheet but cant seem to get it working. Can anybody help?

Thanks

Phil


I need a way to highlight duplicate cell entries across four sheets. Ideally, I'd like for this to happen automatically each time a duplicate entry is made on any sheet, but I could live with a macro to be run manually after a batch of entries have been made. All of the entries are in column A.

Conditional Formatting works to highlight all duplicates on a single sheet. But I need to signal the user that a new entry duplicates one on any of the four sheets and highlight both the new entry and the existing duplicated entry. I don't want to automatically delete anything. The user needs to review the duplicates and decide which to keep.

Any thoughts or ideas on this?

Thanks for your help.

Ray


Hi guys,

I can't quite find how to do this, I'm assuming it may have to be a macro rather than conditional formatting.
Problem - I am trying to highlight all cells in column B if the respective cell in BH is greater than 0.

Range = B1035:BH1035

Column B contains text
Column BH contains numbers

e.g.
If BH2 is 5 then B2 should be highlighted, If BH2 is 0 then B2 should remain the same.

Any ideas?

Thanks.


Hi

I have a simple 4 column excel sheet.

Column A - Part Number
Column B - Cost Price
Column C - Min Sales Price
Column D - Current Sales Price

Because there are over 3000 rows, I would like to know if I can perform the following please...

If Column D is less than Column C highlight the part numbers which are different or highlight it in some way so that I can quickly eyeball the sheet and find out which one differs.

Is this possible? Any help would be appreciated.

Regards


Column A has a list of Entity ID for each customer.
Column B has a list of Entity ID's in multiple instance for how many service appointments they have.

So in Office 2007, I can go to - conditional formatting -highlight cells - equal to. BUT, in only lets me select once cell in Column A to match in Column B.

I need it to look for all the numbers listed in Column A, and Highlight all the matching numbers in column B.

Ive thought about putting the ID's all in one column, and use conditional formatting to find unique, but since some people have two service calls, they dont come back as unique, even though that customer is unique since they dont match my list.

Attached is a sample sheet. The list will be very large so I cant go one by one.

Thanks for the help!


I maintain a spreadsheet that has about 7000 rows of data. One of the columns lists a number (like a serial number) that is unique to each row. There are no duplicates. Every day I receive a list of numbers that I have to bounce the sheet against to see if that number is represented. I want to use conditional formatting so that I can enter all numbers on a separate worksheet and automatically highlight any rows that match that number. Any ideas?


Hi

I would like to find a way that will look on a worksheet for a specfic word and highlight all the rows that contain this data.

I.e

Column A - Has the wording "JOB" & "WORK"

I want this to look down column A find all the rows with the word "JOBS" and just highlight them rows, i dont want a highlight like conditional formating but a highlight like when you click on the entire row

Thanks

DJ


Hi,

I require only one instance of a duplicate to be highlighted (Part 1 - below) and counted (Part 2 - below).

I have a Data Table of numbers that span many ROWS by eight COLUMNS. First Row number is 26, Columns C to J. Each Row of eight numbers needs to be checked for a "unique match" against a Named Range: Pass_Mark that spans one Row by eight Columns($I$9:$P$9). The Pass_Mark range will always contain "unique numbers" only.

Data Table range starts Row 26-350, Columns C to J
Pass_Mark range starts Row 9, Columns I to P

Part 1) Using Conditional Formatting:
Condition 1
=D26=C26 to highlight cell pattern and text in white to blank out one instance of all duplicate numbers in the Rows (so the highlighted numbers match the total count for each Row in Part 2).

Condition 2
=COUNTIF(Pass_Mark,C26) to highlight in orange any numbers in my Data Table that match with any of the eight numbers in Named Range Pass_Mark ($I$9:$P$9).

Example:
Pass_Mark range Row 9, Columns I J K L M N O P
60 65 70 75 80 85 90 95

Data Table Row 26, Columns C D E F G H I J M
70 75 60 70 80 70 60 70 4


Part 2) Row 26 Column M contains SUM and COUNT value of "unique matched numbers" from Data Table Row 26 matched to Pass_Mark range Row 9.

Desired Result:
Part 1) To avoid using Condition 1 where I blank out one instance of a duplicate number, is there a Formula that can be used in Conditional Formatting to actually highlight only one instance of a duplicate number in each Row of my Data Table that also matches a separate range of data as my Pass_Mark range.

Part 2) I need to Sum and Count each "unique match" in each Row of my Data Table that matches Named Range Pass_Mark ($I$9:$P$9). This Formula will be entered in Row 26, Column M. Must count each "unique match" and NOT unique numbers in each Row. Using the Example Data the answer/ value in Row 26, Column M should be 4.

Thanks
Tinä


Hi,

I require only one instance of a duplicate to be highlighted (Part 1 - below) and counted (Part 2 - below).

I have a Data Table of numbers that span many ROWS by eight COLUMNS. First Row number is 26, Columns C to J. Each Row of eight numbers needs to be checked for a "unique match" against a Named Range: Pass_Mark that spans one Row by eight Columns($I$9:$P$9). The Pass_Mark range will always contain "unique numbers" only.

Data Table range starts Row 26-350, Columns C to J
Pass_Mark range starts Row 9, Columns I to P

Part 1) Using Conditional Formatting:
Condition 1
=D26=C26 to highlight cell pattern and text in white to blank out one instance of all duplicate numbers in the Rows (so the highlighted numbers match the total count for each Row in Part 2).

Condition 2
=COUNTIF(Pass_Mark,C26) to highlight in orange any numbers in my Data Table that match with any of the eight numbers in Named Range Pass_Mark ($I$9:$P$9).

Example:
Pass_Mark range Row 9, Columns I J K L M N O P
60 65 70 75 80 85 90 95

Data Table Row 26, Columns C D E F G H I J M
70 75 60 70 80 70 60 70 4


Part 2) Row 26 Column M contains SUM and COUNT value of "unique matched numbers" from Data Table Row 26 matched to Pass_Mark range Row 9.

Desired Result:
Part 1) To avoid using Condition 1 where I blank out one instance of a duplicate number, is there a Formula that can be used in Conditional Formatting to actually highlight only one instance of a duplicate number in each Row of my Data Table that also matches a separate range of data as my Pass_Mark range.

Part 2) I need to Sum and Count each "unique match" in each Row of my Data Table that matches Named Range Pass_Mark ($I$9:$P$9). This Formula will be entered in Row 26, Column M. Must count each "unique match" and NOT unique numbers in each Row. Using the Example Data the answer/ value in Row 26, Column M should be 4.

Thanks
Tinä


Hey board,

I highlighted a column and selected "conditional formatting > highlight cell rules > less than" then entered in a date and selected formatting (red text). This did not work. My goal is to have any date less than 06/10/2010 highlight red in my column. How may I achieve this?

I'm on Windows XP and Excel 2007.


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.


Hey everyone,

I've created a rather long macro to format a workbook the way I need it. Everything works fine in the macro itself, I just have a simple question. Part of the macro adds conditional formatting to highlight rows which contain data in a certain column. Once the macro's done however, I need to add some new rows to the sheet...every time I do this it splits up the conditional formatting into multiple rules and it stops working.

So ,my question is...

How can I make it so that, when I insert rows after the macro's done, it doesn't screw up my conditional formatting.

Just fyi: the conditional formatting rules right now are applied to =$2:$1048576 because it also doesn't work if the header is included.

Thanks for any help,
AJ


I've found several articles on how to highlight row/column when a cell is selected, but they don't work when Cond. Formatting is already being used (for banding rows). Is there another approach that would not conflict with my conditional formatted banding, but still highlight the row and column of the active cell?


I would like to know how to apply a conditional format in this scenario.
I combined data from two spreadsheets that has similiar data and I did this to find out how they compare to eachother and I need to highlight any cell in one column that is not equal to the cell in the next column on the same row. The column of course can have many rows.

Example:
Grade1 is the column title in column A.
Grade 2 is the column title in column B.
In A2 the value is GS-12
In B2 the value is GS-13
I want to highlight B2 because it is not equal to A2. Or I want to highlight both A2 and B2 because they don't match. Either way I want to highlight cells that don't match.
Thanks.


Hi

I have a report in Excel 2003, where i enter lots of numbers which i need to higlight if a match is found.

Basically, i enter the numbers in the following columns:

J,K,L,M,N,O - From rows 7 - 200
AE,AF,AG,AH,AI,AJ - From rows 7 - 200
AZ,BA,BB,BC,BD,BE - From rows 7 - 200
BU,BV,BW,BX,BY,BZ - From rows 7 - 200
CP,CQ,CR,CS,CT,CU - From rows 7 - 200

and I need to know if a match is found in any of the below Columns

DD - From Rows 3 - 1000 (Range Named Orange - Highlights in Orange)
DE - From Rows 3 - 1000 (Range Named Green - Higlights In Green)
DF throught to EZ - From Rows 3-5000 (Range Named Blue - Highlights In Blue)

I can get this to work with a conditional format, however due to there being so many numbers it has to check against, it kills the performance of the report, so far that it becomes unusable.

Is there anyway i can get around this to make it faster and not make the file size huge (either through a macro or conditional formatting)?

Any help would be much appreciated.

Edit

Oh and just to confirm, the numbers need to highlight in the section below

J,K,L,M,N,O - From rows 7 - 200
AE,AF,AG,AH,AI,AJ - From rows 7 - 200
AZ,BA,BB,BC,BD,BE - From rows 7 - 200
BU,BV,BW,BX,BY,BZ - From rows 7 - 200
CP,CQ,CR,CS,CT,CU - From rows 7 - 200