deduplicate data in excel

How do I deduplicate data in Excel. I have a large database of names and 
addresses which I want to check to duplication
0
Melbourne (3)
11/27/2005 11:49:02 PM
excel.misc 78881 articles. 5 followers. Follow

8 Replies
608 Views

Similar Articles

[PageSpeed] 39

You may want to read some of Chip Pearson's techniques for dealing with
duplicates:
http://www.cpearson.com/excel/duplicat.htm

Julie Melbourne wrote:
> 
> How do I deduplicate data in Excel. I have a large database of names and
> addresses which I want to check to duplication

-- 

Dave Peterson
0
petersod (12004)
11/28/2005 12:11:03 AM
Thanks Dave
I have looked at this site, but cannot get the formula to work? 
Must be doing something wrong.  Have you tried it?
Do you have a working spreadsheet wtih this formula?

Thanks for you help
Julie Melbourne

"Dave Peterson" wrote:

> You may want to read some of Chip Pearson's techniques for dealing with
> duplicates:
> http://www.cpearson.com/excel/duplicat.htm
> 
> Julie Melbourne wrote:
> > 
> > How do I deduplicate data in Excel. I have a large database of names and
> > addresses which I want to check to duplication
> 
> -- 
> 
> Dave Peterson
> 
0
11/28/2005 12:33:05 AM
Look at Data>Filter>Advanced filter, it has a unique option.

-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"Julie Melbourne" <Julie Melbourne@discussions.microsoft.com> wrote in
message news:B59F181F-9EF7-468C-A99D-2B7AD447F065@microsoft.com...
> How do I deduplicate data in Excel. I have a large database of names and
> addresses which I want to check to duplication


0
bob.phillips1 (6510)
11/28/2005 12:36:48 AM
What formula did you use?

Where's your data?

I've never had trouble with the formulas.

Julie Melbourne wrote:
> 
> Thanks Dave
> I have looked at this site, but cannot get the formula to work?
> Must be doing something wrong.  Have you tried it?
> Do you have a working spreadsheet wtih this formula?
> 
> Thanks for you help
> Julie Melbourne
> 
> "Dave Peterson" wrote:
> 
> > You may want to read some of Chip Pearson's techniques for dealing with
> > duplicates:
> > http://www.cpearson.com/excel/duplicat.htm
> >
> > Julie Melbourne wrote:
> > >
> > > How do I deduplicate data in Excel. I have a large database of names and
> > > addresses which I want to check to duplication
> >
> > --
> >
> > Dave Peterson
> >

-- 

Dave Peterson
0
petersod (12004)
11/28/2005 12:38:55 AM
Dave
I tried the following formula in a test worksheet using one column named 
Range with same data as displayed on the website

=IF(MAX(COUNTIF(Range1,Range1))>1,"Duplicates","No Duplicates")

Then I pressed CTRL+Shift+Enter
I get the following result
#NAME?

Any ideas what I am doing wrong?
Much appreciate your help

"Dave Peterson" wrote:

> What formula did you use?
> 
> Where's your data?
> 
> I've never had trouble with the formulas.
> 
> Julie Melbourne wrote:
> > 
> > Thanks Dave
> > I have looked at this site, but cannot get the formula to work?
> > Must be doing something wrong.  Have you tried it?
> > Do you have a working spreadsheet wtih this formula?
> > 
> > Thanks for you help
> > Julie Melbourne
> > 
> > "Dave Peterson" wrote:
> > 
> > > You may want to read some of Chip Pearson's techniques for dealing with
> > > duplicates:
> > > http://www.cpearson.com/excel/duplicat.htm
> > >
> > > Julie Melbourne wrote:
> > > >
> > > > How do I deduplicate data in Excel. I have a large database of names and
> > > > addresses which I want to check to duplication
> > >
> > > --
> > >
> > > Dave Peterson
> > >
> 
> -- 
> 
> Dave Peterson
> 
0
11/28/2005 12:53:01 AM
First, that's not the formula you want to use.  But if you just wanted to check
to see if all the entries were unique, you'd change range1 to A1:A100 (or some
other range).

I think the formula you want (if your data is in A1:Axx) is this:
=if(countif($a$1:a1,a1)=1,"First Occurrence","Duplicate")

Then the first occurrence is marked differently.

If you're looking for just any old duplicate.
=if(countif(a:a,a1)=1,"unique","duplicate")

And drag done the column.

