Hey, besties! Today, I’m going to spill a little secret on how to easily compare data in two Google Sheets or columns. You know those moments when you need to double-check to avoid errors or fraud? Well, this trick is going to make your life a whole lot easier!
The Scenario
Let’s imagine you own a delivery business and need to track online payments from your 150 daily customers. Each customer has a unique identifier, and the purchasing process involves an online booking followed by a payment. Your delivery drivers collect the customer identifiers who made the payment upon delivery. However, to ensure payment compliance, you need to compare these identifiers with the data recorded in your online portal.
The Solution
To compare the data, you can use a simple yet powerful formula on Google Sheets. Here’s how it works:
-
Create two lists: one for the customer identifiers provided by your drivers (let’s call it “DList”) and one for the customer identifiers from your online portal (let’s call it “PList”).
-
In column D of the DList, paste the following formula:
=IF(ISERROR(VLOOKUP(D2, PList!$E$2:$E$5000, 1, FALSE)), "NOT RECEIVED", "RECEIVED")This formula searches for the customer identifier provided by the driver in the column of identifiers in the PList. If the identifier matches, the formula displays “RECEIVED”; otherwise, it displays “NOT RECEIVED”.
-
To count the number of matches, use the following formula:
=SUMPRODUCT(COUNTIF(D2, PList!$E$2:$E$5000))This formula counts how many times the customer identifier provided by the driver appears in the PList. This way, you can easily check if a customer has taken goods more than once in a month.
The Magic Happens!
Now that you have these formulas in place, you can quickly verify payments and detect any irregularities. But wait, that’s not all!
If you want to highlight the rows in the PList that match more than one customer identifier from the DList, follow these additional steps:
-
Select the column of customer identifiers in the PList.
-
Go to the “Format” menu and select “Conditional formatting”.
-
Choose “More rules” and select “Custom formula”.
-
In the “Custom formula” field, paste the following formula:
=COUNTIF($E:$E, $E2)>1 -
Select the highlighting format you prefer and apply it.
Now, the rows in the PList that match more than one customer identifier from the DList will be automatically highlighted!
Oh, and I forgot to mention… You can also create a separate table in the same sheet to display the highlighted rows.
And there you have it, my friends! You now have all the keys to easily compare data in two Google Sheets or columns. No need to spend hours doing it manually anymore. You can save time and precious effort!
For more tips and advice on Google Sheets, be sure to check out Crawlan.com. You’ll find additional resources to make your life easier.
See you soon for more awesome tips!
