#### Finding duplicates in column

```Hi,

I have Excel 2002 and have 6000 emails addresses in a column.

How can I find if there are any duplicates in that column?

Thanks

rock
```
 0
rock (31)
8/21/2009 7:57:52 AM
excel.newusers 15348 articles. 2 followers.

4 Replies
357 Views

Similar Articles

[PageSpeed] 20

```Assuming the data is in column A, add a formula in column B of

=IF(COUNTIF(\$A\$1:\$A1,\$A1)>1,"Duplicate","")

Cpy that down, then you can filter column B for Duplicate

```
 0
BobNGs (423)
8/21/2009 8:10:00 AM
```
Sorry to be so newby Bob, but when you say 'copy' down, what exactly do
you mean?

I have entered the formula in B1 but..

Thanks

rock

Bob Phillips wrote:
> Assuming the data is in column A, add a formula in column B of
>
> =IF(COUNTIF(\$A\$1:\$A1,\$A1)>1,"Duplicate","")
>
> Cpy that down, then you can filter column B for Duplicate
>
>
```
 0
rock (31)
8/21/2009 10:52:51 AM
```Hi,
if you have 6000 rows what he means is to copy the formula from B1 all the
way down to B6000. How you do that two ways

1 select the cell where the formula is, right click on the mouse, copy, then
go one cell down, highlight from there to the last row where you see
information in column A, right click on the mouse, paste

2 having the cell with the formula highlighted, move the mouse to the right
bottom corner untill you see a bold cross like this  +, when you see it
double click in the mouse, it will copy the formula down up to it find a

"rock" wrote:

>
>
> Sorry to be so newby Bob, but when you say 'copy' down, what exactly do
> you mean?
>
> I have entered the formula in B1 but..
>
>
> Thanks
>
>
> rock
>
>
>
>
>
> Bob Phillips wrote:
> > Assuming the data is in column A, add a formula in column B of
> >
> > =IF(COUNTIF(\$A\$1:\$A1,\$A1)>1,"Duplicate","")
> >
> > Cpy that down, then you can filter column B for Duplicate
> >
> >
>
```
 0
Eduardo (134)
8/21/2009 11:23:01 AM
```Hi Rock,

Another way is to use Conditional Formatting under Format.  Select the 6000
cells in the column and then Format > Conditional Formatting > click the
down arrow for Formula Is > enter =COUNTIF(\$A\$1:\$A\$10,A1)>1 > click Format >
Patterns > select a color > OK your way out.

Any duplicates will be highlighted in that color.

HTH
Regards,
Howard

"rock" <1940@pobox.com> wrote in message
news:uKUsWUjIKHA.3928@TK2MSFTNGP04.phx.gbl...
> Hi,
>
> I have Excel 2002 and have 6000 emails addresses in a column.
>
> How can I find if there are any duplicates in that column?
>
> Thanks
>
>
> rock

```
 0
lhkittle (223)
8/21/2009 7:32:06 PM

Similar Artilces:

Sharing violation on Excel files and cannot find the path specifie
W2003 Storage server in a Microsoft cluster with storage on a Compellent SAN. Problems with Excel files. Lot of corrupted Excel files. Excel files with sharing violation. If I want to delete a file I get the message â€ścannot find the path specified". Files are not in use. These problems occur only with Excel files opened with MS Excel. If Open office is used there is no sharing violation. Single Instance Storage is activated. Problems disappear after a while and occur on other Excel files. Is someone familiar with these problems? Thanks in advance. Will ...

Last used Column
I have been successfully using the function below since Jim Tomlinson posted it in 2006. Recently under certain circumstances it has returned an "invalid" value. In a case in point: sh has 19 columns and 70 rows Cells 1 to 19 of Row 1 have been merged 25 rows have been filtered out using autofilter (filtered col = 19) If I leave the filtering in place the function is returning 1 If I remove the filtering, it correctly returns 19. Can anyone explain why and whether there are other circumstances in which it might behave unexpectedly? Function fnLastCol(sh As Work...

Budget duplication
I have two items that appear to be duplicated in my budget. When I click the budget icon from the top menu, it pulls up one version, in which the two items have the amounts doubled. When I click on "edit budget," the amounts appear correctly. These are two transactions I attempted to download from my bank, and I cannot find them anywhere to delete them. Thanks for any help. This is a bug in money that to my knowledge has never been corrected. I've been using MS Money now for several years and the budget interface appears to have several glitches, this being one of them. YO...

Can't find directory
I went to my Outlook 2000 Advanced find, and looked for some messages. I found some matches, and saw what folder they were in - but I can't find the folder anywhere! The folder name starts with an S, so I kept hitting S, cycling through all folders starting with S, but can't find the folder containing this (and similar) messages. How many folders do you have then? Expand them all to find it. Folders are meant for increasing structure not for loosing messages ;-) You might want to review your folder list -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: ...

