The views expressed in this blog are those of the owner and do not reflect the views or opinions of the owner’s employer. All content provided on this blog is for informational purposes only. The owner of this blog makes no representations as to the accuracy or completeness of any information on this site or found by following any link on this site. The owner will not be liable for any errors or omissions in this information nor for the availability of this information. The owner will not be liable for any losses, injuries, or damages from the display or use of this information. This policy is subject to change at any time. The owner is not an attorney, and nothing posted on this site should be construed as legal advice. Litigation Support Tip of the Night does not provide confirmation that any e-discovery technique or conduct is compliant with legal, regulatory, contractual or ethical requirements.
A free version of the Synkronizer add-in for Excel is available for download here. It can be used to detect differences in two Excel workbooks. The trial version only covers the range from A1:Z100.
As you can see in this example you can select individual worksheets to compare can specific cell ranges:
Synkronizer provides you with a list of the cells that have differences, and also highlights the different cells as well.
The good folks at Extend Office have posted a formula which can be used to run a VLOOKUP formula on multiple Excel worksheets simultaneously.
The formula is an array formula and looks like this:
=VLOOKUP(C1,INDIRECT("'"&INDEX(baseball,MATCH(1,--(COUNTIF(INDIRECT("'"&baseball&"'!$A$1:$T$200"),C1)>0),0))&"'!$A$1:$T$200"),6,FALSE)
. . . array formulas should be entered in a cell while you press CTRL + SHIFT so that brackets that appear at both ends.
In this example we are working with pitching statistics for the 1915 baseball season. The data for the American, Federal and National leagues are on three separate worksheets. We want to find the number of wins that Jeff Pfeffer (we know his code name is pfeffje01) got in 1915, but we don't know which league he pitched in.
The first step is to give the worksheets a name range. In this example the three worksheet names are listed in column A and we give them the name 'baseball'. Just select the cells with the worksheet names and enter the name in the box on the left of the toolbar.
In the array formula we refer to the range name twice, the value we're searching for at the beginning after 'VLOOKUP(' and then again in the middle. The 6 at the end represents the value in the array that we to pull. The number of wins is listed in the sixth column.
We're searching the same cell range on each worksheet A1:T200, and the value we're looking for must appear in column A of one of the worksheets.
If you're editing a column of data in an Excel worksheet and you want to keep track of which was the last cell that you edited, use the CELL formula. Just type in =CELL("address"), and it will automatically update with the location of the most recently edited cell any time a change is made.
