Search code examples
excelexcel-formulaexcel-tables

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

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?

Solution

  • 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