Locating Info In New Excel Spreadsheet When Compared To Old One

Hello,

I'm a neophyte regarding Excel spreadsheets, so the answer to my question
may be obvious to those experienced in it.

On a semi-weekly basis, I receive an Excel spreadsheet that is identical in
every way to the previous one except that new cells have been added.

Neither I nor the sender of the spreadsheet has any control over where the
new cells will be added within the file.

With some files having over 22,000 cells, it's extremely difficult and
time-consuming to visually search for those cells which are new.

Each cell, whether new or old, has info, including dollar amount.

It's the dollar amount in each of the new cells in which I am interested.

If it exists, I am looking for a method or software app that can compare
yesterday's spreadsheet "A" to tomorrow's spreadsheet "B" and then tell me
what appears in "B" that's not in "A".

As always, any and all constructive suggestions are appreciated.

Thank you.

IHT



0
Internet
12/19/2009 7:34:22 PM
excel 39879 articles. 2 followers. Follow

4 Replies
588 Views

Similar Articles

[PageSpeed] 38

      If desired, send your file to my address below. I will only look if:
      1. You send a copy of this message on an inserted sheet
      2. You give me the newsgroup and the subject line
      3. You send a clear explanation of what you want
      4. You send before/after examples and expected results.


-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
"Internet Highway Traveler" <Internet.Highway.Traveler@Invalid.Invalid> 
wrote in message news:hgj9rv$b42$1@news.eternal-september.org...
> Hello,
>
> I'm a neophyte regarding Excel spreadsheets, so the answer to my question
> may be obvious to those experienced in it.
>
> On a semi-weekly basis, I receive an Excel spreadsheet that is identical 
> in
> every way to the previous one except that new cells have been added.
>
> Neither I nor the sender of the spreadsheet has any control over where the
> new cells will be added within the file.
>
> With some files having over 22,000 cells, it's extremely difficult and
> time-consuming to visually search for those cells which are new.
>
> Each cell, whether new or old, has info, including dollar amount.
>
> It's the dollar amount in each of the new cells in which I am interested.
>
> If it exists, I am looking for a method or software app that can compare
> yesterday's spreadsheet "A" to tomorrow's spreadsheet "B" and then tell me
> what appears in "B" that's not in "A".
>
> As always, any and all constructive suggestions are appreciated.
>
> Thank you.
>
> IHT
>
>
> 

0
Don
12/19/2009 10:59:50 PM
 The activeworkbook is the new workbook and it must contain this macro.  The 
name of the old workbook is assumed to be A.xls.  The sheet in the old 
workbook is assumed to be "General.  Change these as needed.  This macro 
adds a new sheet, "Compare", to the active workbook.   It then compares 
every cell in the new workbook's used range against the same cell in the old 
workbook.  Any cell value that is different between the 2 workbooks will be 
listed in the Compare sheet, address and value.  HTH  Otto
Sub Compare()
    Dim Sh As Worksheet
    Dim wb As Workbook
    Dim i As Range
    Dim Dest As Range
    Set Sh = ActiveSheet
    Sheets.Add before:=Sheets(1)
    Sheets(1).Name = "Compare"
    Set Dest = Range("A1")
    Sh.Select
    Set wb = Workbooks("A.xls")
    For Each i In ActiveSheet.UsedRange
        If i <> wb.Sheets("General").Range(i.Address) Then
            Dest = i.Address(0, 0)
            Dest.Offset(, 1) = i.Value
            Set Dest = Dest.Offset(1)
        End If
    Next i
End Sub

