How to combine Excel 2002 files and remove duplicate records?

I’ve recently took over a task at my job of managing some Excel files.
I am using Excel 2002. There are several Excel files that need to be 
combined into one file however, if I do this, I will have some duplicate 
records that I will have to delete.
The only way I know how to do this is to copy and paste and then look for 
duplicate records manually and delete them as I find them. Is there an easier 
way to do this?
Thank you for you support and help.
0
Dave542 (2)
4/5/2006 1:53:01 AM
excel.newusers 15348 articles. 2 followers. Follow

4 Replies
534 Views

Similar Articles

[PageSpeed] 2

Yes, there is.  I would use VBA to do that job, but I need more info.  When 
you say there are duplicate records, exactly what is duplicated?  In other 
words, if you are looking for duplicate records manually, what do you look 
for?  Do you look at just one column and if that one column has a duplicate, 
delete one of those rows?  Or do you have multiple columns and you have to 
check for a duplicate in more than one column (maybe all?) before you can 
say you have a duplicate?  Post back with more details.  HTH  Otto
"Dave542" <Dave542@discussions.microsoft.com> wrote in message 
news:8FDDDE14-6EF2-4BCA-8D4D-F08CD33A4762@microsoft.com...
> I've recently took over a task at my job of managing some Excel files.
> I am using Excel 2002. There are several Excel files that need to be
> combined into one file however, if I do this, I will have some duplicate
> records that I will have to delete.
> The only way I know how to do this is to copy and paste and then look for
> duplicate records manually and delete them as I find them. Is there an 
> easier
> way to do this?
> Thank you for you support and help. 


0
ottokmnop (389)
4/5/2006 2:18:49 AM
Wow...This is the first time I've posted here or done anything like this 
before.
I was told at work to try asking my question here and I would get some help.
I am surprised. 
Thank you very much for your reply.
I don’t have VBA.
I have several columns in each file but, on of the columns is an employee ID 
number.
I’ll sort on this column and the delete the records (rows) that are 
duplicated. If there are more that on records with the same employee number I 
delete the additional records so that there is only one record per employee 
ID number.


"Otto Moehrbach" wrote:

> Yes, there is.  I would use VBA to do that job, but I need more info.  When 
> you say there are duplicate records, exactly what is duplicated?  In other 
> words, if you are looking for duplicate records manually, what do you look 
> for?  Do you look at just one column and if that one column has a duplicate, 
> delete one of those rows?  Or do you have multiple columns and you have to 
> check for a duplicate in more than one column (maybe all?) before you can 
> say you have a duplicate?  Post back with more details.  HTH  Otto
> "Dave542" <Dave542@discussions.microsoft.com> wrote in message 
> news:8FDDDE14-6EF2-4BCA-8D4D-F08CD33A4762@microsoft.com...
> > I've recently took over a task at my job of managing some Excel files.
> > I am using Excel 2002. There are several Excel files that need to be
> > combined into one file however, if I do this, I will have some duplicate
> > records that I will have to delete.
> > The only way I know how to do this is to copy and paste and then look for
> > duplicate records manually and delete them as I find them. Is there an 
> > easier
> > way to do this?
> > Thank you for you support and help. 
> 
> 
> 
0
Dave542 (2)
4/6/2006 2:21:02 AM
Dave
    Since you need to check only one column, you can do this with a 
worksheet formula or with VBA.  You say that you don't have VBA.  I think 
you mean you don't know VBA.  VBA is a part of Excel, so you have it 
available.
Here is the procedure using a formula:
Let's say your data starts in row 1.
First sort all the data by the ID column.
In row 1 of the first empty column, enter the formula:
=IF(A2=A1,"Y","")

Now drag that formula down as far as your data goes.

You will see a "Y" in that column for every duplicate you have.  For 
instance, if you have 3 rows with the same ID, you will see 2 "Y"s.

Now do an AutoFilter (Data - Filter - AutoFilter) on the "Y" in the ID 
column.

This will display all the "Y" rows together.

Delete all those rows.

Click on Data - Filter - ShowAll

Done.

This is easy to do from my end.  It may not be from your end.  Post back if 
you need more.  HTH   Otto


