Forum Discussion
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)
A | B | C | D | E | F |
Bank | Bank Code | Country | Bank Branch | Bank Code | SWIFT Code |
Value 1 | Value 2 | Value 3 | Value 4 | Value 5 | Value 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.