I have multiple tables that use the same ID values but have different items and descriptions assigned to them. The ID order is fixed and same in all tables. What I'd like to do is find out which table rows are the same across all tables. A B C D E F G H I 1 H1 H2 H3 H1 H2 H3 H1 H2 H3 -- -- -- -- -- -- -- -- -- 2 1 a a+ 1 a a+ 1 c c+ FALSE 3 2 b b+ 2 b b+ 2 b b+ TRUE 4 3 c c+ 3 x x+ 3 a a+ FALSE H1 = ID, same values and order in all tables H2 = item; order varies by table H3 = item description; items & descriptions come in fixed pairs What I've done so far is placed them next to each other, and I'm using the following formula in the last column: =SUMPRODUCT(ABS(COUNTIF(A2:I2; A2:I2) - 3)) = 0 COUNTIF returns the array of the entire multi-table row containing the number of occurrences of each cell's value within that same row. For the three tables in the example, that will be three dupes of each cell per row, or [3,3,3, 3,3,3, 3,3,3] . The -3 part zeroes out the array, [0,0,0, 0,0,0, 0,0,0] , for rows with matching table values. ABS drops the minuses from any potential negative numbers in the array caused by the previous step. This makes sure that, in the last step, only the sums of duped-row arrays can equal zero, while all the other arrays will result in a value >0. SUMPRODUCT sums the array and returns a single value that can then be compared to zero, which the second step has assured means that all the tables' values in the current row match. (Actually, a simple SUM is a more straightforward choice, but for some reason, unlike SUMPRODUCT , it requires Ctrl+Shift+Enter when entering the formula). Is there a simpler formula or layout I can use to deal with this problem?

Reputation: 230

Check if multiple cell ranges are equal

I have multiple tables that use the same ID values but have different items and descriptions assigned to them. The ID order is fixed and same in all tables.

What I'd like to do is find out which table rows are the same across all tables.

     A  B  C     D  E  F     G  H  I

1    H1 H2 H3    H1 H2 H3    H1 H2 H3
     -- -- --    -- -- --    -- -- --
2    1  a  a+    1  a  a+    1  c  c+    FALSE
3    2  b  b+    2  b  b+    2  b  b+    TRUE
4    3  c  c+    3  x  x+    3  a  a+    FALSE

H1 = ID, same values and order in all tables
H2 = item; order varies by table
H3 = item description; items & descriptions come in fixed pairs

What I've done so far is placed them next to each other, and I'm using the following formula in the last column:

=SUMPRODUCT(ABS(COUNTIF(A2:I2; A2:I2) - 3)) = 0

COUNTIF returns the array of the entire multi-table row containing the number of occurrences of each cell's value within that same row. For the three tables in the example, that will be three dupes of each cell per row, or [3,3,3, 3,3,3, 3,3,3].
The -3 part zeroes out the array, [0,0,0, 0,0,0, 0,0,0], for rows with matching table values.
ABS drops the minuses from any potential negative numbers in the array caused by the previous step. This makes sure that, in the last step, only the sums of duped-row arrays can equal zero, while all the other arrays will result in a value >0.
SUMPRODUCT sums the array and returns a single value that can then be compared to zero, which the second step has assured means that all the tables' values in the current row match. (Actually, a simple SUM is a more straightforward choice, but for some reason, unlike SUMPRODUCT, it requires Ctrl+Shift+Enter when entering the formula).

Is there a simpler formula or layout I can use to deal with this problem?

Upvotes: 1

Answers (3)

Error 1004

Reputation: 8230

Try:

=IF(AND(A2=D2,D2=G2,B2=E2,E2=H2,C2=F2,F2=I2),"Full Match","Mismatch")

Results:

Upvotes: 0

Solar Mike

Reputation: 8375

So, compare the three pairs like this with AND():

Upvotes: 1

ttaaoossuu

Reputation: 7894

In my work I use this solution. Perhaps, it's not perfect but viable. Just compare concatenated values:

=A2&B2&C2
=D2&E2&F2
=G2&H2&I2

Upvotes: 0

Check if multiple cell ranges are equal

Answers (3)

Related Questions