"Dave542" <Dave542@discussions.microsoft.com> wrote in message 
news:D6726B96-9B0B-41C6-8E10-CBDD7A880BB9@microsoft.com...
> Wow...This is the first time I've posted here or done anything like this
> before.
> I was told at work to try asking my question here and I would get some 
> help.
> I am surprised.
> Thank you very much for your reply.
> I don't have VBA.
> I have several columns in each file but, on of the columns is an employee 
> ID
> number.
> I'll sort on this column and the delete the records (rows) that are
> duplicated. If there are more that on records with the same employee 
> number I
> delete the additional records so that there is only one record per 
> employee
> ID number.
>
>
> "Otto Moehrbach" wrote:
>
>> Yes, there is.  I would use VBA to do that job, but I need more info. 
>> When
>> you say there are duplicate records, exactly what is duplicated?  In 
>> other
>> words, if you are looking for duplicate records manually, what do you 
>> look
>> for?  Do you look at just one column and if that one column has a 
>> duplicate,
>> delete one of those rows?  Or do you have multiple columns and you have 
>> to
>> check for a duplicate in more than one column (maybe all?) before you can
>> say you have a duplicate?  Post back with more details.  HTH  Otto
>> "Dave542" <Dave542@discussions.microsoft.com> wrote in message
>> news:8FDDDE14-6EF2-4BCA-8D4D-F08CD33A4762@microsoft.com...
>> > I've recently took over a task at my job of managing some Excel files.
>> > I am using Excel 2002. There are several Excel files that need to be
>> > combined into one file however, if I do this, I will have some 
>> > duplicate
>> > records that I will have to delete.
>> > The only way I know how to do this is to copy and paste and then look 
>> > for
>> > duplicate records manually and delete them as I find them. Is there an
>> > easier
>> > way to do this?
>> > Thank you for you support and help.
>>
>>
>> 


0
ottokmnop (389)
4/6/2006 12:06:17 PM
Dave
    I forgot to mention that you should make a copy of your file and try the 
procedure on the copy.  Only when you feel confident that you have it, do it 
on your real file.  Otto
"Dave542" <Dave542@discussions.microsoft.com> wrote in message 
news:D6726B96-9B0B-41C6-8E10-CBDD7A880BB9@microsoft.com...
> Wow...This is the first time I've posted here or done anything like this
> before.
> I was told at work to try asking my question here and I would get some 
> help.
> I am surprised.
> Thank you very much for your reply.
> I don't have VBA.
> I have several columns in each file but, on of the columns is an employee 
> ID
> number.
> I'll sort on this column and the delete the records (rows) that are
> duplicated. If there are more that on records with the same employee 
> number I
> delete the additional records so that there is only one record per 
> employee
> ID number.
>
>
> "Otto Moehrbach" wrote:
>
>> Yes, there is.  I would use VBA to do that job, but I need more info. 
>> When
>> you say there are duplicate records, exactly what is duplicated?  In 
>> other
>> words, if you are looking for duplicate records manually, what do you 
>> look
>> for?  Do you look at just one column and if that one column has a 
>> duplicate,
>> delete one of those rows?  Or do you have multiple columns and you have 
>> to
>> check for a duplicate in more than one column (maybe all?) before you can
>> say you have a duplicate?  Post back with more details.  HTH  Otto
>> "Dave542" <Dave542@discussions.microsoft.com> wrote in message
>> news:8FDDDE14-6EF2-4BCA-8D4D-F08CD33A4762@microsoft.com...
>> > I've recently took over a task at my job of managing some Excel files.
>> > I am using Excel 2002. There are several Excel files that need to be
>> > combined into one file however, if I do this, I will have some 
>> > duplicate
>> > records that I will have to delete.
>> > The only way I know how to do this is to copy and paste and then look 
>> > for
>> > duplicate records manually and delete them as I find them. Is there an
>> > easier
>> > way to do this?
>> > Thank you for you support and help.
>>
>>
>> 


0
ottokmnop (389)
4/6/2006 12:08:42 PM
Reply:

Similar Artilces:

