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