Ask Your Question

Compare values between two sheets

asked 2018-09-02 13:05:30 +0100

Roihan gravatar image

updated 2018-09-02 13:29:47 +0100


I have a calc document contains two sheets, both sheets contain same columns: ID, DEPT, PRICE I need to compare the PRICE for each ID on both sheets. Example, for ID 001, DEPT A.01, the PRICE on sheet1 is 500. I want to compare it with it's PRICE on sheet2. If they are different, I need to mark it (maybe set the font to bold or the background color to red). There are nearly 26000 rows, so it will take too long to do it manually. Is there any way (a formula or something) that I can use to do it efficiently?

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2018-09-02 13:41:46 +0100

ebot gravatar image

updated 2018-09-02 14:55:53 +0100

Example: The first value is always in "A3". In Table 1, use an auxiliary column, e.g. "B". Type in "B3": "= WENN(A3 <> $ table2.A3; 1; 0)". If both values are equal, "0" (zero) is displayed. If they are unequal, "1" is displayed. You can now mark cell "B3". Hold the small black square on the lower right cell with the mouse and pull down. So we copied the formula down.

WENN is in english-UI IF. Tabelle is table.

edit flag offensive delete link more


I think someone might have a better solution.

ebot gravatar imageebot ( 2018-09-02 14:04:53 +0100 )edit

answered 2018-09-02 14:09:23 +0100

Roihan gravatar image

What if their positions are not same This is the situation : The first sheet contains all products while the second sheet contains the sold products in a month. So, ID 001 could appear more than once in sheet2. I need to make sure if every ID is sold with the correct price based on the PRICE column in sheet1.

edit flag offensive delete link more


When asking a question, you should reveal all relevant information in order to get a satisfactory answer.

ebot gravatar imageebot ( 2018-09-02 14:13:48 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-09-02 13:05:30 +0100

Seen: 77 times

Last updated: Sep 02 '18