"Internet Highway Traveler" <Internet.Highway.Traveler@Invalid.Invalid> 
wrote in message news:hgj9rv$b42$1@news.eternal-september.org...
> Hello,
>
> I'm a neophyte regarding Excel spreadsheets, so the answer to my question
> may be obvious to those experienced in it.
>
> On a semi-weekly basis, I receive an Excel spreadsheet that is identical 
> in
> every way to the previous one except that new cells have been added.
>
> Neither I nor the sender of the spreadsheet has any control over where the
> new cells will be added within the file.
>
> With some files having over 22,000 cells, it's extremely difficult and
> time-consuming to visually search for those cells which are new.
>
> Each cell, whether new or old, has info, including dollar amount.
>
> It's the dollar amount in each of the new cells in which I am interested.
>
> If it exists, I am looking for a method or software app that can compare
> yesterday's spreadsheet "A" to tomorrow's spreadsheet "B" and then tell me
> what appears in "B" that's not in "A".
>
> As always, any and all constructive suggestions are appreciated.
>
> Thank you.
>
> IHT
>
>
> 
0
Otto
12/20/2009 6:47:52 PM
On Dec 20, 10:47=A0am, "Otto Moehrbach" <moehrbachoex...@bellsouth.net>
wrote:
> =A0The activeworkbook is the new workbook and it must contain this macro.=
 =A0The
> name of the old workbook is assumed to be A.xls. =A0The sheet in the old
> workbook is assumed to be "General. =A0Change these as needed. =A0This ma=
cro
> adds a new sheet, "Compare", to the active workbook. =A0 It then compares
> every cell in the new workbook's used range against the same cell in the =
old
> workbook. =A0Any cell value that is different between the 2 workbooks wil=
l be
> listed in the Compare sheet, address and value. =A0HTH =A0Otto
> Sub Compare()
> =A0 =A0 Dim Sh As Worksheet
> =A0 =A0 Dim wb As Workbook
> =A0 =A0 Dim i As Range
> =A0 =A0 Dim Dest As Range
> =A0 =A0 Set Sh =3D ActiveSheet
> =A0 =A0 Sheets.Add before:=3DSheets(1)
> =A0 =A0 Sheets(1).Name =3D "Compare"
> =A0 =A0 Set Dest =3D Range("A1")
> =A0 =A0 Sh.Select
> =A0 =A0 Set wb =3D Workbooks("A.xls")
> =A0 =A0 For Each i In ActiveSheet.UsedRange
> =A0 =A0 =A0 =A0 If i <> wb.Sheets("General").Range(i.Address) Then
> =A0 =A0 =A0 =A0 =A0 =A0 Dest =3D i.Address(0, 0)
> =A0 =A0 =A0 =A0 =A0 =A0 Dest.Offset(, 1) =3D i.Value
> =A0 =A0 =A0 =A0 =A0 =A0 Set Dest =3D Dest.Offset(1)
> =A0 =A0 =A0 =A0 End If
> =A0 =A0 Next i
> End Sub
>
> "Internet Highway Traveler" <Internet.Highway.Trave...@Invalid.Invalid>
> wrote in messagenews:hgj9rv$b42$1@news.eternal-september.org...
>
>
>
> > Hello,
>
> > I'm a neophyte regarding Excel spreadsheets, so the answer to my questi=
on
> > may be obvious to those experienced in it.
>
> > On a semi-weekly basis, I receive an Excel spreadsheet that is identica=
l
> > in
> > every way to the previous one except that new cells have been added.
>
> > Neither I nor the sender of the spreadsheet has any control over where =
the
> > new cells will be added within the file.
>
> > With some files having over 22,000 cells, it's extremely difficult and
> > time-consuming to visually search for those cells which are new.
>
> > Each cell, whether new or old, has info, including dollar amount.
>
> > It's the dollar amount in each of the new cells in which I am intereste=
d.
>
> > If it exists, I am looking for a method or software app that can compar=
e
> > yesterday's spreadsheet "A" to tomorrow's spreadsheet "B" and then tell=
 me
> > what appears in "B" that's not in "A".
>
> > As always, any and all constructive suggestions are appreciated.
>
> > Thank you.
>
> > IHT- Hide quoted text -
>
> - Show quoted text -

Hi Otto, I tried this and got a Run-Time error '9': Subscript out of
range.
How do I fix this. Your macro looks like a nift tool that I could use.
0
SanCarlosCyclist
12/21/2009 2:28:42 AM
"Subscript out of range" means that the code is looking for something, a 
sheet maybe, that doesn't exist.  The only thing this macro is looking for 
is the "Compare" sheet.  Make sure you have one (or change the name in the 
code).  If you have one and still get the error, check the spelling of it 
and/or check that the sheet name doesn't have an extra space before or after 
it.  Otto


