+ Reply to Thread
Results 1 to 5 of 5

help with funtion to search for information, maybe VLOOKUP?

  1. #1
    Registered User
    Join Date
    06-04-2014
    Posts
    7

    help with funtion to search for information, maybe VLOOKUP?

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

  2. #2
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: help with funtion to search for information, maybe VLOOKUP?

    Put this in I2, then drag down:
    =IF((COUNTIFS($B$2:$B$10,B2,$F$2:$F$10,"P*")>0),"",B2)

    Note, it assumes all of the delivered packages texts begin with a 'P' and the others do not begin with a 'P'. Maybe that's a safe assumption, maybe it isn't. A problem I foresee is that if a person ever had any package delivered, then this will not show their name if another intended package was not delivered. In other words, if 'Joe' orders an item and it is delivered, then a week later Joe orders another package but it is not delivered, this formula would not highlight Joe (since he had the first package delivered a week ago).
    Pauley
    --------
    If I helped with your issue, I'd appreciate a rep bump (hit the '*' icon to the bottom left of this post).

  3. #3
    Forum Contributor
    Join Date
    01-25-2014
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    434

    Re: help with funtion to search for information, maybe VLOOKUP?

    there is a problem with the examples you've provided

    row 8 = package sent & cancelled - package returned
    row 10 = package sent & cancelled - package returned

    but in your expected results you are not interested in row 8, only row 10

  4. #4
    Registered User
    Join Date
    06-04-2014
    Posts
    7

    Re: help with funtion to search for information, maybe VLOOKUP?

    Very impressive solution Pauleyb. I tried to transfer the same concept to my spreadsheet but I didn't realize my spreadsheets are more complex than the example I sent initially.

    I have attached a spreadsheet that is more similar with what I deal with everyday. When I create the formula to show the email addresses on column F for the people who never received the order somehow it gives me all the addresses. I am not sure why. maybe because of the "Status" column (column C), which some of them have blank spaces, I am not sure. Also, more than 2 results determine that the customer received the order: "Order Complete" and "Pending Delivery", so doing the P* option didn't work in this case (although it was very clever). How would you suggest adjusting the formula so it gives the same result like column H, which I did manually?

    Thank you for your help.
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: help with funtion to search for information, maybe VLOOKUP?

    Similar technique, put in H2 and drag down:
    =IF(COUNTIFS($A$2:$A$48,A2,$C$2:$C$48,"Order Complete")+COUNTIFS($A$2:$A$48,A2,$C$2:$C$48,"Pending Delivery")>0,"",D2)
    The above is similar to my first attempt until I noticed the (inappropriate) 'P' trick.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. function to add a information to a other sheet
    By benjamin.grimm in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-10-2013, 06:09 AM
  2. [SOLVED] If Search funtion = one cell over
    By imogul in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-29-2013, 02:49 PM
  3. [SOLVED] Correct syntax to have the find funtion search for whatever is in cell B2
    By geshorse in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 11-06-2012, 06:58 PM
  4. Creating an input box to use as a search funtion
    By danialw in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-13-2009, 08:51 AM
  5. [SOLVED] search funtion
    By steve in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-15-2005, 11:55 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1