insert column move object
Hi I ma trying ot insert a column in excel and get Can not shift objects off sheet - What do I do? use ctrl+end to see the last row/column. Then end+up arrow. Delete un-needed rows>SAVE>try again. -- Don Guillett SalesAid Software donaldb@281.com "markg" <markg@discussions.microsoft.com> wrote in message news:8F76F80C-E9A2-446A-9427-C0B526AA7751@microsoft.com... > Hi I ma trying ot insert a column in excel and get Can not shift objects off > sheet - What do I do? Check this article http://support.microsoft.com?kbid=170081 XL97: "Cannot Shift Objects Off...

Recievind duplicates messages in Inbox
Hi I'm using outlook 2000 w SPK3 installed. I do see duplicated messages in my inbox every time i receive any messages. How can I fix this problem? Thanks. Sort of related ... I am using OL2003, I run WinXP, I have all the whizbang bits etc, but this error, if it;s an error is befuddling me. I am NOT using a POP3 account, only http mail. Occassionally (which makes it more diff to track down) when I send an email, the system appears to work as it was programmed to, yaaay! ... but ... sometiems I find that not only does the person I send to get my email, but I do to. I have c...

Subtotal by two columns at once
Hi, I need to subtotal two separate columns at the same time in Excel. I have item numbers in one column and the corresponding quantity of each item in another column. Each item has many different rows. Using the Subtotals command under Data, I am only able to subtotal by the item number and then I have to go in manually and sum the quantities of each subtotaled item. Is there a way using Subtotals or something else to subtotal by item number and then sum up the quantity column related to each item number at the same time? Thank you, Katherine Repeat the Subtotals command on the sam...

URGENT
17th Street is both "th" regular text and "th" superscript in a document. How to get it consistent, either way in a document. 2007 -- Janet A. Add the autoformat command from the all commands group to the QAT (Quick Access Toolbar) then run that command. From the options set only the Ordinals with Superscript and run the autoformat. -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org...

Hidden Column A Problem
I read the message sent in by ebf kentucky about having his column A hidden in Excel and the solution of unfreezing the frames did not work for m y client. Client is using Office 2007 Excel. It doesn't specify in his email which version he is using so maybe there is something different for 2007. The strange part is when the screen is set to full - the A column appears but when you get the menu bar/ribbon back, the A disappears. Any assistance would be greatly appreciated. Select columns to the left and right of you want to unhide. Go to Home, Cells, Format, Hide & Unhi...

Subtracting one column from another
Hi, I have two columns of data and I want to remove any names that appear in another column. For example: Column 1: Bill Henry Ruby Column 2: Frank Bill Joe I want the calculation to subtract any names that appear in Column 2 to produce the following: Henry Ruby Please let me know what I have to do, possibly with an SQL statement, to produce this including naming worksheets if thats necessary. - Brian Hi Brian, See Chip Pearson's Duplicates page at: http://www.cpearson.com/excel/duplicat.htm#HighlightingDuplicates See particularly, the section entitled: 'Extract...

Stacked Columns
Using Excel 2003. Have five stacked columns. Want to change order of only one stack; the only one that has more than two values in its stack. Was able to shift order of one of the two stack columns but the one with three values doesn't give me the same screen that permitted me to move the order up or down. Disregard! Found it! "jaygreg" <jaygreg90@hotmail.com> wrote in message news:e\$eAOUTzHHA.1164@TK2MSFTNGP02.phx.gbl... > Using Excel 2003. Have five stacked columns. Want to change order of only > one stack; the only one that has more than two values in it...

ScsiPortValidateRange(): to find the type of memory
Hi, i have device whose pci config space has bar0 is i/o mapped. now if i am calling ScsiPortValidateRange( , , , , TRUE ) or FALSE in both cases it returns success. does it mean that the parameter "BOOLEAN InIoSpace"is optional. is there anyother way using i can find exactely which kind of address it is, whether memory mapped or io mapped? regards, Hitesh On Mar 31, 10:00=A0pm, hitesh <hitesh.ughr...@gmail.com> wrote: > Hi, > i have device whose pci config space has bar0 is i/o mapped. > now if i am calling ScsiPortValidateRange( , , , , TRUE ) or FALSE...

How do I check which cells in column are date format?
Hi A few weeks ago I located the answer to this but now I can't find it anywhere. I have a formula that adds 10 years to a date in the cell next to it. My problem is, if one of the dates is not formatted correctly, the formula returns an error. I am sure all I had to do was something along the lines of =(CELL)ISDATE or something like that. It just said TRUE or FALSE........ I may be miles wrong here but I hope someone can help. I just need to see if all the cells in the column are formatted correctly. Kind regards Malcolm Malcolm. A quick look at the CELL function in help wou...

duplicate checks appearing in Checkbook Register
Hello: A client created a new checkbook this month. I noticed that most of the month's checks in the Checkbook Register Inquiry are duplicated. Literally, the same record appears mulitple times. The client is not reporting any issues in GP, though they do use an integration in Integration Manager that I think is a direct to table integration. Does this sort of integration override the standard logic of not allowing for duplicate records in tables? Thanks! childofthe1980s Direct to table integration will override any business logic in the GP application. So the only thing you...

