Comparative Formulas

[Michele]

I'm trying to compare 3 tables (same fields) to check and see if any data is
different. The layout is the same only the data will be different (Base
Table, Current Table, New Table)

Table 1: Base
Family Sub Grade TMI TMI50
1 229 23 1 $70,500
1 229 23 3 $75,000
3 450 22 1 $185,000
5 220 24 3 $160,000

Table 2: Current
Family Sub Grade TMI TMI50
1 229 23 1 $70,500
1 229 23 3 $75,000
3 450 22 1 $185,000
5 220 24 3 $160,000

Table 3: New
Family Sub Grade TMI TMI50
1 229 23 1 $70,500
1 228 23 3 $75,000
3 450 22 1 $185,000
5 220 25 3 $164,000

I want to run a comparison that will compare every row and "point out"
whatever differences there maybe. I know there are some because I have the
same exact number of rows in Base and Current, but about 50 more in the New
table. Any thoughts?

Thanks!
Michele

[CLR]

I use a commercial Add-in from Jim Cone, called XLCompanion to perform tasks
like this. It works super and gives a detailed account of each difference.

Vaya con Dios,
Chuck, CABGx3



"Michele" wrote:

> I'm trying to compare 3 tables (same fields) to check and see if any data is
> different. The layout is the same only the data will be different (Base
> Table, Current Table, New Table)
>
> Table 1: Base
> Family Sub Grade TMI TMI50
> 1 229 23 1 $70,500
> 1 229 23 3 $75,000
> 3 450 22 1 $185,000
> 5 220 24 3 $160,000
>
> Table 2: Current
> Family Sub Grade TMI TMI50
> 1 229 23 1 $70,500
> 1 229 23 3 $75,000
> 3 450 22 1 $185,000
> 5 220 24 3 $160,000
>
> Table 3: New
> Family Sub Grade TMI TMI50
> 1 229 23 1 $70,500
> 1 228 23 3 $75,000
> 3 450 22 1 $185,000
> 5 220 25 3 $164,000
>
> I want to run a comparison that will compare every row and "point out"
> whatever differences there maybe. I know there are some because I have the
> same exact number of rows in Base and Current, but about 50 more in the New
> table. Any thoughts?
>
> Thanks!
> Michele

[Michele]

Chuck,

Where would I find this add-in? Thanks!

Michele

"CLR" wrote:

> I use a commercial Add-in from Jim Cone, called XLCompanion to perform tasks
> like this. It works super and gives a detailed account of each difference.
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
>
> "Michele" wrote:
>
> > I'm trying to compare 3 tables (same fields) to check and see if any data is
> > different. The layout is the same only the data will be different (Base
> > Table, Current Table, New Table)
> >
> > Table 1: Base
> > Family Sub Grade TMI TMI50
> > 1 229 23 1 $70,500
> > 1 229 23 3 $75,000
> > 3 450 22 1 $185,000
> > 5 220 24 3 $160,000
> >
> > Table 2: Current
> > Family Sub Grade TMI TMI50
> > 1 229 23 1 $70,500
> > 1 229 23 3 $75,000
> > 3 450 22 1 $185,000
> > 5 220 24 3 $160,000
> >
> > Table 3: New
> > Family Sub Grade TMI TMI50
> > 1 229 23 1 $70,500
> > 1 228 23 3 $75,000
> > 3 450 22 1 $185,000
> > 5 220 25 3 $164,000
> >
> > I want to run a comparison that will compare every row and "point out"
> > whatever differences there maybe. I know there are some because I have the
> > same exact number of rows in Base and Current, but about 50 more in the New
> > table. Any thoughts?
> >
> > Thanks!
> > Michele

[Roger Govier]

Hi Michele

This method would at least identify which row and column to look at in the
three tables. I am assuming the tables are different sheets, and the data
apart from headers is all numeric.

Insert a new sheet and in cell A2 enter
=IF(SUM(Sheet1:Sheet3!A2)=3*A2,"",ERROR")
Copy across the width of your data. Copy down as far as the maximum number
of rows as you have on the biggest sheet.

Wherever ERROR occurs, you need to check the values in the 3 sheets.



Regards

Roger Govier


Michele wrote:
> I'm trying to compare 3 tables (same fields) to check and see if any data is
> different. The layout is the same only the data will be different (Base
> Table, Current Table, New Table)
>
> Table 1: Base
> Family Sub Grade TMI TMI50
> 1 229 23 1 $70,500
> 1 229 23 3 $75,000
> 3 450 22 1 $185,000
> 5 220 24 3 $160,000
>
> Table 2: Current
> Family Sub Grade TMI TMI50
> 1 229 23 1 $70,500
> 1 229 23 3 $75,000
> 3 450 22 1 $185,000
> 5 220 24 3 $160,000
>
> Table 3: New
> Family Sub Grade TMI TMI50
> 1 229 23 1 $70,500
> 1 228 23 3 $75,000
> 3 450 22 1 $185,000
> 5 220 25 3 $164,000
>
> I want to run a comparison that will compare every row and "point out"
> whatever differences there maybe. I know there are some because I have the
> same exact number of rows in Base and Current, but about 50 more in the New
> table. Any thoughts?
>
> Thanks!
> Michele