+ Reply to Thread
Results 1 to 8 of 8

Formula Help. How can I not return duplicates?

  1. #1
    Registered User
    Join Date
    06-05-2013
    Location
    Orlando, FL
    MS-Off Ver
    Excel 2010
    Posts
    55

    Formula Help. How can I not return duplicates?

    I am looking to return all of a specific value in a range without returning duplicates. The formula that I use works to pull out the information that I need bet it does include duplicates. I have attached an abbreviated version of my workbook, The formula is on the summary page and in Cell F7
    Thank You in advance for your help.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Formula Help. How can I not return duplicates?

    What dont you want duplicated?
    The surgeons name or the dates?
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Formula Help. How can I not return duplicates?

    Hi mahalek,

    I don't see where your summary information checks with the second sheet data.

    I think this problem is really a Pivot Table type of problem. See what I've done with your data on the attached. Can a Pivot Table do what you are looking for?
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Registered User
    Join Date
    06-05-2013
    Location
    Orlando, FL
    MS-Off Ver
    Excel 2010
    Posts
    55

    Re: Formula Help. How can I not return duplicates?

    I am only interested in the Conf # nothing else. Thanks

  5. #5
    Registered User
    Join Date
    06-05-2013
    Location
    Orlando, FL
    MS-Off Ver
    Excel 2010
    Posts
    55

    Re: Formula Help. How can I not return duplicates?

    I believe it can. Thank you. I can also run averages off of that table. I can absolutely use it in this case. I am however still interested if there is a formula that can pull out that data.

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Formula Help. How can I not return duplicates?

    I am however still interested if there is a formula that can pull out that data.
    This is a formula based solution that uses a helper column (All Cases column M).
    Here is the array formula* pasted in F7 and dragged down to F16:
    Please Login or Register  to view this content.
    The "Merge and Center" for rows 18 and 30 will have to be removed. Then copy cell F7 into cells F18 and F30 and dragged to F28 and F40 respectively. You'll need to use the format painter to reformat your fill color afterwards. Here is a copy of your file with the formula and aforementioned changes applied:
    Copy of SURG Pricing example.xlsx
    Array formulas are activated with the keystrokes Ctrl+Shift+Enter.
    Let me know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  7. #7
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2019
    Posts
    2,360

    Re: Formula Help. How can I not return duplicates?

    Another version
    Attached Files Attached Files

  8. #8
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Formula Help. How can I not return duplicates?

    Quote Originally Posted by azumi View Post
    Another version
    https://www.excelforum.com/showthread.php?t=1040120 ----------
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

+ 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. Replies: 3
    Last Post: 07-21-2015, 05:10 PM
  2. Replies: 6
    Last Post: 07-07-2015, 12:53 PM
  3. Return values of duplicates
    By kneenah in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-22-2014, 05:19 PM
  4. Replies: 1
    Last Post: 07-30-2014, 02:37 PM
  5. Replies: 6
    Last Post: 12-10-2013, 02:43 AM
  6. Return value - 2 criteria, many duplicates
    By ABSTRAKTUS in forum Excel General
    Replies: 6
    Last Post: 02-03-2012, 12:02 PM
  7. Replies: 2
    Last Post: 03-20-2011, 11:19 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