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 Co...
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.