hide column
unable to hide a column. when I try to hide a column I get a message "cannot shift objects off sheet". pl advise what should I do to hide that particular column. Thanks Hi Right Click on the column header (where the column letters are )of the column you want to hide. Select Hide. Regards Michael >-----Original Message----- >unable to hide a column. when I try to hide a column I get a message "cannot >shift objects off sheet". pl advise what should I do to hide that particular >column. >Thanks >. > Maybe try this extract of a recent post by ...

Error when summing columns
I have a spreadsheet with groups of numbers in columns. Sometimes when i sum the column, Excel rounds the numbers up. For example, I have the following forumula: Value Annual Fee Quarterly Fee 13,311.83 199.68 49.92 1318.78 Answered in your other post I assume. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL2K & XLXP ---------------------------------------------------------------------------- There's no 'I' in 'Team'...

How do I find propogate in Publisher? #2
I am new to using Publisher Office 2003. I've just created 15 labels on a page of 30. How do I propogate/copy these first 15 names so that I fill the entire page and do not waste any labels?? ...

Finding Unmatched Rows
I am trying to create a find unmatched query in access 2000. I have a table that has values for months 1-12. I want the query to look at the value in one table that I have an compare them to the values table that I created. If the value don't match up then I want a query created which shows those records. I am having a lot of trouble figuring this out. Any input would be greatly appreciated. -Anthony Morano Pension Intern So does your table structure have a column for each month? Or do you have a table structure where the month is identified in ONE field and the Value associated ...

data > text to columns
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Hello, <br> When I want to import data in excel from another file, I have to use the function text to columns. in Pop up sceen 2/3 I can't insert the vertical &quot;dash&quot;, as I could wiht my windows-PC (Keyboard Alt GR &quot;1&quot;). <br> This sign is not on my Apple keyboard, and the paste/copy seems not to work in this pop-up screen. <br> How can I fill this sign in this screen 2/3 of this text-to-column function? <br> Thanks in advance! <br> Bart ...

I would like to freeze the first column and the header line. How do I do this. I can freeze either one but not at the same time. Does anyone have any ideas, or is this impossible. Hi - move the cursor to cell B2 - goto 'Windows - Freeze Panes' It will freeze all rows above the current cursor position and all columns left to this position -- Regards Frank Kabel Frankfurt, Germany Wendj421 wrote: > I would like to freeze the first column and the header line. How do > I do this. I can freeze either one but not at the same time. Does > anyone have any ideas, or is this ...

Keeping Validation while inserting new columns
Anybody know how I can keep my data validation on a column if I insert another column before it? Thanks much-JL Hi the validation should stay. What happens exactly in your case and what kind of data validation have you used? -- Regards Frank Kabel Frankfurt, Germany Jim Lee wrote: > Anybody know how I can keep my data validation on a column if I insert > another column before it? Thanks much-JL ...

Automatic column width increase unwanted
Is it possible to avoid (turn off) the automatic increase of column width that occurs to make room for certain numbers, e.g. when the number of decimal places is increased beyond what can be displayed with the original width? Excel 2000 Did you try adjusting the width manually? As soon as I did that, excel wouldn't resize the column on its own. (same response as before) dOinK wrote: > > Is it possible to avoid (turn off) the automatic increase of column width > that occurs to make room for certain numbers, e.g. when the number of > decimal places is increased beyond wha...

I want to type a paragragh that would include other columns
I am new at this. I am trying to paste a aparagragh into excel which will be at the bottom of a form I am making. I am trying to make it in its own box but it seems it wants to stay in the column that it is pasted into. I would appreciate any Help. Thank-you B.W. Use a text box from the drawing toolbar -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Nothwest Excel Solutions www.nwexcelsolutions.com "It is a good thing to follow the first law of holes; if you are in one stop digging." Lord Healey "B.W." <B.W.@discussions.microsoft.com> wrote in message new...

kicking off a prompt box requiring input when any cell in column C is keyed in
This is a multi-part message in MIME format. ------=_NextPart_000_0015_01C402D8.C91DEE70 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I'm exploring the possibility of having a prompt box pop up when any = cell has data keyed in it in column C. For instance if Column C was requiring and answer such as Yes/No and = "yes" was keyed in (or selected from a drop down box), how could I bring = up a box that would force the entering on required data in the adjacent = columns D:F?=20 TIA, AlanN ------=_NextPart_000_0015_01C402D8....

Update of calculated columns in imported files?
I have got this problem which would be easy to solve (I think) with any common statistics software, but which also should be possible to achieve in Excel. My own efforts have not been enough though. This is the baseline: *One file is used for data entry, a kind of questionnaire data. *The data is imported into another file for making reports, had plans to use Pivot tables. *In this report file, besides the imported data there also has to be a number of calculations made on the imported columns (week number, changing Yes/No to 1/0, transforming answers like 1:s or 2:s to 0:s and...