Highlight exact match in column

I have a column with 10000 entries. I want to highlight duplicate cells that 
have the exact same text string order.
I would have the following:

Cap
Fernco Cap
Coupling
Coupling
Fernco Coupling
Fernco Coupling
Fernco Coupling
Fernco Coupling
Fernco Coupling
Where as the 1st 2 lines are not matching but the remaining would be 
duplicates. I would like to highlight not delete these cells. Or find the 1st 
one and cross-out the remaining.
0
Utf
5/28/2010 5:45:01 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
742 Views

Similar Articles

[PageSpeed] 30

Using excel 2007:

If your items are in column A, select A to highlight the whole column, then 
go into your conditional formatting. Select New Rule..., Select "Use formula 
to determine which cells to format. In the Format Values... box put:

=A1=A65536

Press the format button and select your formatting. Press ok twice. In the 
"Applies to" box put: (if it is not already there)

=$A:$A

Apply and ok.

Squeaky

"mgbcab" wrote:

> I have a column with 10000 entries. I want to highlight duplicate cells that 
> have the exact same text string order.
> I would have the following:
> 
> Cap
> Fernco Cap
> Coupling
> Coupling
> Fernco Coupling
> Fernco Coupling
> Fernco Coupling
> Fernco Coupling
> Fernco Coupling
> Where as the 1st 2 lines are not matching but the remaining would be 
> duplicates. I would like to highlight not delete these cells. Or find the 1st 
> one and cross-out the remaining.
0
Utf
5/28/2010 6:59:02 PM
Thanks this work great but now I another problem to address. I would like to 
count the number of duplicate cells based this CF. I have tried VBA codes but 
they do not seem to work with a formula based CF and only count manually 
colored cells. I also could sort the colored cells then have a helper coulmn 
to count them or just have a cell to count the column of CF cells.

"מיכאל (מיקי) אבידן" wrote:

> Assuming the values resides in range A1:A10000
> Select that range > Format > Conditional Format > in 'Condition1' choose 
> 'the formula is' > type:
> =COUNTIF(A1:A$10000,A1)>1
> select some background pattern color > OK.
> Micky
> 
> 
> "mgbcab" wrote:
> 
> > I have a column with 10000 entries. I want to highlight duplicate cells that 
> > have the exact same text string order.
> > I would have the following:
> > 
> > Cap
> > Fernco Cap
> > Coupling
> > Coupling
> > Fernco Coupling
> > Fernco Coupling
> > Fernco Coupling
> > Fernco Coupling
> > Fernco Coupling
> > Where as the 1st 2 lines are not matching but the remaining would be 
> > duplicates. I would like to highlight not delete these cells. Or find the 1st 
> > one and cross-out the remaining.
0
Utf
6/2/2010 12:22:01 PM
Reply:

Similar Artilces:

Sync of .OST to server does not match?
We have multiple users that have this issue: Exchange 5.5 sp4 with Outlook 2002 SP2. When the user select to sync, it completes without errors but then if you compare the messages count for the online folder and the offline folder - they do not match. This is true for folders such as inbox/sent. I have started outlook in offline mode and forced a synchronization - this did not fix it. Also verified that there are no filters set on the sync setup. In one instance, I have deleted the .ost and created a new one. After a while, the message count again does not match. Any suggestions on wher...

Excel Compare values in columns & display missing values in a new
How can I compare a master part numbers list in one Excel column or worksheet against actual values in a second Excel column or worksheet, and display the missing part numbers that were not in the second column in a new column or worksheet? For Example: Part Number Master Part List Missing Parts 12A221315 12A221315 12A221332 12A221316 12A221316 12A221333 12A221317 12A221317 12A221318 12A221318 12A221319 12A221319 12A221320 12A221320 12A221321 12A221321 12A221322 12A221322 12A221323 12A221323 12A221324 12A221324 12A221325 12A221325 12A221326 12A221326 12A221327 12A22132...

Two-column lookup help!
Greetings, I am having difficulty looking up an item using two-column lookup formulas as suggested. Following are the parameters of my situation 2 Different Workbooks Workbook 1 (Reference Workbook) Column b c d 125 Lay Foundation March 9, 2004 125 Purchase Frame mat. March 27, 2004 125 House complete May 15, 2004 267 Lay Foundation June 5, 2004 267 Purchase Frame mat. June 28, 2004 267 House complete August 29, 2004 Workbook...

Compare and Highlight Rows
I have an excel file with two worksheets. is there a way to programatically compare the two worksheets to find matches and highlight those matches on each worksheet. They both have the same columns. I want to compare the InspectionID column. The Inspection ID column may have duplicates in both of the worksheets. I have never done anything in excel above the beginner level. However, I am a very skilled VBA programmer (programmed in ACCESS for over 10 years). Thank you You may find it easier to use Conditional formatting instead: For instance, with Sheet1 column A selected, and cel...

