Comparing Tables

[irisqueiroz]

Hi!


I have 2 tables to compare... The table 1 is the newest and the 2 the older. Both have 4 columns (A:D). The logic is to compare each line of the table:

- If there is a different line in table 1 compareng with table 2, then set this line blue

- If there is a different line in table 2 compareng with table 1, then set this line red


How can I code it?


Thanks.

 

[Sajan]

Hello,


Are both tables of the same length?

Are you wanting to compare the corresponding row in each table? (i.e. compare row1 of table1 to row1 of table 2, row2 of table1 to row2 of table2, etc.?)

If you answered yes to the above questions, then you should be able to do a simple comparison such as the following:

=SUMPRODUCT(N(A1:D1=H1:K1))=4


(In this sample formula, table 1 row1 is in A1:D1, and table2 row1 is in H1:K1)


On the other hand, if the tables are of different sizes (i.e. different number of rows), and a given row could be matched on any other row in the second table, then a different approach is needed to identify matches or non-matches.


As such, please describe your problem further.


Cheers,

Sajan.

 

[irisqueiroz]

anyone?

 

[irisqueiroz]

Sajan, my problem is exactly the second case...


As I said I have 2 tables, one of month (t-1) and the other of month t. I need to know, in month t, what are the new "lines" (A:D) and witch "lines" in month (t-1) are not in month t table anymore.

 

[Sajan]

Hello,

For ease of reference in the formula, I have assumed that your first table (month=t-1) is named "Table1", and your second table is named "Table2".


In my sample setup, Table1 was in the range A2:D5

Table2 was in the range H2:K6


Put the following formula in cell E2:

=SUMPRODUCT(N(MMULT(N(Table2=A2:D2),{1;1;1;1})=4))>0


Copy down to additional rows


Put the following formula in cell L2:

=SUMPRODUCT(N(MMULT(N(Table1=H2:K2),{1;1;1;1})=4))>0


Copy down to additional rows


My sample data setup, for your reference:

Table1

[pre]

Col1	Col2	Col3	Col4	Match?
A	B	C	D	TRUE
B	F	G	H	TRUE
I	J	K	L	TRUE
M	N	O	P	FALSE
Table2

Col1	Col2	Col3	Col4	Match?
A	B	C	D	TRUE
E	X	G	H	FALSE
I	J	K	L	TRUE
M	N	X	P	FALSE
B	F	G	H	TRUE

[/pre]
Instead of the helper column, you could put the formula directly into a conditional format to color lines as blue, red, etc.


Cheers,

Sajan.