+ Reply to Thread
Results 1 to 2 of 2

Excel formula to find missing customers from an actual sheet

  1. #1
    Registered User
    Join Date
    07-06-2015
    Location
    India
    MS-Off Ver
    PC 2016
    Posts
    87

    Excel formula to find missing customers from an actual sheet

    Hi Friends,

    I have an excel sheet where I am calculating the Actual and forecasted hours for resources.

    The challenge is that I have a set of customers for which forecasting was done but when it comes to actual there were more customers for which resources worked and entered hours as well.

    So If I will check the no of actual customers for which a resource worked it will take a very longtime.Please help me witha formula which can be used to extract the set of new customers which were not in forecast but are in actual sheet.

    Reagrds,

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Excel formula to find missing customers from an actual sheet

    well let's say you have a list of customers in Sheet1 column A and the other longer list of customers in Sheet2 column A.
    if you use a vlookup against the longer list in Sheet2 to see if they are in Sheet1 then any values returned as #N/A are missing from the list.
    something like =VLOOKUP(Sheet2!A2,Sheet1!A:A,1,false)
    adjust for the locations of our data and sheets.

    EDIT: now the problem with that is that let's say in one list you have J Smith and in another list it is J. Smith - that won't match even if you know they are the same customer. A better way would be to use a fuzzy lookup and if you don't have that it is a free download from microsoft. You just download and follow the directions. It is a great way to do lookups like you need.
    Last edited by Sam Capricci; 07-01-2018 at 07:04 AM.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

+ 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. Formula to find Variances between Planned & Actual Dates
    By GemBox in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 02-23-2023, 09:49 PM
  2. [SOLVED] Formula to Find Unique Customers per City
    By mo4391 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-29-2016, 12:41 PM
  3. Excel Macro to find missing dates and insert missing date as blank row
    By JevaMarie in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-26-2013, 11:08 AM
  4. Any way to use the Find feature in the actual formula?
    By JeffGreat in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-13-2013, 01:14 AM
  5. Any way to use the Find feature in the actual formula?
    By JeffGreat in forum Excel General
    Replies: 1
    Last Post: 02-13-2013, 01:14 AM
  6. Formula to find top customers based on sales value.
    By Harlort in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-17-2013, 08:39 AM
  7. [SOLVED] Find missing dates in a sheet
    By alvin Kuiper in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-07-2006, 10:55 AM

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