hiding specific columns
how do i stop certain people viewing specified columns in excel and are viewable only via a password Good morning Joe Black You could protect that fairly easily by using Tools > Protection > Worksheet Protection. It will keep out all users and will require a password. Now the bad news. It's not very secure at all and anyone who finds newsgroups like this can find code listings and sites that show the code that would be required to remove the password. The bottom line - it can be used to prevant accidental change or erasure and will keep out some users, but if you want to keep...

highlighting #2
I was told that although that there is no official highlight button for publisher like there is for word but there are ways to get around it. I am using the 2003 version of publisher. does anyone know the trick. Make a small box and fill it with whatever color you want. Place it behind your text and then group it with your text. -- JoAnn Paules MVP Microsoft [Publisher] "justin" <anonymous@discussions.microsoft.com> wrote in message news:17aa701c4496a$65af12f0$a501280a@phx.gbl... > I was told that although that there is no official > highlight button for publish...

highlight
Hi - just switched to Outlook 2003 from OE. I can't work out how to set up a rule where the message is a different color for a certain sender as I did in O! - can anyone tell me? TIA You can set flags on messages from certain senders. You can set the color of the font used in the list view using Conditional Formatting: View menu > Arrange by > Current View > Customize Current View > Automatic Formatting "Daskarzine" <Daskarzine@doesnotexist.com> wrote in message news:3fab5eb8@duster.adelaide.on.net... > Hi - just switched to Outlook 2003 from OE. I can&...

can't highlight cell
When I click on a cell, the contents of the cell are not highlighted. Instead the cell's row and column are highlighted. I can't work with the cell or it's formula itself. Help! Tools>Options, Edit tab, check Edit directly in cell -- Kind Regards, Niek Otten Microsoft MVP - Excel "MM" <MM@discussions.microsoft.com> wrote in message news:3883756F-29AC-482D-8205-F61892E8523D@microsoft.com... > When I click on a cell, the contents of the cell are not highlighted. > Instead the cell's row and column are highlighted. I can't work with the &...

how do I autofil a column with sequential numbers?
Put your starting number in a cell, then hold the CTRL key and formula-drag the cell over the required range (that is, highlight the cell, and click the small + in the bottom right corner of the highlight, and drag this whilst holding the CTRL key) Yesrek Wrote: > -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=21059 View this thread: http://www.excelforum.com/showthread.php?threadid=486582 Two methods...there are more. 1. Enter your start number i...

How to lock "hidden" Columns for good
Post Preview: Hi Friends I have been using Exel for a long time. I use exel heavily from time t time and this is one of those times. I am not an expert but I kno exel, the common stuff, pretty good I think. There is something tha always drives me crazy! in fact, I just finished talking to one of ou Exel help desk people and could not find an obvious solution to m problem that is why I coming to you. My problem is this: how do I LOCK the hidden columns from a particula sheet, so that when I need to Format/Row or Column/Autofit, the stupi "hidden" columns won't pop up back on t...

Text highlighting in a TreeList?
Using a TreeList in a dialog, is it possible to add some text highlighting to some of the lines, like changing the color, or making it bold? (There's no indication I can find in my Newcomer book that this is possible, but it's a 9 year old book.) Yes. Owner-draw/Custom-draw tree controls did not exist when I wrote that chapter of the book. Now you would handle the NM_CUSTOMDRAW notification and handle them otherwise like most owner-draw controls. joe On Wed, 21 Nov 2007 15:58:00 -0800, David Deley <deleyd@gte.net> wrote: >Using a TreeList in a dialog, is it possib...

Select column with highest value
Hi, How do I get the desired result for the following sample Table1 data: Source Table: Table1 Columns: Mukey State L11 L12 L21 L22 m657753 AL 8.00 0.00 5.00 1.00 m657753 TN 0.00 1.00 3.00 0.00 m657754 AL 1.00 6.00 2.00 27.00 M657754 TN 0.00 4.00 1.00 5.00 Desired result: Target Table: Table2 Columns: Mukey State LandUse ...