"SanCarlosCyclist" <sancarloscyclist@gmail.com> wrote in message 
news:3ec62766-a331-4896-b9c7-969e13060754@s31g2000yqs.googlegroups.com...
> On Dec 20, 10:47 am, "Otto Moehrbach" <moehrbachoex...@bellsouth.net>
> wrote:
>>  The activeworkbook is the new workbook and it must contain this macro. 
>> The
>> name of the old workbook is assumed to be A.xls.  The sheet in the old
>> workbook is assumed to be "General.  Change these as needed.  This macro
>> adds a new sheet, "Compare", to the active workbook.   It then compares
>> every cell in the new workbook's used range against the same cell in the 
>> old
>> workbook.  Any cell value that is different between the 2 workbooks will 
>> be
>> listed in the Compare sheet, address and value.  HTH  Otto
>> Sub Compare()
>>     Dim Sh As Worksheet
>>     Dim wb As Workbook
>>     Dim i As Range
>>     Dim Dest As Range
>>     Set Sh = ActiveSheet
>>     Sheets.Add before:=Sheets(1)
>>     Sheets(1).Name = "Compare"
>>     Set Dest = Range("A1")
>>     Sh.Select
>>     Set wb = Workbooks("A.xls")
>>     For Each i In ActiveSheet.UsedRange
>>         If i <> wb.Sheets("General").Range(i.Address) Then
>>             Dest = i.Address(0, 0)
>>             Dest.Offset(, 1) = i.Value
>>             Set Dest = Dest.Offset(1)
>>         End If
>>     Next i
>> End Sub
>>
>> "Internet Highway Traveler" <Internet.Highway.Trave...@Invalid.Invalid>
>> wrote in messagenews:hgj9rv$b42$1@news.eternal-september.org...
>>
>>
>>
>> > Hello,
>>
>> > I'm a neophyte regarding Excel spreadsheets, so the answer to my 
>> > question
>> > may be obvious to those experienced in it.
>>
>> > On a semi-weekly basis, I receive an Excel spreadsheet that is 
>> > identical
>> > in
>> > every way to the previous one except that new cells have been added.
>>
>> > Neither I nor the sender of the spreadsheet has any control over where 
>> > the
>> > new cells will be added within the file.
>>
>> > With some files having over 22,000 cells, it's extremely difficult and
>> > time-consuming to visually search for those cells which are new.
>>
>> > Each cell, whether new or old, has info, including dollar amount.
>>
>> > It's the dollar amount in each of the new cells in which I am 
>> > interested.
>>
>> > If it exists, I am looking for a method or software app that can 
>> > compare
>> > yesterday's spreadsheet "A" to tomorrow's spreadsheet "B" and then tell 
>> > me
>> > what appears in "B" that's not in "A".
>>
>> > As always, any and all constructive suggestions are appreciated.
>>
>> > Thank you.
>>
>> > IHT- Hide quoted text -
>>
>> - Show quoted text -
>
> Hi Otto, I tried this and got a Run-Time error '9': Subscript out of
> range.
> How do I fix this. Your macro looks like a nift tool that I could use. 

0
Otto
12/21/2009 1:19:11 PM
Reply:

Similar Artilces:

Macro to Combine 2 columns to make one column without merging
I found this macro courtesy of Dave: Option Explicit Sub testme02() Dim myRng As Range Dim myArea As Range Dim myRow As Range Dim myCell As Range Dim myStr As String Set myRng = Selection Application.DisplayAlerts = False For Each myArea In myRng.Areas If myArea.Columns.Count > 1 Then For Each myRow In myArea.Rows myStr = "" For Each myCell In myRow.Cells myStr = myStr & " " & myCell.Text Next myCell myRow.Merge acros...

