+ Reply to Thread
Results 1 to 10 of 10

Array Unique list Issue

  1. #1
    Registered User
    Join Date
    06-21-2012
    Location
    Cardiff, Wales
    MS-Off Ver
    Excel 2007
    Posts
    23

    Unhappy Array Unique list Issue

    Hi
    I have an array formula to search using two criteria and returning the resulting list of company names. my problem is is that is bringing back duplicate values where i only want a unique list. the search criteria change as the search fields are drop down lists. The formula i have is :

    =INDEX(CustName, SMALL(IF(ISERROR(SEARCH($A$4,TerritoryMain)*SEARCH($B$4,TerrMgrMain)), "", ROW(TerritoryMain)-MIN(ROW(TerritoryMain))+1), ROW('Quote Data'!A1)), COLUMN('Quote Data'!A1))

    I was thinking i needed to insert a Countif function sumwhere, but im not sure.

    Any help would be greatfully received.

    Thanks

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,658

    Re: Array Unique list Issue

    To extract a unique-list, in general, they use like this:
    =INDEX(CusName,MATCH(0,COUNTIF(CusName,CusName),0))
    In your real data, I am not sure it is OK or not, unless you can upload an example
    Quang PT

  3. #3
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,658

    Re: Array Unique list Issue

    To extract a unique-list, in general, they use like this:
    =INDEX(CusName,MATCH(0,COUNTIF(CusName,CusName),0))
    In your real data, I am not sure it is OK or not, unless you can upload an example

  4. #4
    Registered User
    Join Date
    06-21-2012
    Location
    Cardiff, Wales
    MS-Off Ver
    Excel 2007
    Posts
    23

    Re: Array Unique list Issue

    Hi there
    Yes that fine - however the unique list must be created after matching two different criteria (Territory, then Territory Manager). I have a list, but dont know how to make it unique as its pulling duplicate company names at present. If my current formula is working in the sense that its bringing a list of companies, where and what do i insert to make sure those values are unique.
    Unfortunately i cant post an example at this time.

  5. #5
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    wales
    MS-Off Ver
    Excel 2007
    Posts
    964

    Re: Array Unique list Issue

    you would be better to upload a workbook with a smaple and your expected results
    The Importance of INDEX - A GUIDE TO INDEX'S OTHER USES
    <--- If a post helps hit the star

  6. #6
    Registered User
    Join Date
    06-21-2012
    Location
    Cardiff, Wales
    MS-Off Ver
    Excel 2007
    Posts
    23

    Re: Array Unique list Issue

    Here is a sample of what im working on - it the regional reporting tab im looking at.. you can see im getting duplcate entries. I want to be able to bring back a unique list of values as long as it matches the two criteria specified in the drop downs.

    Thanks all
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    06-21-2012
    Location
    Cardiff, Wales
    MS-Off Ver
    Excel 2007
    Posts
    23

    Re: Array Unique list Issue

    Can nobody help me?

  8. #8
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,156

    Re: Array Unique list Issue

    hi Enigmatise_1981, maybe an array formula like that in B7 copied down:
    =IFERROR(INDEX(CustName,MATCH(0,IF(TerritoryMain=$A$4,IF(TerrMgrMain=$B$4,COUNTIF('Regional Reporting'!B$6:B6,CustName))),0)),"")

    you have to paste the formula inside the formula bar & press CTRL + SHIFT + ENTER. When done correctly, you should see curly brackets surrounding it

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  9. #9
    Registered User
    Join Date
    06-21-2012
    Location
    Cardiff, Wales
    MS-Off Ver
    Excel 2007
    Posts
    23

    Re: Array Unique list Issue

    All that does is return a value of TRUE or FALSE!

    Scratch that - the formula copied over twice. That seems to the the trick - thank you.

    Is there something i can do (maybe in VBA that automatically then sort the results alphabetically) doing it using sort on the tolobar doesnt work too well as has to be re-done everytime a new value is selected in the dropdown.

    Many thanks
    Last edited by Enigmatise_1981; 02-11-2013 at 05:59 AM.

  10. #10
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,156

    Re: Array Unique list Issue

    chanced upon a video on how to do the sorting, so i thought i'll share. try this array formula in B7:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    do note that your 3 Named Range must have the same rows. Currently, CustName is:
    ='Quote Data'!$F$2:$F$5000
    while TerritoryMain & TerrMgrMain is only until row 4000

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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