It’s not easy to find a one-size-fits-all solution for comparing tables and removing duplicates in Google Sheets. The requirements can vary from person to person. But don’t worry, I’ve got a formula that can help you out!
Comparing Two Tables and Removing Duplicates
Imagine you have two tables in a Google Sheets file. One table contains the invoice raised, and the other table contains the payment received details. You want to match the invoice numbers and remove the duplicates. Here’s how you can do it.
Sample Data for Comparing Two Tables and Removing Duplicates
Let’s start by preparing some sample data. In the “ACCOUNTS RECEIVABLE” tab, you have a list of records. In the “PAYMENT COLLECTED” tab, you have just one record. Take a look at the tables to understand better.
As you can see, the invoice number “II000025” appears in both tables. We can handle this in two ways!
- Remove all the occurrences of the matching rows.
- Keep only one record of the matching row.
Formulas for Comparing Two Tables and Removing Duplicates
1. Removing All Occurrences of the Matching Row
If you want to remove all the occurrences of the matching rows, you can use the following formula:
=UNIQUE(VSTACK('ACCOUNTS RECEIVABLE'!A2:D,'PAYMENT COLLECTED'!A2:D),,TRUE)The VSTACK function combines both tables vertically, and the UNIQUE function returns the distinct rows. Alternatively, you can use the QUERY formula as well:
=QUERY(QUERY(VSTACK('ACCOUNTS RECEIVABLE'!A1:D,'PAYMENT COLLECTED'!A2:D),"SELECT Col1,Col2,Col3,Col4, COUNT(Col1) WHERE Col1<>'' GROUP BY Col1,Col2,Col3,Col4"),"SELECT Col1,Col2,Col3,Col4 WHERE Col5=1")The QUERY formula groups all the columns in the table and returns the count of records. The outer QUERY filters out any rows that have a count of greater than 1 in the last column.
2. Keeping the First Occurrence
If you want to keep only the first occurrence of the matching rows, you can use the following formulas:
Matching Total Row (method B):
=LET(ftr,UNIQUE(VSTACK('ACCOUNTS RECEIVABLE'!A2:D,'PAYMENT COLLECTED'!A2:D)),FILTER(ftr,CHOOSECOLS(ftr,1)<>""))The UNIQUE function removes duplicates, and the LET function filters out any blank rows in the result.
Matching Invoice Number (method A):
=LET(ftr,SORTN(VSTACK('ACCOUNTS RECEIVABLE'!A2:D,'PAYMENT COLLECTED'!A2:D),9^9,2,1,1),FILTER(ftr,CHOOSECOLS(ftr,1)<>""))The SORTN function is used instead of UNIQUE, and it selects the column for matching (in this case, the invoice column).
That’s all you need to know about comparing two tables and removing duplicates in Google Sheets. Give it a try and make your data more organized and accurate!
To learn more about Google Sheets and its powerful features, visit Crawlan.com for informative content and helpful tips.
Happy sheeting!
