Compare two different tables with same rows but not exactly in the same order in Excel

Question

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?

Answer 1

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)

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.

---

And when one cell does not match:

Answer 2

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.