Hi everyone. I have a question. By the way, I am using Excel 2010. I have a spreadsheet with some orders (usually it has several hundred rows but for simplicity I am attaching one with 10 rows only). As you can see, it has the name of the customer, their email address, the date they placed the order, if the package was sent or cancelled, and what happened with it. Some customers had to place 2 or 3 orders for the same item, since the initial order(s) didn't go through for some reason.
What I am trying to do is to gather the email addresses of ONLY the customers that never received their package so I can contact them and take care of it one way or another. I could go and check one by one and gather each email but as I mentioned, with hundreds of rows it will be very time consuming.
Is there a way I can enter a formula on column G for each row to only display the email address if that customer has NOT received the product they ordered? The challenge is that there are 2 or 3 rows for the same customer so the normal =IF(F2="Package delivered","",B2) would not work because it will display the email address on G3 for Matt but Matt did get his package when he placed the order a few days later on Row 4. The end result should only give me the email addresses of Chris and Bob on cells G8, G9 and G10 respectively (it doesn't matter if G8 gives the email too. I don't mind duplicate emails as long as they are from customer who never received the product)
The other challenge is that there are 2 ways a customer can get the product, Based on the status, the way to know that the customer got the product is if it says "Package Delivered" or "Picked up at store".
I was thinking maybe a VLOOKUP function that searches for different rows. It would need to eliminate John, Matt and Steve for example from displaying their email addresses on column G since all three of them got their packages. Or maybe some other function. I am not very good a coding on VBA so I am trying to stay away from that if possible.
Thank you in advance.
Bookmarks