An automated way of comparing an old spreadsheet with an updated version

Hi,

I recieve an updated spreadsheet each month showing the newer entries
at the bottom of the sheet. What i need to do is find a way that this
newer data can be compared to previous entries to see if it has been
entered before (i.e Mr Smith was entered in Jan and again in May)
Currently I am having to manually search through the spreadsheet
(about 400 cells) and I figure there must be an easier way!

Thanks in advance

Alex
0
6/30/2008 9:27:56 AM
excel 39879 articles. 2 followers. Follow

4 Replies
164 Views

Similar Articles

[PageSpeed] 55

Hi Alex

Assuming that you have headers in row 1 and your data you are wanting to 
search is in column A, enter in a blank column
=COUNTIF(A:A,A2)
and copy down as far as required using the fill handle.

Highlight your header row>Data>Filter>>Autofilter>from the dropdown on your 
new column choose Customise>Greater Than>1

-- 
Regards
Roger Govier

<alexlavington@hotmail.com> wrote in message 
news:30472798-988d-4a23-a80a-788741144dcc@l42g2000hsc.googlegroups.com...
> Hi,
>
> I recieve an updated spreadsheet each month showing the newer entries
> at the bottom of the sheet. What i need to do is find a way that this
> newer data can be compared to previous entries to see if it has been
> entered before (i.e Mr Smith was entered in Jan and again in May)
> Currently I am having to manually search through the spreadsheet
> (about 400 cells) and I figure there must be an easier way!
>
> Thanks in advance
>
> Alex 

0
Roger
6/30/2008 10:22:36 AM
Assuming your data in Column A, enter the following formula in Column B 
starting with Row 2:-

=IF(A:A<>"",IF(ISERROR(MATCH(A2,$A$1:A1,0)),"Unique","Repeat"),"")
Now, you have the repeated enteries shown as Repeat.

Pls revert back if this helps.  Thanks.

-- 
Dilip Kumar Pandey
MBA, BCA, B.Com(Hons.)
dilipandey@yahoo.com
dilipandey@gmail.com
New Delhi, India


"alexlavington@hotmail.com" wrote:

> Hi,
> 
> I recieve an updated spreadsheet each month showing the newer entries
> at the bottom of the sheet. What i need to do is find a way that this
> newer data can be compared to previous entries to see if it has been
> entered before (i.e Mr Smith was entered in Jan and again in May)
> Currently I am having to manually search through the spreadsheet
> (about 400 cells) and I figure there must be an easier way!
> 
> Thanks in advance
> 
> Alex
> 
0
DILipandey (41)
6/30/2008 10:50:00 AM
On 30 Jun, 11:22, "Roger Govier" <roger@technology4unospamdotcodotuk>
wrote:
> Hi Alex
>
> Assuming that you have headers in row 1 and your data you are wanting to
> search is in column A, enter in a blank column
> =COUNTIF(A:A,A2)
> and copy down as far as required using the fill handle.
>
> Highlight your header row>Data>Filter>>Autofilter>from the dropdown on your
> new column choose Customise>Greater Than>1
>
> --
> Regards
> Roger Govier
>
> <alexlaving...@hotmail.com> wrote in message
>
> news:30472798-988d-4a23-a80a-788741144dcc@l42g2000hsc.googlegroups.com...
>
>
>
> > Hi,
>
> > I recieve an updated spreadsheet each month showing the newer entries
> > at the bottom of the sheet. What i need to do is find a way that this
> > newer data can be compared to previous entries to see if it has been
> > entered before (i.e Mr Smith was entered in Jan and again in May)
> > Currently I am having to manually search through the spreadsheet
> > (about 400 cells) and I figure there must be an easier way!
>
> > Thanks in advance
>
> > Alex- Hide quoted text -
>
> - Show quoted text -


Thanks for the respones. An added problem I neglected to mention is
that some of the data has entry discrepancies - for example some
entries are listed Smith, J and some are listed as Smith, John. is
there anyway just to search the first part of the cell?

Thanks
Alex

0
6/30/2008 2:53:25 PM
Hi Alex

Try the following formula instead, then use the filter as described 
previously
=SUMPRODUCT(--(LEFT($A$2:$A$1000,6)=LEFT(A2,6))*($A$2:$A$1000<>""))

This is testing for the first 6 characters (which would deal with Smith) and 
maybe sufficient for your needs.

