1

I want to compare two different tables in Excel if they have exactly the same rows [number of rows and their fields] but the rows may not be necessarilly in the same order, say:

col1---col2---col3---col4                  col1---col2---col3---col4
1      10     15      2                    3      30     13     6
2      20     14      4                    4      40     12     8
3      30     13      6                    1      10     15     2
4      40     12      8                    2      20     14     4

         TABLE_1                                    TABLE_2

I call this equivalent tables.

Is there an excel function, or combination of functions, that can give me TRUE if both tables are equivalent, and FALSE if not?

Vega
  • 27,856
  • 27
  • 95
  • 103
MSnts
  • 13
  • 2

2 Answers2

3

You can put two helper columns in the tables with the following formula:

=SUMPRODUCT(--(A2:D2=INDEX(G:J,MATCH(A2,G:G,0),0)))=COLUMNS(A:D)

and 

=SUMPRODUCT(--(G2:J2=INDEX(A:D,MATCH(G2,A:A,0),0)))=COLUMNS(G:J)

enter image description here

Then count those helper columns for FALSE:

=AND(COUNTIF(E:E,FALSE)=0,COUNTIF(K:K,FALSE)=0)

This will return FALSE if any of the rows return false.

enter image description here

And when one cell does not match:

enter image description here

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • Hi Scott, thank you so much, was of much help, but in my real case, I have columns with CHAR values, will this still work?? – MSnts Sep 08 '17 at 20:56
  • yes, there is nothing in the formula that would make it dependent on numbers or characters. – Scott Craner Sep 08 '17 at 20:58
1

You may go around it with the following shortcut, which is not 100% sure but very high possibility of being sure. but it is extremely easy to apply.

first, format your tables as real Tables. Home enu>Format as Table then, write this formula: =SUM(Table1)=SUM(Table2) it would be very little probability for them to be equal if they are not the same.

Volkan Yurtseven
  • 425
  • 3
  • 15
  • Thanks ExcelinEfendisi, but I need to be as precise as possible, and, also, in real cases, I handle CHAR values. – MSnts Sep 08 '17 at 20:58
  • then just add COUNT and AVERAGE as well, like "=(SUM(Table1)+COUNT(Table1)+AVERAGE(Table1))=(SUM(Table2)+COUNT(Table2)+AVERAGE(Table2))" . This will make it more precise :) – Volkan Yurtseven Sep 08 '17 at 21:04