Chart Location
Hi, How to specify the location of a chart in a sheet (The sheet contains 4 charts).The chart location should vary dynamically depending on the values associated with it. Thanks and Regards, Sumathi Sumathi - You need to specify the coordinates and dimensions of the chart's parent chart object, in pixels. With ActiveChart.Parent '' or With ActiveSheet.ChartObjects(1) .Top = 50 .Left = 50 .Height = 175 .Width = 225 End With - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTe...

Expanding a distrobution List in a new mail
Hi All, I have Outlook 2003 and some of my users who try and send a mail to a DL are unable to expand the list because there is not a '+' sign next to it. I can do this on my client. where is the setting for this? I have been unable to find it. The real reason we are trying to do this is because we need to print these lists some times. Thanks in advance. Dave There is no such setting. All DL's in Outlook 2003 have this option by default. Corrupt ones will not, and most DL's are corrupt by the time you get around to using them--especially if they were created in an earli...

new e-mail
???????????,??????? ???? ? ? ?: ????@???? ????: whitehorse@myway.com ????: ????@???? ????????????? | ???? ...

DPM 2010 RC co-locate SQL Server
I'm creating a protection group for SQL Server 2005 SP3 (CU8) with DPM 2010 RC. I have about 15-20 small databases and about 10-15 larger databases. When I create the protection group the option to co-locate is checked (and not gray), but when the protection group is created I end up with many 10gb disk partitions. Isn't co-location supposed to prevent that? I read that the default for co-location is 30 databases (how do I increase that?) so I tried hand picking the larger databases from the list rather than the auto option and only using those in my protection group....

Pivot Table - wont refresh
I've not experienced this problem in Excel 2000, but after upgrading to Excel 2003, there's an odd problem. Has anyone experience something similar - User error or bug? The problem: 1. Create pivot table with pivot table 'pages'. 2. The page selection list includes a listing that is not in the raw data. Refreshing the pivot table wont reset. It is possible to ascertain from the list order, which data item is being incorrectly shown. 3. In the raw data, replace the name of the incorrectly displayed data item with something else (eg 'ABC') 4. refresh the pivot tab...

Incorrect date in line chart in Excel.
I'm having difficulty with a line chart. I've got two ranges of numbers of two types of calls (from 1972 through 2004) that goes up from 50 to 7700. My chart's Y axis has automatically decided to show from 0 to 16,000. On the chart itself, my data placement starts at about 6,000 (for both 24 “F” calls and 649 “M” calls in the year 1972) and goes up to a point above 14,000 (for a figure that should only be just above 7000). I've tried to change the scale of my Y axis, but when I am able to so (2 out of 5 times) my data then goes off the chart. So I've looked at m...

Excel opens with an error report whenever I Boot Up my computer
A couple of days ago, I noticed that whenever I boot up my computer, an error report comes up indicating that Excel needs to close because of error. I unistalled office and the message wnet away. But when I reinstalled Office, the message came right back. Thoughts? ...

