Find and mark duplicates

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.
0
maxtrixx (11)
5/2/2005 6:09:37 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
342 Views

Similar Articles

[PageSpeed] 55

=if(a1=b1,"X","")

"maxtrixx" <maxtrixx@discussions.microsoft.com> wrote in message 
news:139BC711-FAAC-4CD2-A533-CE49BA77291F@microsoft.com...
> 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. 


0
nh1 (51)
5/2/2005 6:11:23 PM
The problem is, I don't know which two cells to compare, I need to know if 
any of the 800 items in the first colum exist in the 600 items of the second 
column.  It's not a row by row comparison.  Thanks.

"N Harkawat" wrote:

> =if(a1=b1,"X","")
> 
> "maxtrixx" <maxtrixx@discussions.microsoft.com> wrote in message 
> news:139BC711-FAAC-4CD2-A533-CE49BA77291F@microsoft.com...
> > 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. 
> 
> 
> 
0
maxtrixx (11)
5/5/2005 12:50:05 PM
hi ...maxtrixx...
You are correct that answer will not work,  I suggest you
look at Chip Pearson's page on duplicates.
   http://www.cpearson.com/excel/duplicat.htm#TaggingDuplicates

But here is the formula, which you can fill down with the fill handle.
    C2: =IF(COUNTIF(B:B,A2),"x","")      -- column A has a duplicate
    D2: =IF(COUNTIF(A:A,B2),"x","")      -- column B has a duplicate
---
HTH,
David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:        http://www.mvps.org/dmcritchie/excel/search.htm

"N Harkawat" <nh@nh.com> wrote in message news:uuviKK0TFHA.3544@TK2MSFTNGP12.phx.gbl...
> =if(a1=b1,"X","")
>
> "maxtrixx" <maxtrixx@discussions.microsoft.com> wrote in message
> news:139BC711-FAAC-4CD2-A533-CE49BA77291F@microsoft.com...
> > 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.
>
>


0
dmcritchie (2586)
5/6/2005 1:13:00 AM
Reply:

Similar Artilces:

How can I find career change resumes templates?
I like to review some nice "Professional Career Change Resume Templates" for the job market. If someone could tell me where to go on the inter-net, it will be well appreciated. Thanks! You could start at www.google.com "Mike" <Mike@discussions.microsoft.com> wrote in message news:705F99E5-1326-4E81-B98F-A7337C5C9AEA@microsoft.com... :I like to review some nice "Professional Career Change Resume Templates" for : the job market. If someone could tell me where to go on the inter-net, it : will be well appreciated. Thanks! Templates are...

Find and replace 03-04-10
I'm trying to scan a field in one of my tables and find a specific character and remove it. However, the character is a " so I'm having difficulty. The field I speak of of contains the sizes of our material so the values look like this: 1/4" 1/2" 1/3" and so on. How can I find all of the " in my feild and remove them? I don't want to replace them I just want to remove them. Thanks, Chris Savedge Create a query, and in the Criteria row under the problem field, enter: Like "*[""]*" -- Allen Browne - ...

Where can I find a list of known CRM 1.2 issues and workarounds?
I am running a test of CRM 1.2 from MSDN Universal and I am having a number of minor strange issues. Is there a list of known problems, and workarounds posted anywhere? Have any patches been issued or other technical update articles? Is there some sort of monitored support newsgroup or something I should purchase to be able to more effectively evaluate the product? Thanks, Bill Walter This is the only public newsgroup on CRM apart from foreign language versions. For support you would need an agreement with Microsoft MBS. Feel free to ask any technical questions here as we normally can get...

Lookup/Find help
Windows XP Professional Office 2000 Hypothetical, but hopefully you'll get the gist of it: I have two worksheets. On worksheet #1, I have two columns. First column is a list of entire workgroup by name and 2nd column is the hours worked. On the second worksheet I simply have an list of names that is a subgroup of those on the first page. These indicate a target group. EXAMPLE Worksheet #1 Sam 35 Joe 37 Mary 20 Beth 41 Ted 38 Worksheet #2 Joe Beth Now, on the first worksheet, I want to add a third column for summing only the target workgroup. Basically, I need a function...

can't find normal.dot
Version: 2004 Operating System: Mac OS X 10.4 (Tiger) Processor: Power PC Hello, I need to delete my normal.dot file and I can't find it anywhere. I've had to do this before and I was able to find it in the templates folder, but I guess an update or two has changed that. Can anyone tell me where to find it? Spotlight says that it doesn't exist. But I have to be able to delete it. Is it called something different now? If you're using Spotlight & searching for "Normal.dot" it's no wonder it isn't being found - that isn't the correct name :-) it's ...

Finding maximum value
Hello, I want to find the maximum value in a column, but I want to ignore the negative and positive part of the value. Basically, the maximum difference from zero. Example: the maximum value I am looking for will be -0.467. -0.467 0.345 -0.253 0.411 Thanks Ruan One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN): =INDEX(A1:A4,MATCH(MAX(ABS(A1:A4)),ABS(A1:A4),FALSE)) In article <OGKhnF1KEHA.2100@TK2MSFTNGP10.phx.gbl>, "Ruan" <ruan@aegismed.com> wrote: > Hello, > > I want to find the maximum value in a column, but I want to ignore the > negati...