Julie Melbourne wrote:
> 
> Dave
> I tried the following formula in a test worksheet using one column named
> Range with same data as displayed on the website
> 
> =IF(MAX(COUNTIF(Range1,Range1))>1,"Duplicates","No Duplicates")
> 
> Then I pressed CTRL+Shift+Enter
> I get the following result
> #NAME?
> 
> Any ideas what I am doing wrong?
> Much appreciate your help
> 
> "Dave Peterson" wrote:
> 
> > What formula did you use?
> >
> > Where's your data?
> >
> > I've never had trouble with the formulas.
> >
> > Julie Melbourne wrote:
> > >
> > > Thanks Dave
> > > I have looked at this site, but cannot get the formula to work?
> > > Must be doing something wrong.  Have you tried it?
> > > Do you have a working spreadsheet wtih this formula?
> > >
> > > Thanks for you help
> > > Julie Melbourne
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > You may want to read some of Chip Pearson's techniques for dealing with
> > > > duplicates:
> > > > http://www.cpearson.com/excel/duplicat.htm
> > > >
> > > > Julie Melbourne wrote:
> > > > >
> > > > > How do I deduplicate data in Excel. I have a large database of names and
> > > > > addresses which I want to check to duplication
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

-- 

Dave Peterson
0
petersod (12004)
11/28/2005 1:07:49 AM
Dave
Thanks so very much.  It works a beauty!
Much appreciated

Julie Melbourne

"Dave Peterson" wrote:

> First, that's not the formula you want to use.  But if you just wanted to check
> to see if all the entries were unique, you'd change range1 to A1:A100 (or some
> other range).
> 
> I think the formula you want (if your data is in A1:Axx) is this:
> =if(countif($a$1:a1,a1)=1,"First Occurrence","Duplicate")
> 
> Then the first occurrence is marked differently.
> 
> If you're looking for just any old duplicate.
> =if(countif(a:a,a1)=1,"unique","duplicate")
> 
> And drag done the column.
> 
> Julie Melbourne wrote:
> > 
> > Dave
> > I tried the following formula in a test worksheet using one column named
> > Range with same data as displayed on the website
> > 
> > =IF(MAX(COUNTIF(Range1,Range1))>1,"Duplicates","No Duplicates")
> > 
> > Then I pressed CTRL+Shift+Enter
> > I get the following result
> > #NAME?
> > 
> > Any ideas what I am doing wrong?
> > Much appreciate your help
> > 
> > "Dave Peterson" wrote:
> > 
> > > What formula did you use?
> > >
> > > Where's your data?
> > >
> > > I've never had trouble with the formulas.
> > >
> > > Julie Melbourne wrote:
> > > >
> > > > Thanks Dave
> > > > I have looked at this site, but cannot get the formula to work?
> > > > Must be doing something wrong.  Have you tried it?
> > > > Do you have a working spreadsheet wtih this formula?
> > > >
> > > > Thanks for you help
> > > > Julie Melbourne
> > > >
> > > > "Dave Peterson" wrote:
> > > >
> > > > > You may want to read some of Chip Pearson's techniques for dealing with
> > > > > duplicates:
> > > > > http://www.cpearson.com/excel/duplicat.htm
> > > > >
> > > > > Julie Melbourne wrote:
> > > > > >
> > > > > > How do I deduplicate data in Excel. I have a large database of names and
> > > > > > addresses which I want to check to duplication
> > > > >
> > > > > --
> > > > >
> > > > > Dave Peterson
> > > > >
> > >
> > > --
> > >
> > > Dave Peterson
> > >
> 
> -- 
> 
> Dave Peterson
> 
0
11/28/2005 1:34:02 AM
Glad you got it working.

