Forum Discussion

Dan1ExcelUser's avatar
Dan1ExcelUser
Copper Contributor
Sep 18, 2025
Solved

Which formula to use to compare 2 spreadsheets that have 6 columns?

Hi,

I have 2 spreadsheet, A and B, and both have 6 columns, like the table below, and more than 7k rows (6 x 7k)

ABCDEF
BankBank CodeCountryBank BranchBank CodeSWIFT Code
Value 1Value 2Value 3Value 4Value 5Value 6

I would like to compare spreadsheet A and B to find which values is missing into B. And, I assume that SheetA!Value1=SheetB!Value1, SheetA!Value2=SheetB!Value2,..,SheetA!Value6=SheetB!Value6

I tried this formula IFERROR(IF(MATCH(1,(A:A=SheetB!A:A)*(B:B=SheetB!B:B)*(C:C=SheetB!C:C)*(D:D=SheetB!C:C)*(E:E=SheetB!E:E)*(F:F=SheetB!F:F),0),"Added"),"Missing").

The problem with this formula it's take a lot time to give a result. My spreadsheets have  Excel is very slow and my PC is freezing.

So, I don't know if this formula is good nor if there is another one that won't slow down my PC.

Best regards

  • How about using a Helper Column + Concatenation

    Instead of comparing each column individually, combine all six columns into one string per row and then compare those strings. This drastically reduces the complexity.

    Step 1: Create a Helper Column in Both Sheets

    In SheetA, insert a new column (say, column G) and use this formula in G2:

    =A2 & "|" & B2 & "|" & C2 & "|" & D2 & "|" & E2 & "|" & F2

    Do the same in SheetB, also in column G.

    The | symbol is just a separator to avoid accidental matches (e.g., "Bank1Code2" vs. "Bank1|Code2").

     

    Step 2: Use COUNTIF to Compare

    In SheetA, next to your helper column (say, column H), use:

    =IF(COUNTIF(SheetB!G:G, G2) > 0, "Match", "Missing")

    This checks whether the combined row from SheetA exists in SheetB.

1 Reply

  • How about using a Helper Column + Concatenation

    Instead of comparing each column individually, combine all six columns into one string per row and then compare those strings. This drastically reduces the complexity.

    Step 1: Create a Helper Column in Both Sheets

    In SheetA, insert a new column (say, column G) and use this formula in G2:

    =A2 & "|" & B2 & "|" & C2 & "|" & D2 & "|" & E2 & "|" & F2

    Do the same in SheetB, also in column G.

    The | symbol is just a separator to avoid accidental matches (e.g., "Bank1Code2" vs. "Bank1|Code2").

     

    Step 2: Use COUNTIF to Compare

    In SheetA, next to your helper column (say, column H), use:

    =IF(COUNTIF(SheetB!G:G, G2) > 0, "Match", "Missing")

    This checks whether the combined row from SheetA exists in SheetB.

Resources