Excel 2003- Tabbing to a specific location in a cell
I have created a template and I want to be able to fill in parts of the template by just tabbing and the cursor to move to the next cell...and to a specific area in the cell...how do i set up my template to do this? THanks so much unlock the cells you want to tab to. leave the other cells protected. then protected the sheet. -- Gary Keramidas Excel 2003 "monty the magician" <monty the magician@discussions.microsoft.com> wrote in message news:FD3728D8-E730-407E-B58A-FED2C6D9EA8A@microsoft.com... >I have created a template and I want to be able to f...

Mails Combined
HI, I mistakenly delete all mails from Inbox and then found them on the deleted Items and selected all and ask to move folders back to Inbox. the problem is all mails were combined into one email and moved back in to Inbox as a single email. How do i go back to the original version. How do i uncombined all the mails in to individual mails from the right recipient? Please help ASAP.. Submitted using http://www.outlookforums.com ...

merge/combine workshhets
I need to merge about 75 worksheets into one spreadsheet on a regular basis - all have exactly the same format/layout etc back can vary in the number of rows Try this Jeff http://www.rondebruin.nl/copy3.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Jeff224" <Jeff224@discussions.microsoft.com> wrote in message news:C6790506-2888-4F4D-80EA-BB0D9F9B27A2@microsoft.com... >I need to merge about 75 worksheets into one spreadsheet on a regular basis - > all have exactly the same format/layout etc back can vary in the number of > rows Or http://www.rondebru...

document for file I/O with VBA
Hi all, Where can I find a complete document about File I/O with VBA? I find it hard to find in help due to the way it is organized. I want to find ways to deal with files especially ".INI" alike text files using Open, Input, etc. (let me know if there are better ways). The functions I need are to seek lables like [General] and delte or replace data in the section. Thank you, muster <muster@gmail.com> wrote: >Where can I find a complete document about File I/O with VBA? I find >it hard to find in help due to the way it is organized. Access Type Writing Data ...

Printing spreadsheets crested in earlier versoins of Excel
Hello - My computer and my wife's computer are networked. I have WinXP and Excel 2000 9.0.3821 SR-1 on my computer. She has Win98 and Excel 2000 9.0.2720. When I print a spreadsheet created in Win98 with an earlier version of Excel (I'm not sure which version), I get "squiggilly" vertical lines and numbers. When I print the same file on the same printer using my wife's computer it looks fine. What is the problem? Is there a way I can convert the file so it will print correctly? Thanks Drew As answered at your second post: Have you tried copying the conte...

Finding the combination that appears more times
Hi everybody i have a list on excel 2007 that displays the purchase of items on columns A:E, each column showing one (1) item I want to find what combination of items appears more times, especifically, which combination of four (4) is the favorite mix eg: A B C D F A C D E F B C D E F B C D F Z in this example, the winner would be (B, C, D, E) as it appears 3 times the main problem is that I have over 1,000 rows and trying with =SUMPRODUCT(COUNTIF(A1:E1,$N$1:$R$1)) por each combination would take forever... is there a simpler, quicker ...

excel 2003 publish to a remote share does not work...
Using Excel 2003. Create a chart and have saved it as a web page and published to a remote share. When I save it locally, it works fine and is interactive,etc.. When I save it remotely and open the htm file in a web browser, it opens like the charting area but the data is not present. Gives error below in the pivot table that appears. Getting error The query could not be processed: o Error opening data file "file://\\remoteserver01\hassan\numbers_files\Numbers_Jan24_12558_cachedata001.xml". Any idea how to fix it ? I want others to access it remotely. Thanks ...

excel formula #4
I have in column A1:A365 all dates of the year 2005 and in colum B1:B365 the days of that date. In column D1:D365, E1:D365 and F1;F365 I fill in a X for every actio Succeeded. SO if it is 1 January 2005 and the action in D1 and E1 and F1 are al succeeded I fill in those fields an: X I have in a cell a formula what checks how many % (procent) succeede of the fields who are filled. But when it is December he also calculates the X that are filled in A etc. Now I would like to make a formula which calculates how many action went good in the last 30 days is this possible? So that he only chec...