adding a new record to a table from a form (works in edit mode)
I have a form which shows select field from a table (showing all rows of data), and have 2 buttons , one to edit, one to add. The edit button works perfectly DoCmd.OpenForm "frm_TRA_EditRoute", , , "[anRouteID]=" & Me.Text62.Value, acFormEdit, , True the add button does not. DoCmd.OpenForm "frm_TRA_EditRoute", , , , acFormAdd in edit mode, the form loads up with all of the details (the autonumber correct, the combo boxes drawing in the correct data), however in add mode the autonumber box is just blank (doesn't say "(Autonumber)" like it ...

QuickBooks export to Excel
Suddenly, my QuickBooks program no longer allows me to export to Excel. Worked fine a week ago. I can log onto QB from every other pc in the office, and export to Excel works. Any ideas? Since you are exporting from QB I would start at that end. What trouble-shooting have you done? What message do you get when you try an export from QB? Gord Dibben MS Excel MVP On Fri, 30 Apr 2010 06:41:02 -0700, SecurityGuy <SecurityGuy@discussions.microsoft.com> wrote: >Suddenly, my QuickBooks program no longer allows me to export to Excel. >Worked fine a week ago. I...

Spreadsheet Protection & Sort
Is there a way to protect a spreadsheet (just one column of cells) yet allow for the spreadsheet to be sorted? Thanks for your help in advance. John John, If you want to sort only the unlocked cells of a protected sheet, you can do so with XL2002. In Tools - Protection, you have to option of allowing sort. Not available in XL97, don't know about 2000. In 97, it's generally done with a macro that unprotects the sheet, does the sort, then protects the sheet. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "John"...

Seeking new Bank Recommendation
I am currently a BankOne customer, but am dropping them due to the new ATM service fee. BankOne now charges $1.50 per non-BankOne ATM transaction - this is in addition to what the local bank that runs the ATM charges. I travel A LOT and am always needing cash from a non-BankOne ATM. Unfortunately, BankOne is also the dominant bank in my area - ATMs in Walgreens, Dominicks, lots of branches. I am going to stand on principle and cancel my accounts w/ BankOne however. I was a Preferred Checking customer w/ BankOne for 10 years (no fees for anythign if you kept a $2500 minimum balance - maybe it ...

Using User input in excel VBA
I'm supposed to make an invoice of 4 items. Each item needs to have user input for the description, price, and quantity ordered. Then I need to calculate the cost of the 4 items. Then calculate the sales tax at 6%. Then calculate the total cost including sales tax. This needs to be done using VBA code in excel. --I'm curious as to how to prompt the user to give me information. --I'm curious as to how I would use the users input in another cell once they entered it. -- I'm curious also as to how VB will know that something is entered once the user enters something into t...

Location Location Location? No, Traffic Traffic Traffic! #9
Do you want a great backend product that is selling like crazy this week? My readers are running to get this NEW CONCEPT! I�m selling more this week than I ever did in the past months with any other program. My commissions are climbing very fast. I�m really excited! This is the best ad copy I�ve read in months! It sells. If you haven't felt the shockwave yet, stop everything you are doing and read this to be one of the first promoters. The inner circles of the shockwave are raking in the profits like fury. This will pay us for years to come as it is the first �Wave� of the NEW I...

New computer & moving an upgraded Trial version
i upgraded a Trial version of Money 06 deluxe online; but recently upgraded my computer. i would like to move my .mny files over to the new computer but cannot figure out how to reinstall money 06... since i originally downloaded and then upgraded online i do not have any resource CDs. any suggestions?!? thx. Read more posts for how to reinstall the online-purchased licensed application. This kind of thing happens all too frequently. Move the .MNY files the same way you move any other data file. "chris" <chris@discussions.microsoft.com> wrote in message news:A9E8AF0...

New Mail & Folder Expansion
This is a multi-part message in MIME format. ------=_NextPart_000_0008_01C59B22.A2EECEC0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I have several rules which send new mail messages to sub-folders that = I've created in Outlook. Typically I keep many of these folder trees = collapsed. I'm searching for an Outlook setting or registry edit that = will automatically expand the folder tree to show any folders that = receive a new message. Any suggestions would be appreciated. Kind Regards, --=20 Colin M. McGroarty, MCSE, MCP...

How to set default page for new tag window?
I am using XP with SP3 and IE8. Whenever I click a new tag for IE8, I would like to set the default google page. Does anyone have any suggestions on how to do it? Thanks in advance for any suggestions Eric "Eric" <Eric@discussions.microsoft.com> wrote in message news:52B7E662-E0AD-4460-AC0F-4231721BB564@microsoft.com... >I am using XP with SP3 and IE8. > Whenever I click a new tag for IE8, I would like to set the default google > page. > Does anyone have any suggestions on how to do it? > Thanks in advance for any suggestions Tools||Internet O...

Excel Data Sources
Excel 2007 I have created a standard line chart, which I hoped to represent two different data sets on (x-axis = time, y-axis = %). Data Series 1 plots perfectly. Data Series 2, instead of plotting the second data series, plots Data Series 1 + Data Series 2 (added together). When you add data labelling, it states the % as the correct value, i.e. it says 7.26% but plots the point 5% (-2.26% +7.26%). Rather confused, I cannot determine what I am doing wrong. I'd suggest a fundamental error with excel, but that seems ridiculous. Please help! I suggest that you go back and look caref...

Excel opens 2 copies of the file...
Hi, i did a search with no results, although im sure its been aske before... when i open a file (one we use every month) it all of a sudden decide to start opening 2 copies of it. I couldnt find a setting where i tells it to do this... im sure its a easy fix. Anyone know? Thanks in advance Chri -- johnnyra ----------------------------------------------------------------------- johnnyrad's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=573 View this thread: http://www.excelforum.com/showthread.php?threadid=27288 Hi see: http://www.contextures.com/xlfaqApp.htm...

Import Vendor Bank Info for EFT
I need to know if there is any way possible of importing to GP10 vendor bank account information for EFT Payables. We had to merge one of our GP databases into another shortly after we upgraded to GP10, we have over 200 vendors on the EFT payables program and would like to import the bank info to the EFT tabs instead of hand entering everything. I checked the IM.mdb sample imports and it doesn't look like the new eft bank fields were added to the available fields for import on the vendor sample. I would appreciate anyone's assistance/comments on this. BTW - we had to consol...

New entity to assign to queue...
How can I assign new custom entity to queue? Afraid you can't. Only Activities & Cases can be routed to a queue. -- Matt Parks MVP - Microsoft CRM "Cyrus" <Cyrus@discussions.microsoft.com> wrote in message news:532FAE65-EFF0-43D0-9311-567E35F11C63@microsoft.com... How can I assign new custom entity to queue? Thank you. But is there a way to create new custom Activity entity? "Matt Parks" wrote: > Afraid you can't. Only Activities & Cases can be routed to a queue. > > -- > > Matt Parks > MVP - Microsoft CRM No. be...

How do I sort data in excel by color?
Sorry, English isn’t my mother tongue, hope you’ll be able to understand my question anyway. I have a sheet with four columns of data. Three of them contain numbers and the last one contains a word like the example bellow. In Out Pending Name 1 3 2 Andrew 5 3 7 Mike 3 2 4 Andrew 2 7 1 Mary 4 5 1 Andrew Now, if I want to quickly find all rows containing the word “Andrew” I go to Edit, click Find and click replace and type Andrew into the “Find what” space and Andrew into the “replace with” space. After that I format the fon...

upgrading from one IMac to another
how do I transfer msft office for mac from old IMac to new IMac Probably the easiest way is to connect a firewire cable between the two macs. Start one of them normally. Start the other one up holding while holding down the T key. The hard drive of the one you hold the T key down will mount as a volume on the one that is started normally. Then you can simply drag and drop the files as you need to. -Jim Gordon Mac MVP All responses should be made to this newsgroup within the same thread. Thanks. About Microsoft MVPs: http://www.mvps.org/ Search for help with the free Google search Excel ...

Macro location
I have a file called PERSONAL.XLS that contains all my macros.It is located in STRARTXLS I had reason to copy that file and an other Excel file to drive A(Floppy) Now every time I try to run a macro the computer looks to drive A. How can I stop this ? Thanks -- Norton Virus checker 2003 says this email is clean First off, you should never copy files from Excel to a floppy - it gives too many problems because of the way Excel uses its temporary file. The file can get corrupted. Save to our hard drive and copy from there. The change of location is "a feature not a bug" <grin>...

date in excel #2
Hi, I'm trying to solve a problem... If I put "x" in A1 I would like for excel to set date and time in A2 when I put "x", B1 -> B2, ...., and so on. NOW() does not work because every time I close and open excel it recalculates. Is there a formula to set cell date and time without excel refreshing it on reopen, without using "manual calculations", and possible without macros and VBA? Thanks, MS! In article <j3b35d$1u9$1@sunce.iskon.hr>, MySelf.2001 at REMOVEmyself.2001 @gmail.com says... > > Hi, > I'm trying to solve a problem......