If there is always a comma after the name, you could use
 =SUMPRODUCT(--(LEFT($A$2:$A$1000,FIND(",",A2)-1)=LEFT(A2,FIND(",",A2)-1))*($A$2:$A$1000<>""))which would deal with the whole of the name up to the comma.--RegardsRoger Govier<alexlavington@hotmail.com> wrote in messagenews:04dc8906-5eb7-4d19-aa1c-9eafbfd6f02c@27g2000hsf.googlegroups.com...> On 30 Jun, 11:22, "Roger Govier" <roger@technology4unospamdotcodotuk>> wrote:>> Hi Alex>>>> Assuming that you have headers in row 1 and your data you are wanting to>> search is in column A, enter in a blank column>> =COUNTIF(A:A,A2)>> and copy down as far as required using the fill handle.>>>> Highlight your header row>Data>Filter>>Autofilter>from the dropdown onyour>> new column choose Customise>Greater Than>1>>>> -->> Regards>> Roger Govier>>>> <alexlaving...@hotmail.com> wrote in message>>>> news:30472798-988d-4a23-a80a-788741144dcc@l42g2000hsc.googlegroups.com...>>>>>>>> > Hi,>>>> > I recieve an updated spreadsheet each month showing the newer entries>> > at the bottom of the sheet. What i need to do is find a way that this>> > newer data can be compared to previous entries to see if it has been>> > entered before (i.e Mr Smith was entered in Jan and again in May)>> > Currently I am having to manually search through the spreadsheet>> > (about 400 cells) and I figure there must be an easier way!>>>> > Thanks in advance>>>> > Alex- Hide quoted text ->>>> - Show quoted text ->>> Thanks for the respones. An added problem I neglected to mention is> that some of the data has entry discrepancies - for example some> entries are listed Smith, J and some are listed as Smith, John. is> there anyway just to search the first part of the cell?>> Thanks> Alex>
0
Roger
6/30/2008 5:49:09 PM
Reply:

Similar Artilces:

Sort order for chart only and not on spreadsheet
Hi there! I neet to plot a simple graph with the data below Candidate Score A 4 B 3 C 7 D 1 The relevent numbers (are actually scores) keep getting updated for each candidate. I want my graph to plot it in the order of highest score to lowest (whilst keeping my spreadsheet in the same order A,B,C,D . . .) every time I update the score (automatically) without me having to change it everytime I do it. I have 32 entries. Can I use a macro to do this Can anyone pls help me plssssssssssss Thanks so mu...

Trial Version of CRM 12-23-03
Hi, I am trying to get a trial version of CRM, does anyone know where I can get it? We are a Microsoft Certified Partner but have not received any info about it yet. thanks Jamie ...

Is there any way a macro can be created for various print area selections?
Hi, I'm looking to see if anyone knows of a way to have a macro select a print area of a constant width (columns A thru K) but with a variable length. Our MRP system will dump our BOM's into Excel and all of the BOM's vary in the number of rows used. Some can be 3 pages while others will be 15 pages. Currently we have to set up a print area to capture the information we want printed under columns A thru K everytime we go to print a BOM. Please advise, Steve If you hit ctrl-end, do you go way past where you think the data should end? If yes, you could use the techniques at...

How can I update endnote cross-references in Word 2007 ?
I created a long document with many endnotes and some cross-references to these endnotes. When I insert a new endnote somewhere in the document, the following endnote numbers change but not the cross-reference numbers. This seems to be a bug in Word 2007 as it worked perfectly in my previous Word version. How can I update endnote cross-references in Word 2007 ? Ctrl+A, F9 should work in all versions of Word. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "Joe56" <Joe56@discussions.microsoft.com> wro...

Solution for: Windows XP (NL): Windows Activation Problem + Windows Automatic Update Problem = Very Slow Computer.
I think I have found a solution on the internet, I will sum it up shortly because the description wasn't that accurate, my description will be more accurate with additional information about what I think might have happened, but first a little story what I wanted to do today: Today I wanted to try and install Service Pack 3 for Windows XP dutch version (NL). This was not possible because Windows Automatic Updates was required by the Microsoft website to be able to download it and windows automatic updates is malfunctioning, so that became a short attempt. Then I decided to ...

KB982524-.Net update
WIN XP PRO SP3 I have just dll, burnt to disc and installed this update. All went well - the yellow shield has gone from system tray, but on checking Add/Remove, the KB installed is 976576?? Is this correct? Rgds Antioch On 2010-06-25 8:43 a.m., antioch wrote: > WIN XP PRO SP3 > > I have just dll, burnt to disc and installed this update. > All went well - the yellow shield has gone from system tray, but on > checking Add/Remove, the KB installed is 976576?? > > Is this correct? KB982524 says that 982524 is an update rollup, and references 9765...