How do I import a database from excel 2003 to 2007?
The database is not too large, but important. I need to transfer it to Excel 2007 from 2003. Can this be done without losing data, or changing the format? If so, how? Obviously I am not very adept with Excel. Thank you for your help. Theresa, Start by making a backup copy of your 2003 format file, just in case. Open Excel 2007 and then open the 2003 file with it. It will open in "compatibility mode" meaning that it is treated as an Excel 2003 file with 2003's restrictions on rows, columns and other features. Use File | Save As to save the file in one of the native Exce...

Dynamic formulae
Hello Excel disciples, =A0 =A0 I wonder if I can run this past you? =A0 =A0 Lotus 123 had a facility that you were able to build "Dynamic" formulae ie; =A0to construct using text and ranges to create valid formulae. =A0 I have several worksheets name Team 1, Team 2, Team 3, each identical etc I have a consolidation worksheet that takes numeric values from a cell from= each and sums the total. =A0 In the Totals sheet columns above the values column is the team name in= text. =A0 =A0 =A0Team 13 =09=09Team 14=09=09Team 15 etc =A0Grd1 =A0Grd2 =A0100 =A0=09375 =A0100=09=09375 43.5=09=09...

Excel problem #12
I am working on an Excel file for work and thought that maybe you could help me out with the formulas. I initially thought that a combination of an If / sum function would work but I am having a problems. He is the situation: I have one Excel sheet with a couple of workbooks. I have one workbook that has about 8 columns and 25 rows (the number of row could change on any given day) one of the columns has different plants listed (coded by a number). What I want is to have Excel look up the plant I tell it to, and then sum the cost savings (in a different column) by plant. I then want Excel...

Excel
My son is trying to reverse the x and y axis on an Excel chart. I don't believe you can but I thought I should ask. DenisB - > My son is trying to reverse the x and y axis on an Excel chart. I don't > believe you can but I thought I should ask. < One way is to select the chart, then choose Chart | Source Data | Series, and respecify the ranges for X Values and Y Values. - Mike www.mikemiddleton.com "Mike Middleton" wrote: > DenisB - > > > My son is trying to reverse the x and y axis on an Excel chart. I don't > > believe y...

Duplicate Z and ZZ reports
I am getting duplicate shift closing reports from both of my registers. When I view them in the journal, there is one regular Z report, and one with all zeros. There are two ZZ reports also. The cashiers are not z'ing twice as I have tried this myself. With six shifts per day this is getting crazy! Any suggestions? Thanks so much. We had this happen once with version 1.2 What version are you running? "Target" <Target@discussions.microsoft.com> wrote in message news:B5CF6FFA-1621-4F1E-9359-0677EF6464B4@microsoft.com... >I am getting duplicate shift closing report...

Outlook 2000. Trouble importing PST files from another Outlook 2000
I recently had to reformat my hard drive and reinstall Windows on my computer. I have Windows 98, and Office 2000. I made PST files of my curent outlook contacts ..., the reinstallation went fine until I attempted to import the PST file into Outlook. While using the Impoert/Export Wizard, just after brousing to the file in question, as soon as I clicked on the "Next" button, I got the following pop up message "Properties for this Information Service must be defined prior to use" Click "OK" When I clicked "OK", I got another message "File ac...

Combining 2 Excel (xls) files
I have 2 xls workbooks with different sheet names and both with different range names. I would like to combine them both into one workbook so that I don't have to re-create all of the range names. Is there a way to do that? -- LAF ------------------------------------------------------------------------ LAF's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=9656 View this thread: http://www.excelforum.com/showthread.php?threadid=346017 Hi LAF- Open both files (let's call them A & B, and assume A will be your "combined" file). Working i...

Outlook Live and Backup of .pst files
Hi, I use Outlook Live in conection with my Desktop Outlook and I am quite please with the service it provides. My question is the following: I want to backup the .pst files but when I go to the folder that suposedly contains them (my Computer / Documents & settings / User / Local settings / application data / Microsoft / Outlook ) I do not find them. I see only an Outlook.nst document that looks as if it is the one. Am I correct? Thanks for any help you may provide, artist1855 How big is the NST file? It sounds like it's the local cache of data but check the modification da...

