Share via

Compare 2 columns within 2 different excell sheets.

Anonymous
2024-04-17T14:48:34+00:00

I need to compare 2 massive (500,000 lines) excell sheet that contains COLUMN A and COLUMN B to identify differences. (column A from file 1 we'll call 1A, column A from file 2 we'll call 2A and cell A1 from file 1 is 1A1)

Considering that 1A and 2A should contain the same information but in different order, I need to be able to compare:

IF 1A1 = 2Ax then compare 1B1 with 1Bx and give result. If 1B1 different than 1Bx write 1 (or false, or whatever). If 1B1 equal 1Bx then write 0 (or true, or whatever)

Where X is an unknown.

How do I Do that optimally?

Microsoft 365 and Office | Excel | Other | MacOS

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2024-04-17T15:30:21+00:00

    Hello,

    To compare two columns within two different Excel sheets optimally, you can use Excel formulas or scripts. Here's a step-by-step guide using Excel formulas:

    Open both Excel sheets. In a new column in one of the sheets, let's say Sheet1, enter the following formula in the first row (assuming data starts from row 1):

    =IF(VLOOKUP(A1, Sheet2! A:A, 1, FALSE) = A1, IF(B1 = VLOOKUP(A1, Sheet2! A:B, 2, FALSE), "0", "1"), "Not Found")

    This formula checks if the value in cell A1 of Sheet1 exists in column A of Sheet2. If it does, it compares the corresponding value in column B of Sheet1 with the value in column B of Sheet2. If they are the same, it returns "0", otherwise "1". If the value in cell A1 of Sheet1 is not found in Sheet2, it returns "Not Found". Drag the formula down to apply it to all rows in your sheet.

    Hope this helps!

    Warm Regards, Ozi

    4 people found this answer helpful.
    0 comments