Julie Melbourne wrote:
> 
> Dave
> Thanks so very much.  It works a beauty!
> Much appreciated
> 
> Julie Melbourne
> 
> "Dave Peterson" wrote:
> 
> > First, that's not the formula you want to use.  But if you just wanted to check
> > to see if all the entries were unique, you'd change range1 to A1:A100 (or some
> > other range).
> >
> > I think the formula you want (if your data is in A1:Axx) is this:
> > =if(countif($a$1:a1,a1)=1,"First Occurrence","Duplicate")
> >
> > Then the first occurrence is marked differently.
> >
> > If you're looking for just any old duplicate.
> > =if(countif(a:a,a1)=1,"unique","duplicate")
> >
> > And drag done the column.
> >
> > Julie Melbourne wrote:
> > >
> > > Dave
> > > I tried the following formula in a test worksheet using one column named
> > > Range with same data as displayed on the website
> > >
> > > =IF(MAX(COUNTIF(Range1,Range1))>1,"Duplicates","No Duplicates")
> > >
> > > Then I pressed CTRL+Shift+Enter
> > > I get the following result
> > > #NAME?
> > >
> > > Any ideas what I am doing wrong?
> > > Much appreciate your help
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > What formula did you use?
> > > >
> > > > Where's your data?
> > > >
> > > > I've never had trouble with the formulas.
> > > >
> > > > Julie Melbourne wrote:
> > > > >
> > > > > Thanks Dave
> > > > > I have looked at this site, but cannot get the formula to work?
> > > > > Must be doing something wrong.  Have you tried it?
> > > > > Do you have a working spreadsheet wtih this formula?
> > > > >
> > > > > Thanks for you help
> > > > > Julie Melbourne
> > > > >
> > > > > "Dave Peterson" wrote:
> > > > >
> > > > > > You may want to read some of Chip Pearson's techniques for dealing with
> > > > > > duplicates:
> > > > > > http://www.cpearson.com/excel/duplicat.htm
> > > > > >
> > > > > > Julie Melbourne wrote:
> > > > > > >
> > > > > > > How do I deduplicate data in Excel. I have a large database of names and
> > > > > > > addresses which I want to check to duplication
> > > > > >
> > > > > > --
> > > > > >
> > > > > > Dave Peterson
> > > > > >
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

-- 

Dave Peterson
0
petersod (12004)
11/28/2005 1:43:59 AM
Reply:

Similar Artilces:

deduplication
Trying to remove duplicates within one column can someone help If your Data is in Column A , beginning in Cell A2 enter the following fomula in B2 and Copy down =COUNTIF(A$2:A2,A2)>1 You will see True/False; All that are True are Duplicates Suggest you do an AutoFilter Selecting True; and while filtered, Delete all Selected Rows. Then Unfilter "scavvie" wrote: > Trying to remove duplicates within one column can someone help Excel 2003.............Data>Filter>Advanced Filter. Unique records only and copy to another location. Excel 2007..............

Office 2000 Excel SP-3: Nasty intermittent bug during save.
The bug occurs in the following circumstances (although not every time). 1. Two files are open, - a 'book4' type temporary file that I couldn't care less about. - a rather large 10 sheet workbook that I've been working on. 2. I try to save the large file - instead - the _temporary file_ is saved _over_ the large file on the disk. - an error message is displayed saying the file has been saved but that it is out of memory and had to close it. - the temporary file is displayed. 3. Generally the large file disappears from the task bar. 4. If the large file doesn't disppea...

Sort and sum data in a table
I have a table of names, with multiple entries of each name. Each name has 5 columns of data that needs to be summed up. If I use a pivot table, it does not allow me to have column A as the name, B the sum of column B, C the sum of C, etc. It forces the data into individual rows, which is not good. Is there a way to create a list of unique names, and then sum up each column of data for each of these names? Thanks in advance Tim, Perhaps you need to put the name header (column A) into the row area of the pivot table, and each of the B, C, etc., headers into the data area, for totals...

Excel report help
I did a quick search on this topic and to be honest found that I know s little about this topic, that I was lost trying to understand what saw. So to be honest, I am looking for someone to basically hand hol me through this problem. A coworker has a report that she files once a month, this report is culmination of four Excel reports into one. The four reports we ar combining consist of a beginning of month inventory, end of mont inventory, cost of inventory, and product ordered and shipped. The problem I am running into is that none of the reports she is abl to pull from our database has a...

labeling data points on a scatter chart
Hey guys. I need some help with my scatter diagram im using for a marketing assignment, about cars. So i have thee collums, the 1st is the car type e.g. "Mustang" and the 2nd is the torque e.g. "300" and lastly then the Price e.g. "20 000" What i want, is for each data point on the scatter chart to have the cars name next to it. Using the the e.g. data/figures above, i would have at the point (300, 20 000) the word "mustang" next to it. Help: How do i do this? Thanx guys!!! After plotting the X and Y values, you can use a third party label...

