+ Reply to Thread
Results 1 to 8 of 8

unique values formula if match criteria

  1. #1
    Registered User
    Join Date
    08-24-2017
    Location
    DUBAI UAE
    MS-Off Ver
    2021
    Posts
    79

    unique values formula if match criteria

    Good Evening Guys,

    I want to list in Column "G" all the unique salesperson's names which are existing in column "B" but it should work according to the store code location @ "E1".

    so basically whenever the value in "E1" changed according to the given values in column "A" the formula should list the unique name of that location only without being repeated.

    I have attached the workbook for more reference as well as a screenshot shot for additional clarification.

    thank you so much in advance.

    Book1.xlsx

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,683

    Re: unique values formula if match criteria

    do you still have version 2016

    this would be for 365 version - i'll look at 2016 version

    =UNIQUE(FILTER(B2:B100000,A2:A100000=E1&""))

    However the numbers in column A are actually Text
    and E1 is a number

    hence why i have added the &"" to the formula

    OR
    =IFERROR(INDEX($B$2:$B$100000, MATCH(0, IF($E$1&""=$A$2:$A$100000, COUNTIF($H$1:$H1, $B$2:$B$100000), ""), 0)),"")
    as an array function control+shift+enter to get {} brackets around formula
    Attached Files Attached Files
    Last edited by etaf; 10-29-2022 at 09:28 AM.
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: unique values formula if match criteria

    1. Ordinary formula (just Enter):
    =IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($A$2:$A$5000)/((--$A$2:$A$5000=$E$1)*(ISNA(MATCH($B$2:$B$5000,G$1:G1,0)))),1)),"")

    2. Next time, please provide a sample of 10-20 rows, not 4669.
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  4. #4
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,683

    Re: unique values formula if match criteria

    Glenn - just an aside - would that formula , be more efficient then the UNIQUE/FILTER versions on 365 version

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: unique values formula if match criteria

    Absolutely, definitely, no. FILTER would be far more efficient. Only one cell is being calculated, albeit with 10K rows. What might be a better question is countif better/worse than MATCH. I'll check that out when I'm next at a PC.

  6. #6
    Registered User
    Join Date
    08-24-2017
    Location
    DUBAI UAE
    MS-Off Ver
    2021
    Posts
    79

    Re: unique values formula if match criteria

    @Glenn Kennedy much appreciated brother, it worked perfectly if Column "A" contained a number.

    how about if column "A" contained text letters instead of numbers? can the formula be edited to give a similar result?
    i.e. column "A" contained the location name and on "E1" I will right the desired location, the "G" column should also do the same work accordingly.

    can that be possible, please?

    appreciated your usual support on this matter.

    I have attached again the workbook with the new references

    unique values formula if match criteria.xlsx

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: unique values formula if match criteria

    Use this:
    =IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($A$2:$A$5000)/(($A$2:$A$5000=$E$1)*(ISNA(MATCH($B$2:$B$5000,G$1:G1,0)))),1)),"")

    If it can contain numbers as well... there was a problem. Your original example had text that looked like numbers. The best fix is to select column A. Data/Text to numbers/Finish.

    Then the formula above will work perfectly for words, or numbers, or both.

  8. #8
    Registered User
    Join Date
    08-24-2017
    Location
    DUBAI UAE
    MS-Off Ver
    2021
    Posts
    79

    Re: unique values formula if match criteria

    @Glenn Kennedy brother you are a genius, worked perfectly. thank you so much.

+ 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. index match multiple unique values based on one criteria
    By Clooney003 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-10-2019, 10:03 PM
  2. [SOLVED] Index & Match Formula for Unique Values On Multiple Criteria
    By JenMasters84 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-10-2018, 03:01 PM
  3. [SOLVED] Index & Match Formula for Unique Values On Multiple Criteria
    By Neilesh Kumar in forum Excel General
    Replies: 2
    Last Post: 03-24-2017, 10:13 AM
  4. [SOLVED] Index & Match Formula with Unique Values bases on a criteria
    By Neilesh Kumar in forum Excel General
    Replies: 5
    Last Post: 03-24-2017, 06:22 AM
  5. Replies: 2
    Last Post: 01-03-2017, 08:40 AM
  6. Listing Unique values based on partial match criteria
    By mamachrissy1028 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-17-2016, 05:23 PM
  7. [SOLVED] Listing Unique values based on partial match criteria
    By mamachrissy1028 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-11-2016, 11:38 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