How do I find the inside page in the 3-panel brochure.
I am trying to make a 3-panel brochure and all that seems to be available is the side shown in the brochure options. When I print, it prints both sides yet I can see no way to access the inside part. Thanks. View, status bar... this will allow you to change page views, you will see the page tabs at the bottom of the screen. I am not sure I know what you are asking. Are you saying you are printing the brochure sight unseen? -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "springmtn" <springmtn@discussions.microsoft.co...

Find a multiple photos on a page template for Publisher?
Possible to find a template for Publisher to print a page full of multiple photos without recreating the page each time I print. Why not save your page you have created as a template? File, "save as", files of type, scroll down to Publisher template. -- Mary Sauer http://msauer.mvps.org/ "srfaith" <srfaith@discussions.microsoft.com> wrote in message news:157E95AB-1159-4889-B196-61E147E6E240@microsoft.com... > Possible to find a template for Publisher to print a page full of multiple > photos without recreating the page each time I print. ...

Finding Desktop region in MFC
I am working on a commercial application, and recently discovered a bug I have been asked to fix (they threw it back in my lap). Run the application on a 2-monitor system, such as a laptop in a dock with an external monitor. Drag the main window to the second (non-taskbar) window and quit. Now move the laptop to a different dock with the second menu on the other side (or without a second monitor at all) and launch the app. Oops. The app continues to want to go on the side it was last put, even though there is now no monitor there. (Yes, my dock in the office and at home have the second ...

Performance counter for Outlook Finds?
What server performance counters (if any) are there for monitoring the number of "Find" requests that Outlook clients make? -GT ...

finding calendar and data info
I had to move my primary hard drive into another computer. It is now running as a slavein the new computer. I'm unable however, to find my calendar and contacts from Outlook 2003 when I open the program on the new computer. If I attempt to open the program from the F drive (the drive assigned in the new computer to the hard drive that was moved from another computer) it tells me the program is not installed. If I open Outlook 2003 from the C drive of the new computer, of course, it doesn't find the old calendar and contacts. I would appreciate any and all assistance in findin...

Conditional Formatting VBA with formula to find string
Hi, Please can I get some help.... I need to create a conditional format VBA in Excel 2007 (because I have many conditions to include) and I don't know how to do it... The Action Required: If Product A appears in any text string in range($C$95:$C$300) then colour that cell RED, if Product B appears anywhere in a text string within range($C$95:$C$300) then colour that cell BLUE, if Product B appears anywhere in a text string within range($C$95:$C$300) then colour that cell GREEN, and so on through 41 products... Data - I have a list of about 41 Product names in range Z...

Using sdk to find out if an Account already exists
Hi, Want to use sdk to see if an Account already exists , all i have is its name. I've looked at Retrieve method but it requires the Guid. Any equivalent call to Retrieve that just takes name? Or a way to get the Guid from the name? Sample code would be great if available. Thanks for all help. John Hi John, The method you are looking for is RetrieveMultiple as only the guid search gives the certainty to retrieve only one record. All explanations are in the sdk but as the sdk sample is nearly the exact code you want, here it is : // Set up the CRM Service. CrmService service =3D new ...

Unremovable Duplicate Personal & Archive Folders
I have two Archive Folders and two Personal Folders showing in the folders list of Outlook 2001. I have uninstalled Office Outlook twice, gone to RegEdit to remove any stray Outlook registries and reinstalled Office Outlook. I still have duplicate folders along with all my original folders. I have tried the procedures recommended in MS FAQ but a message comes up sasying I cannot "Close the folders" because an "OBJECT" cannot be found. I have also tried renaming the folders but am not allowed to do so. I have backed up all folders in a .pst file so all I want to do ...

Doesn't find files w/spaces in name
There are a lot of folders and Excel spreadsheets on our shared drive that I need to open. Most of the folders and file names are named like: get reports\reports sep 2004.xls When I try to open one, Excel doesn't find it and I think it's because there are spaces in the folder names and/or file names. Is there a setting in Excel that will help me get around this? This was working until I had a glitch in Excel and the technician fixed the glitch and this problem appeared. Any help would be appreciated. I found an answer and so far it works. "C:/program files/microsoft off...

SQL statement to find a particular column within all tables
I am looking for a query that will allow me to find all instances of a particular column within all tables so I know where they are all located. Does anyone have such a query they would be willing to share? Thank you. Pam, I posted this query a few months aback on my blog (http://dynamicsgpblogster.blogspot.com/2008/03/in-past-days-i-have-found-lot-of-people.html), but here is the excerpt: select distinct rtrim(objs.name) from syscolumns cols inner join sysobjects objs on (cols.id = objs.id) inner join sysindexes indx on (cols.id = indx.id) where (cols.name = 'ACTINDX') and (ob...

Find number of weekdays and wekend days given a total number of da
Hello, I have a question for the gurus here. I am working on a spreadsheet where in column A I have Total # of Vacation days. Example: # Vac Days --- # Weekdays --- # Weekend days 75 55 20 44 32 12 25 19 6 I figured this out by simply creating a list like this 1 Weekday 2 Weekday 3 Weekday 4 Weekday 5 Weekday 6 Weekend 7 Weekend And so on... And then doing a countif to figure out how many of either on...

Process Duplicates
Hi everyone, I have duplicate data and I need to process it accordingly. I followed some advice I found in this group and went to: http://www.cpearson.com/excel/topic.htm There I found the following code (reproduced here exactly) which looked like it could be modified to do the job I require: Sub FixDuplicateRows() Dim RowNdx As Long Dim ColNum As Integer ColNum = Selection(1).Column For RowNdx = Selection(Selection.Cells.Count).Row To _ Selection(1).Row + 1 Step -1 If Cells(RowNdx, ColNum).Value = Cells(RowNdx - 1, ColNum).Value Then Cells(RowNdx, ColNum).Value = "...

find
How can I setup a macro or use a function to find a column who's first cell is called "Addresses" then move the whole column to sheet 2. Is it possible? Puzzled J, If there is only one cell with the word "Addresses" in it: Sub MoveAddresses() Cells.Find("Addresses").EntireColumn.Cut Worksheets("Sheet 2").Range("IV1").End(xlToLeft)(1,2) End Sub This will put the column into the column with the first free cell in row 1 of Sheet 2. HTH, Bernie MS Excel MVP "J Bates" <newss@pittam.plus.com> wrote in message news:4507...

Duplicate (Ghost) Folders in Contacts
After a major crash of our company's Exchange Server I now have multiple contact folders with the same name when I go to email a message and go to the address books to find the name. They do not appear in the Contacts only when I click on TO: in an email message. For instance I have a folder called Distribution, there are two of these folders displayed. One works and the other when you click on it puts up an error ("The address list could not be displayed. The Contacts folder associated with this address list could not be opened; it may have been moved or deleted, or you do not ha...

Finding/deleting macros...
Thanks to anyone who helped me with my finding/deleting link question... Now in a different workbook.. when i open it, it prompts whether t enable or disable macros. When i go to the macros list though, it blank, none listed. How could I find where thesee phantom macros ar and get rid of them? FYI we always choose Disable macros. This is work book that was built a long time ago, probably has about 50+ tab of which we only currently use about 15. Thanks for any help! Chri -- Message posted from http://www.ExcelForum.com Hi you also have to remove the modules: - open the VBA editor (hi...

In Excel how do I find unformatted shapes that are invisible?
I am sent an Excel spreadsheet each week that I copy and paste and add to a main worksheet. The size of the file is getting huge. I discovered the reason for the size is the many unformatted shapes in this file. I would like to delete them. The problem is because they are unformatted, they are invisible. I only know they are there because I happened upon them accidently. Is there way to make all these visible so I can delete them one at a time? Or a way to delete all of them all at once? Hi Not sure if I understand but try this : in Excel 2003 or older... Go to Edit > ...

Mark as read
I am using outlook 2003 on winxp pro machine I have some vba coding that I use to strip the attachments off my email and place them into a specific folder. This is running fine, but I would like to fine-tune it a bit. Not sure how to do it and cannot find anything in my 2003 outlook vba book or when I google it, so here I am again. Here is my question Can I mark these emails as having been read during/after I have stripped the attachment off of them. They have never been clicked on so they never get marked as having been read which can become a bit confusing when the number of e...

Preventing Duplicate Entries within a column
Is there an easy way to prevent creating duplicate entries within a given column in Excel? Alternately....is there a way to automatcially highlight a cell / row when a duplicate entry is created within a given column? If they are typed in yes it is easy http://www.cpearson.com/excel/NoDupEntry.htm or to tag them http://www.cpearson.com/excel/duplicat.htm Regards, Peo Sjoblom "Bruce" wrote: > Is there an easy way to prevent creating duplicate > entries within a given column in Excel? > Alternately....is there a way to automatcially highlight > a cell ...

Find latest date from list and corresponding info
Hi, I have a list like the below and am trying to: A) Find most recent date of each color group (ColC) B) Then, from that date, find corresponding number code (ColA) ColA ColB ColC 1001 9/1/10 Red 1002 4/1/05 Red 1003 8/1/09 Blue 1004 9/1/08 Blue 1005 1/1/10 Blue Thanks for your help! Try these... Data in the range A2:C6 E2 = Red Formula in F2 array entered** for the max date: =MAX(IF(C2:C6=E2,B2:B6)) Formula in G2 array entered** for the code: =INDEX(A2:A6,MATCH(1,IF(C2:C6=E2,IF(B2:B6=F2,1)),0)) ** array formulas need to be entered using t...