help with excel cells
HI there, basically i have a cell spread sheet, and i wanna have some help in this field i have a sheet with products and exipry dates(sheet 1), and the products expired (sheet 2). basically i wanna know how can i make the cell from sheet 1 when it expires , takes the whole raw for that specific product to sheet 2 thanks On 16 May, 22:50, hai <h...@discussions.microsoft.com> wrote: > HI there, > > basically i have a cell spread sheet, and i wanna have some help in this field > > i have a sheet with products and exipry dates(sheet 1), and the products > expired (s...

Data breaks in wrong place
Hello kind people, I am not an expert Access user, but am attempting to create a report for top management from a data base. The problem is that on several pages detail field headers are printing at the bottom of the page and the accompanying data at the top of the next. Experimenting with the Keep Together property does not seem to have a positive effect. Please help..... Paul ...

Link Data to Shapes not Displayed under Data Menu
I am trying to follow along with the practice part of the Visio 2007 How to link data to diagrams training session. However, when I go under the data menu, the 'Link Data to Shapes' is not listed. Any ideas? the data linking feature is in visio professional and not in visio standard, what version do you have? al "KT" <KT@discussions.microsoft.com> wrote in message news:7C92831D-C013-45DB-B402-1D287E341B3D@microsoft.com... > I am trying to follow along with the practice part of the Visio 2007 How > to > link data to diagrams training sessio...

find multiple data cells for one data source
I need a formula that will help me organize country codes. In Column A, I have 3 digit codes that have a country code, in column B, associated with it. The 3 digit codes appear multiple times in the spreadsheet. I need a way to show the 3 digit code and any and all of the country codes that are associated with it on one row. Example of my raw data below: ABF CN ABF MX ABF US ACC CN ACC IE ACC MX ACC TW ACC US Desired Output: Col A Col B Col C Col D Col E Col F ABF CN MX US ACC CN IE MX TW US Satisfactory out...

importing from Excel To Access
I have established an import button with the following code. It works fine for importing except, some of the header/column names in the Excel spreadsheets have been changed which causes an error in importing. Also, when importing I sometimes get blank rows in the database using code below. a few questions I have are: 1. Is there away for the import to programmatically change the column headings before import? 2. Is there vba or other means for the import to disregard the column headings and just import the data for that column? 3. Is there away to programmatically or other means to disr...

referencing filtered data
I have filtered a large table according to the values in the first column and I now want to refer to that filtered value in a formula at the bottom of the filtered data. If the filtered value is 2 I want to use 2 in a formula, and if I then filter on the value 3, I want the same formula to then pick up the value 3. Any ideas? I am using Office 2007. Thanks, Paul This array formula** will return the value of the first visible cell in the filtered (or unfiltered) range. =INDEX(A2:A15,MATCH(1,SUBTOTAL(3,OFFSET(A2:A15,,,ROW(A2:A15)-ROW(A2)+1)),0)) ** array formula...

where can I find a nurse staffing templatew for excel 2000
I need an excel template for excel 2000 that would allow me to staff nurses Try the Office Template Gallery. http://officeupdate.microsoft.com/templategallery/default.asp Gord Dibben MS Excel MVP On Mon, 17 Mar 2008 17:54:00 -0700, klif_e <klife@discussions.microsoft.com> wrote: >I need an excel template for excel 2000 that would allow me to staff nurses ...

Embedding Excel2007 Data Issue
Hi, Have trawled through the forum and couldn't find the answer so hopefully one of you clever people can help me :-) I have a user who is embedding a simple table from an Excel document into Word (he does this by copying the data from Excel, selecting Paste Special in Word and then choosing Microsoft Office Excel 2007 Workbook Object (with display as icon UNticked). This appears fine. Double click on the embedded object and the Excel toolbar spawns within Word so that's groovy. Save the document (locally or to the network) and when re-opening the Word document, ...

need to edit excel spreadshhet
I have a column in spreadsheet excel, I have 30,000 items and I want to drop the last line which is "Shipping charges wil apply to orders under $100.00" this line is in every cell as the last line of the description column. Is there a formula that will work? Hi Pete, A formula can return a value, it cannot delete or hide a row, for that you would need a macro (or conceivably a filter). I suspect that you are trying to remove this text, or the entire row from all sheets in a workbook, but that is not clear. Can you more clearly identify what you want to do, and we'll be in a ...

Excel file modification date
In Excel 2002 SP3 on Win XP, the workbook's file modification date changes to the current date and time as soon as the Excel workbook is opened, even if there are no changes applied to the file. If you close the workbook without changing any of its contents, the file modification date reverts back to the original value. Is there any way for the file modification date to remain unchanged until afte the Excel workbook has been modified? Does this behavior occur in newer versions of Excel? It has been that way for as long as I can remember and I don't think it's changed in XL20...

Freeze Excel Spreadsheet till template open first then spreadsheet
Hi, When I export the data from Access 2002 to excel spreadsheet after select excel templates. I couldn't open the spreadsheet until i open the template and then closed the template. I open the spreadsheet where i get the data from Access 2002. It works. Here is the area that I have trouble with. 'Hide warnings on the spreadsheet ExcellApplication.DisplayAlerts = False 'Save the workbook ExcellApplication.ActiveWorkbook.Save 'Turn spreadsheet warnings back on ExcellApplication.DisplayAlerts = True 'Make it visible ExcellApplication.V...

no memo field data after enter
I have a memo field on a form, where teammates enter continuous customer data. After each sentence, they hit the enter key, to seperate the notes in the field. The underlying table is only recording everything up to the time they hit the enter key the first time. So, I could have 20 sentences (entries) in the field, and only the first entry will show up in the table. Anyone know why, and is there a way to correct this? Thanks, Perry -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access/200706/1 Are you sure the other lines are not there? They won&...

Macro to pull every Nth row of data
I have a spreadsheet of data and I need to pull every 60th row out onto another sheet. Any simple macros? Thanks. Amy How about Sub Test() Source_Sheet = "Sheet1" Target_Sheet = "Sheet2" n = 5000 ' your last line of data on Sheet1 Target_Row = 1 Sheets(Target_Sheet).Select For nCount = 1 To n Step 60 Worksheets(Source_Sheet).Cells(nCount, 1).EntireRow.Copy Worksheets(Target_Sheet).Cells(Target_Row, 1).Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ F...

Copying data
Hi How do I set up a drop down list (Sales April, May, June ect) to copy data from 1 column to another in a specified location? Thanks Andre You can use and Advanced Filter to copy data from one location to another. There are instructions in Excel's Help, and here: http://www.contextures.com/xladvfilter01.html Also, there are sample files here that use Data Validation to create dropdown lists, and Advanced Filter to extract data: http://www.contextures.com/excelfiles.html Under Filters, look for 'Product List by Category ' Andre wrote: > How do I set u...

Saving a RichEditCtrl data
Is it possible to save the data to an access ole object field? How would I do that? ...

Excel: Name Range Values Reset After Refresh Data + ReCalc
Whenever I refresh data (Data->Refresh Data) the name range values are reset from, e.g., $A$1:$A$65534 to $A:$A, and then the array formulas in which the name is used won't recalc until I manually fix the values. Any idea how to stop these from changing? ...

Using OleDB to get data from Excel
Hi people! I'm using OleDbDataReader to get rows one-by-one from Excel sheet. The code I'm using to do this: oConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + name + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1;\""); oConn.Open(); OleDbCommand oCmd = new OleDbCommand("SELECT * FROM [Sheet1$]", oConn); OleDbDataReader oReader = oCmd.ExecuteReader(); while (oReader.Read()) { //array to store current row object[] rowValues = new object[oReader.FieldCount]; oReader.GetValues(rowValues); ...

Outlook2002 requesting data from server
Hello everybody, We recently upgraded to Exchange2K and things are going fairly smoothly. We have one user, however, who is running Outlook2002 (rather than our standard 2000). On occasion he gets a message that says that Oulook is requesting data from the server. During this period he is unable to work in Outlook. This occurs whether he's working in Outlook or not (but is is open). I looked through his settings and don't see anything suspicious. Any ideas what's going on in this instance. If this is a well known issue, I apologize in advance. Thanks. This is a p...

How to convert contents of column from numeric data type to text
you know how sometimes when you paste a value into a cell it reads like a number but Excel thinks its text and aligns it to the left and gives you a little dropdown menu with options such as keep this value as text or to convert it to numeric data. I have a whole column of mostly numbers (and excel is storing them as numeric data) and want to convert them to text. I don't have that menu option available since excel thinks they're numbers. Is there a way to convert numeric data to text? Thanks. -- moondaddy@noemail.noemail If you don't mind a helper column, then you c...

Linked Excel object in Word
I'm linking an Excel spreadsheet into a Word document (Office 97 o XP)as an icon so that when I double click on it within Word it open the spreadsheet at a particular worksheet. The method for doing thi according to Help is to open both, select the data within Excel, clic copy, move to the Word document and Paste Special using Paste as Lin and click Display Icon. All well and good, except when the embedded object opens, the selectio is still outlined by the migraine-inducing, flashing dotted lines. How do I get rid of these so that someone opening the link isn't face with them? H...