Old business card that will not print to Avery s8371 template
I have an old business card designed previously in Publisher. I can not print to an Avery S8371 template in Publisher 2007. How do I correct this problem? The printing does not correspond to the pref. lines in the template. Use one of the pre-formatted Avery templates. They are all the same... 2.5 x 3. Open your old file, copy/paste into the new. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Jeff B" <Jeff B@discussions.microsoft.com> wrote in message news:778BCC52-9801-4C83-9630-7125D4ACD255@microsoft.com... >...

Please Help: compare values in two columns present in seperate tab
Hi All, How can I compare values in two columns (columns are in different tables) ? I also want to display the values that dont match in a seperate table. For eg: If values in Column1 from Table1 does not match Values in Column1 from Table2 then display those values in a seperate table Is there a way to do this? THanks in advance Under query types, there is one for unmatched data and one for matched. -- Milton Purdy ACCESS State of Arkansas "sam" wrote: > Hi All, > > > How can I compare values in two columns (columns are in differe...

How do I convert an excel spreadsheet into a pdf?
You need a pdf creator program to do that, there's a free one called PDFCreator at sourceforge.net Or you can buy one from Adobe.com Try this site http://www.rcis.co.za/dale/info/pdfguide.htm -- XL2002 Regards William willwest22@yahoo.com "pphelp" <pphelp@discussions.microsoft.com> wrote in message news:7DB46284-1138-441A-8E4B-3DF829C27EBE@microsoft.com... | I also recommend the method used on this site. Will make one PDF file from multiple sheets. Gord Dibben Excel MVP On Fri, 7 Jan 2005 23:45:29 -0000, "William" <willwest22@yahoo.com> wrote:...

Comparing two columns of text data
I'm working with copies of someone else's SAP files. Machine operators enter "Notifications" into SAP, which schedulers turn into "Work Orders". After the job is complete, there is a row of data for each time anyone - operator, scheduler, mechanic, etc. - made a change to the record. Notifications have distinct numbers, as do work orders. So you may have: NOTIFICATION | WORK ORDER | ACTIVITY 0001 | 2985 | Notified pump out 0001 | 2985 | Scheduled work 0001 | 2985 | Calibrated pump 0001 | 2985 | Caliper post on first grid armature adjusted to thr...

old topic, new questions: VSS and Exchange
I saw in a post on VSS and Exchange in a microsoft newsgroup and I had a quick question. Is there a site (or can you answer) that describes the disadvantages and advantages of using the Exchange VSS Writer vs. using the streaming API? I'm having a hard time picturing scenarios and what I understand so far is that VSS doesn't allow "no loss" restores or "out of place" restores. But what these are is blurry to me. Given that the Recovery Storage Group was touted as an awesome feature for Exchange 2003, is it thus useless if you are using VSS as your backup strategy?...

Import old messages from slaved hard drive
Outlook Express won't import my old folders & messages from drive 'E' now I'm on new computer with 'C'. The closest I came to accessing got me a message to close OE, but then I can't import. Any advice greatly appreciated see tomsterdam.com for help in setting up OE using our old files. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, Visual Quick Start Guide - OneNote 2003 Outlook Tips: http://www.outlook-tips.net/ http://www.poremsky.com - http://www.cdolive.com Expert Zone http://www.microsoft.com/windowsxp/exp...

update /delete external refs
I have written a macro and want to se it on older workbooks.This requires that a new page replaces an existing page.However all the references come over with the new page.I want to use edit/find replace all to replace [JUL02]Frame!C$6 with Frame!C$6 ie delete all references to original worksheet.(or some other way!) I have figured it out!.I copy the new page over to the old worksheet and then change source of links back to itself! >-----Original Message----- >I have written a macro and want to se it on older >workbooks.This requires that a new page replaces an >existing pa...

is there a way to make graphic indicators of status
In MS Project you can customize a field to give a graphic indicator for the status. Is there a way to do it in excel? -- rdc EXCEL 2007 You appear to want Conditional Formatting. You may find the following helpful. Go into F 1 Help Facility and type in:- conditional formatting Hit the Search, "button". There is s topic called:- Conditionally formatted data: Examples and guidlines Click on the above topic. Opening the above topic there is a hyperlink about three quarters of the way down the page called:- Conditional Formatting Samples Click on...

Is there any way of calculating a running total within a single cell in a column
Can anyone help with this query? e.g. I want to work out how many people are working at any time in a 24 hour period. The number of people rostered on differs each day of the week. Monday Time No. rostered on 6.00 2 7.00 2 8.00 2 9.00 2 10.00 (and so on) Can I add additional staff number in my 'no. rostered on column' by just entering the number into the cell. Say, if there were an extra 2 staff starting their 8 hour shift at 9. am. is there a way of just typing in '2' into that cell and getting '4'? What I do at the moment ...

Update pass through query of report recordsource
I am working on upsizing my access backend database to SQL Server. I have linked all sql tables to the frontend and am now running into the issue of slow performance on one of the reports. One way to improve that I thought was to change the recordsource query (which is a saved query) to a pass through query. However there are form controls that drive the parameters. So I was wondering if a better way to do this is to create the sql query string in vba and then set the report record source. Is this a viable option? Is there a better way? Please ask any leading questions i...

Track Changes compatibility between versions of Excel
I'm planning to undertake a project which involves Excel sheets created in Excel 2003 and tracking the changes made to those sheets via Excel 97. Does anybody know of any problems with the compatibility of this function? This would help me out a lot. Thanks. ...

Access2000: Update query
Hi I have a table p.e. MyTable with columns ID, F01, F02, ..., F12 I also have a view/query p.e. MyQuery, also with columns ID, F01, F02, ..., F12, where MyQuery.ID is read from MyTable (i.e. both table and query have same set of ID-values), and rest of fields are calculated based on data from other tables in database. Now I need to update MyTable with values for columns F01 - F12 from MyQuery for all ID's. Somehow I don't get it working. What will be right syntax? Thanks in advance! Arvi Laanemets Normally, you use something like UPDATE MyTable INNER ...

Security Update for Windows Server 2003 (KB956744) Cannot Install.
For Windows Server 2003. This is a standard Windows Update that I try to install but get a message that it failed to install. I can't find any source to tell me why it won't or what I can do about it. I am not getting an error code of any sort - it is just listed under "not installed" when I get my update installation screen at the end of the installs. Can anyone help please? I downloaded the update manually from: http://www.microsoft.com/downloads/details.aspx?familyid=a37a2d8a-a5ce-4f06-bf07-8cafa16e7a59&displaylang=en Ran it, and then ran Check U...

Is there a way to change preset margins?
I have figured out how to change the default template, which is great. Is there a way to change the margin presets in Word 2007? I don't want to change the default/normal template - I use several types of page marins routinely, but only a couple of them are in the presets. However, I NEVER use the 2003 Default margin with 1.25" side margins. Is there a way to create my own presets to those that I use regularly? Thanks No way to create presets, but you can certainly create templates with the desired margins; see http://word.mvps.org/FAQs/Customization/CreateATemp...

Outlook 2003 Update Fails
Using automatic update I have downloaded and tried to install an update listed as Outlook 2003 Junk Mail Filter (KB925254), but every time I get a message that the update failed. I tried to go to the Microsoft website to find out about this but do not know where to go or who to tell. Help, Frank Apparently this was in my outbox and I did not realize it. Please disregard to see my related message. "flyer" <franklin.breeden@verizon.net> wrote in message news:WdSih.5$Ej7.3@trnddc02... > Using automatic update I have downloaded and tried to install an update > lis...

Upgrade Publisher 2000 to version 6.0
I'm running Version 5.0 and can't open documents created in 6.0. ...

Automation
Hi, Please i need a way around this stuff quickly. I deal with large excel files and usually have to comapare an electronic copy (current) and a hard copy (downloaded from a server a week earlier) and make some remarks on the new one. I could actually lay my hands on the electronic copy of the older list. The prob now is that how do i automate the process and let the program (excel or any other u might suggest) note the missing rows (numbers) in the newer and give a PASS remark on the newer one? I have tried all the tricks i know about writing/ recording excel macros and vba to no avail. P...

Exchange version
How do I find out what version of exchange I am running. WE have small business server 2000 with exchange running on it. I went into exchange manager and clicked on help about and it comes up with microsoft management console with no reference to exchange. Please help If you're running SBS2000, you're using Exchange 2000. paploo wrote: > How do I find out what version of exchange I am running. WE have > small business server 2000 with exchange running on it. I went into > exchange manager and clicked on help about and it comes up with > microsoft management console with n...

Whats the latest version on GP
Thanks Great Plains 8.0 - GP 8.0 extensions is now available. The latest service pack for GP is SP2 -- Regards James[MVP] Visit MBS Blog Central http://mbscentral.blogs.com "Jitu" <info@vbconsulting.co.uk> wrote in message news:e8G8uzlRFHA.2964@TK2MSFTNGP15.phx.gbl... > Thanks > > Latest Version of GP is GP8.0 "Jitu" <info@vbconsulting.co.uk> wrote in message news:e8G8uzlRFHA.2964@TK2MSFTNGP15.phx.gbl... > Thanks > > ...