How to turn off highlighting??
After opening Excel (everytime) the first cell I select becomes an acho for a mass highlighted block. Regardless of where I take the cursor all of the cells within the range of travel become highlighted. Al soft keys are dead (so I can't activate help). Can not "ESC" out. Ca only Cntl-Alt-Del and close program from the task manager. Does anyone have an idea what I can do to turn this off -- rsbfit ----------------------------------------------------------------------- rsbfitz's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3530 View this threa...

Hide column in contious form
I have a continous form [BatchSummaryTotals] that's recordsource is a crosstab query [BatchSummaryTotals_Crosstab]. In datasheet view when I set the field Me.C42.visible = false the column is removed and the rest of the columns move left and remove blank space. Is there a way to do this on a continous form? I can set the visible property to false and it is removed, but it leaves a blank space as large as the column that was there. I am open to other ways to get my data in a row/column form, but I need header and footer sections for filters and labels. Please advise. Ryan Tisse...

Matching Data within Cells
First I am a newbie to this community but I am by no means a newbie to Excel. I have a need where I work to match two different columns of data as follows. Anyone that can help will be my savior. Problem to solve: Cell A2 contains a 16 digit number stored as text (Account Number) This is one of several such cells in column A Column E contains a list of Account numbers from another report that has been copied to the work sheet Solution required: search Column E for a match to Cell A2 and indicate that there is a match in some manner If possible could more than one cel...

highlights
Hi all Can you help me and tell me if this is at all possible using conditional formatting or a formula I4 is to equal to b4 times c4. If d4 is not the same as i4 then d4 needs to highlight in red. However if d4 is a negative number then d4 should remain normal, not highlighted. Thank You Shorty, =AND(D4>=0, D4<>I4) -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Shorty" <Mysteryjim@sbcglobal.net> wrote in message news:i6I2e.8230$zl.6378@newssvr13.news.prodigy.com... > Hi all > > Can ...

How can I alphabetize a column by last name?
I can't find the A-Z, Z-A button to group a column by last name. Publisher is unable to do this; you could build the list in Word, do the alphabetizing and then copy paste into Publisher. -- Don Vancouver, USA "RAD" <RAD@discussions.microsoft.com> wrote in message news:6A514B07-8355-4F6C-893B-90638BABA460@microsoft.com... >I can't find the A-Z, Z-A button to group a column by last name. ...

Clustered column chart with stacked coumns
Is there a way to have a clustered column chart that has stacked columns? Thanks Hi Dave, Check Jon's page for an example. http://peltiertech.com/Excel/ChartsHowTo/ClusterStack.html Cheers Andy Dave wrote: > Is there a way to have a clustered column chart that has stacked columns? > > Thanks -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info ...

highlight color using keyboard(shortcut)
Could you please tell me how to use keyboard (short cut) to highligh color for any cell or row in excell without moving a mouse to color ta in tool bar menue. Thank you for your help Vicki ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com Fairly long key sequence to do it through the menu, rather impractical... my suggestion is to record a marco and assign it a keyboard shortcut. Nikos Y.(nyannaco at in dot gr) >-----Original Message----- > >Could you please ...

my column headers has changed to numeric from alphabetcal order #2
I have come across strange problem ..my column names has chnaged to numeric from alphabet and now none of my formulaes work as all the cell names show up as R1C1 , R1C2 and so on... Anyone knows what have i messed up ??? Hi Annie, Try: Tools | Options | General Tab | Uncheck R1C1 reference style. --- Regards, Norman "Annie" <Annie@discussions.microsoft.com> wrote in message news:1FE6319D-59BD-4BEE-9C77-B8B97AC78CEF@microsoft.com... >I have come across strange problem ..my column names has chnaged to numeric > from alphabet and now none of my formulaes work ...

HOW DO I DELETE NUMBERS IN A COLUMN out of each cell
I have a list of part numbers in a column, over 5000, and I need to change it while keeping other information in tact. For example 1025-00-128-5463 (Each of these numbers are in a cell which totals 1025-00-255-5656 5000. They are listed in a column.) 1025-00-233-5666 I need to delete the first four numbers and all the dash marks so they will look like this 001285463 002555656 002335666 If I tried to do this individually I would waste a lot of time. Please help and remember I am new to this program. Any help would be sincerely appreciated Thanks Dave ...

Purchase Match Invoice Import
Is there an import or integration that someone has that will import invoices that are ready to be paid on cutover day? Terry ...

copy value from the cell above to the cell below in a column
I have a spreadsheet that was saved from a report. this report only lists the employee name once. There is a way to copy the value of the cell above to the cell below, all the way down the column at once. I can get as far as highlighting the blank cells with the Go To function, then I can not remember the steps to tell excel to copy the data into the blank cells. Ctrl-D or edit-fill-fill down, works one at a time. But there is a way of copying each employee's name to the blank cell below all at once, without doing it individually. can anyon help? thanks See answers to yo...

Conditionally highlight cells or ...
:confused: Hi, Could anyone help me with this problem? Here is th table: Apple Banana Orange Pear 15 45 20 335 85 65 44 77 11 787 88 936 I want to either to be able to 1)use a function to return the name o the fruit containing the lowest level in each raw, so for first row i would return "Apple", sedond row would return "Orange", etc. Can an of you help me writing this function? 2) or if possible, i'd like t highlight the cell in each row that contains the lowest value, so fo first row, cell containing 15 will be highlighted. Can anybody help m with that? Thank...

Getting exact cell / Range from the pie chart
By selecting data point on the pie chart, I want to get corresponding cell/range ( e.g E6 or E6:E8). I can get the values ( ActiveChart.SeriesCollection(1).Values), but can't seem to find how to get exact cell. Will appreciate your reply. Thanks Suyog Suyog - Excel doesn't make it easy. You can get the series formula, and parse it to extract the range of interest. John Walkenbach shows how to use a class module to do just this on his web site, http://j-walk.com. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://Peltie...