SOLVED

Is there a function to find differences between two tables on different worksheets?

Copper Contributor

I'm doing this for a college assignment, and I've been racking my brain trying to figure this out.

I have two nearly identical tables and I am required to use conditional formatting to show which values are not identical.

 

Pretty sure I need to use a formula to find them but am completely lost on how to write the formula and on what function to use.

 

I need help ASAP.

2 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

@heitle 

You can use the IF function along with the VLOOKUP or IFERROR function to compare values in two tables across different worksheets in Excel. Here is a step-by-step guide:

Let us assume you have two tables on different worksheets, and you want to highlight the cells with differences.

Open the worksheet where you want to highlight the differences.

Select the first cell where you want to apply conditional formatting.

Go to the "Home" tab on the Ribbon, click on "Conditional Formatting," and choose "New Rule."

Select "Use a formula to determine which cells to format."

Enter the following formula:

=IF(ISERROR(VLOOKUP(A1, Sheet2!$A$1:$A$100, 1, FALSE)), TRUE, FALSE)

This formula assumes that you are comparing values in column A of the current sheet with values in column A of "Sheet2." Adjust the column references as needed.

Alternatively, you can use IFERROR to make it more concise:

=IFERROR(MATCH(A1, Sheet2!$A$1:$A$100, 0), 0) = 0

 

Set the format for cells that meet the condition (e.g., a different background color).

Click "OK" to apply the conditional formatting.

Now, the selected cells will be highlighted if the values are present in the first table but not in the second.

Repeat the process for the reverse case (values in the second table but not in the first) if needed.

Remember to adjust cell references and ranges based on your actual data and tables.

This approach assumes that your tables are in columns (or rows) and that you are comparing values in a single column. If your tables are more complex, the formula may need adjustments.

The text and steps were edited with the help of AI.

 

My answers are voluntary and without guarantee!

 

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.

1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

@heitle 

You can use the IF function along with the VLOOKUP or IFERROR function to compare values in two tables across different worksheets in Excel. Here is a step-by-step guide:

Let us assume you have two tables on different worksheets, and you want to highlight the cells with differences.

Open the worksheet where you want to highlight the differences.

Select the first cell where you want to apply conditional formatting.

Go to the "Home" tab on the Ribbon, click on "Conditional Formatting," and choose "New Rule."

Select "Use a formula to determine which cells to format."

Enter the following formula:

=IF(ISERROR(VLOOKUP(A1, Sheet2!$A$1:$A$100, 1, FALSE)), TRUE, FALSE)

This formula assumes that you are comparing values in column A of the current sheet with values in column A of "Sheet2." Adjust the column references as needed.

Alternatively, you can use IFERROR to make it more concise:

=IFERROR(MATCH(A1, Sheet2!$A$1:$A$100, 0), 0) = 0

 

Set the format for cells that meet the condition (e.g., a different background color).

Click "OK" to apply the conditional formatting.

Now, the selected cells will be highlighted if the values are present in the first table but not in the second.

Repeat the process for the reverse case (values in the second table but not in the first) if needed.

Remember to adjust cell references and ranges based on your actual data and tables.

This approach assumes that your tables are in columns (or rows) and that you are comparing values in a single column. If your tables are more complex, the formula may need adjustments.

The text and steps were edited with the help of AI.

 

My answers are voluntary and without guarantee!

 

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.

View solution in original post