[Solved] Assistance needed in formulating google sheets for desired result.

[ASD]

I own a business where we deliver goods to 150 consumers a day and have to track their online payments. Most of my workload is on google sheets and its not that different that Excel. Each consumer has a unique consumer id and the buying process involves the consumer booking and paying for the goods online and the goods are delivered to them in the next 1-2 weeks. Now we have a team of delivery personnel who deliver the goods to the consumers and bring back the consumer ids who have completed the payment. We verify the payments by extracting the consumer data from our company's online portal which contains the transaction details of all the consumers in the form of an elaborate list. We call it the portal list. We do the cross-verifying on a monthly basis to avoid any discrepancy in payments from the Delivery team and the actual amount of the goods sold. The entries for the month are 4000+.
1. So now to verify we create two lists. One is with all the consumer numbers obtained from the delivery boys (Lets call this list 'DList' ) and the second is the list of consumer ids we get from the portal (Lets call this 'PList' ). I use the following formula to compare the consumer id provided by the DBoy and compare it with the column in the Portal list which contains the Consumer ids we got from the portal.

=IF(ISNA(VLOOKUP(D2, PList!$E$2:$E$5000, 1, FALSE))," NOT RECIEVED", "RECIEVED")

I paste this formula in the DList in which the D column holds the consumer numbers provided by the DBoy. Then I get the list of consumers whose payment has been received and who's is not (The discrepancy over here is that the DBoys might provide a fake number or a wrong one and there is a possibility of theft.)
But this formula fails if the consumer has taken the goods more than once in a month because the formula only compares and returns the value of 'RECIEVED' even if the consumer id matches the PList just once. So to count the number of matches I use the following formula.

=SUMPRODUCT(COUNTIF(D2,PList!$E$2:$E$5000))

This shows me if the consumer id provided by the Dboy has 1 match or >1 and then we can verify the consumers who have got the goods more than once in a month by finding the consumer id in the PList manually (Using Ctrl + F and then pasting the respective consumer id in the Find Popup) and verifying if the consumer id provided by the DBoy is legit.
But this process takes a lot of time and effort for us to complete.

So I wanted a formula that compares the Consumer id in the Dlist with the Column of Consumer id in the PList and then returns the values which match and the values which don't match. I also need the formula to highlight the Row in Plist which shows more than one match with the Consumer IDs in the DList and if possible create a separate table with those highlighted rows in a different table in the same worksheet.

 

[Yousuf Shovon]

Hello ASD,

Thank you for the detailed explanation of the problem. Though it would be more helpful if you could share a demo dataset for a closer look. Based on your explanation, it seems like you're comparing two lists of consumer IDs to verify payments, and you're using the VLOOKUP function and the COUNTIF function to do this. You can also use the following formula to compare the Consumer ID in the DList with the Consumer ID column in the PList, and return the values that match and the values that don't match:
=IFERROR(INDEX(Plist!$B$3:$B$10, MATCH(Dlist!B3, Plist!$B$3:$B$10, 0))&"-MATCHED","NOT MATCHED")
This formula will check if the Consumer ID in the DList appears in the Consumer ID column of the PList, and return MATCH if it does, and NO MATCH if it doesn't.
To count the number of matches I have used the formula same as yours:
=SUMPRODUCT(COUNTIF(A2,Plist!$B$3:$B$10))
To highlight the rows in the PList that show more than one match with the Consumer IDs in the DList, you can use conditional formatting. Here's how:

• Initially, select the entire Consumer ID column in the PList.
• Afterward, go to Home > Conditional Formatting > Highlight Cell Rules > Mores Rules.
• Subsequently, the New Formatting Rule dialog box appears.
• Tap the option Use a formula to determine which cells to format.
• Now, type the following formula in the Format values where this formula is true box:

=COUNTIF(DList!$B$3:$B$11,B3)>1

• Further, click on Format > Fill to customize the cells as you wish.

I have also attached an Excel file for a better understanding. Hope this works for you.

Regards,
Yousuf Shovon