Excel Add-In and Dual Monitor Causing Read-Only
Hello: The client has a dual monitor. And, we have a 3rd-party Excel Add-In that "pushes" data into our accounting system from this excel spreadsheet with a click of a button in the Add-Ins tab. On one monitor, we see the Excel spreadsheet containing this data that we "push" with this Add-In. When we successfully push, the other monitor automatically opens a read-only version of this same spreadsheet. We need to find a way for Excel to not produce this read-only spreadsheet on the second monitor. You see, as we continue testing, the client is updating ...

Importing csv or excel into RMS
Good afternoon, We just bought the RMS for our store and need to import our old data base which is available in csv & excel form into the new RMS database. We have about 11000 items (item code, description, category, supplier, cost & selling price). I have checked and found various references to QS Import, MSSQL Import, Retail Realm, etc... and I'm not sure what would be the best to use. I'm lost, any help anyone can give me I'll appreciate it immensely. Because a local programmer wants to charge me $1,600.00 (too much) to built a program. Thanks again, Tommy Hi...

Opening Multiple files in separate Excel folders
Can this be done? Hi what do you mean with 'separate Excel folders'? -- Regards Frank Kabel Frankfurt, Germany Paul Dunn wrote: > Can this be done? Yes. Dim aryFiles Dim oFSO Sub LoopFolders() Dim i As Integer Set oFSO = CreateObject("Scripting.FileSystemObject") selectFiles "c:\MyTest" Set oFSO = Nothing End Sub '--------------------------------------------------------------------------- Sub selectFiles(sPath) '--------------------------------------------------------------------------- Dim Folder As Object Dim Files As Object D...

Combining Publications
Can I combine two different publications into one? Suzi wrote: > Can I combine two different publications into one? ========================================= Personally, I would simply open two instances of Publisher (one document in each) / tile the two screens...and copy/paste. Maybe the following link will be useful: Combining Publisher Documents http://tinyurl.com/2lpj5w -- John Inzer MS Picture It! - Digital Image MVP Digital Image Highlights and FAQs http://tinyurl.com/aczzp Notice This is not tech support I am a volunteer Solutions that wo...

Excel and MySQL
Is it possible to connect to a MySQL Database using Excel, importing and exporting data? Sugestions please Thanks Luis --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.796 / Virus Database: 540 - Release Date: 13/11/2004 Hi Louis, Here is one place to look at (scroll past the .Net stuff) http://www.able-consulting.com/ADO_Conn.htm Also see this thread in microsoft.public.excel.programming 15 June 2004 All messages from thread "Examples of Using VBA, ODBC, and mysql" http://groups.google.com/groups?hl=en&...

Multiple excel users can open the same file and edit at the same .
We are running Excel 2002 SP2. We recently moved to a Windows 2000 server and now we are running into the issue where Multiple users can open the same excel file and edit it at the same time without getting the file the 2nd time in Read Only mode. Go to Tools>Share Workbook & choose the setting to Allow changes by more than one user. The file must also be stored in a Shared network/workgroup folder. Save the fil. It is also strongly recommended that you research the feature in Excel Help to become more familiar with it before you start sharing the file. HTH |:>) "Jef...

free excel manuals and tutorials
http://programatium.com/en/ ...

How do I update charts in excel 2007
Hi - I'm tracking and charting real estate data in three cities, by month. I can add updated info into the worksheet columns, but I can't get the corresponding points into the line chart that I made three months ago from the columns. Now using excel 2007. Had no trouble with this task in excel 2003, becouse I could easily change the data range and then the chart followed the changes. I'm new in 2007, and when I find the data range and change it to correspond the with the updated columns nothing happens in the chart Appreciate help I have a line chart on the s...

Excel file increasing in size
There is a excel file that is on a shared drive. When I update file and each time I save the file the folder keeps doubling in size continously. Why is this happening and is there a fix for this? Thanks. When you hit ctrl-end, do you go to what you expect to be the lastused cell or does it take you way past what you expected? If it goes further than you expected, you could try using some of the techniques at Debra Dalgleish's site: http://www.contextures.com/xlfaqApp.html#Unused Any chance you're tracking changes and making lots of changes? Premkumar Kavalath wrote: > &...