+ Reply to Thread
Results 1 to 15 of 15

Looking to create a list of unique names that match criteria without using an array

  1. #1
    Forum Contributor
    Join Date
    04-02-2010
    Location
    penrith
    MS-Off Ver
    Excel 2007
    Posts
    522

    Looking to create a list of unique names that match criteria without using an array

    Hi, I have a list of name entries (some duplicated) in column A from A2 to A20, in column B next to certain entries I haver the number 1 to signify dues paid.

    I am looking to create a unique list in column C that lists the names with the 1 beside them, only 1 instance of the name in column A has a 1 next to it at any time.

    Looking to do this without a pivot table or an array formula.

    Looking forward to the answer.

    Cheers

    JD

  2. #2
    Valued Forum Contributor
    Join Date
    10-26-2008
    Location
    Birmingham, UK
    MS-Off Ver
    All versions up to 2010
    Posts
    1,025

    Re: Looking to create a list of unique names that match criteria without using an array

    Hi John
    You can use Autofilter for this. Follow these steps:
    1. Insert 3 blank rows at the top of your worksheet.
    2. In A1 type "Dues Paid". This must be exactly as the header for the Dues paid column on your worksheet.
    3. In A2 type "1".
    4. Highlight all the cells you want to filter on your worksheet.
    5. In the Sort & Filter section of the toolbar click on Advanced.
    6. Click on Copy to another location.
    7. The List Range box will be populated with your highlighted Range.
    8. In the Criteria Range box highlight Cells A1 to A2.
    9. In the copy to box click on a cell where you want the data to appear.
    10. Click OK.
    Job done.
    See sample attached.
    Good luck.
    Tony
    Attached Files Attached Files
    Last edited by ARGK; 11-07-2013 at 07:56 AM.

  3. #3
    Forum Contributor
    Join Date
    04-02-2010
    Location
    penrith
    MS-Off Ver
    Excel 2007
    Posts
    522

    Re: Looking to create a list of unique names that match criteria without using an array

    Need it be a formula as it is part of a display sheet and will be user locked.

    Cheers

    JD

  4. #4
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Looking to create a list of unique names that match criteria without using an array

    John

    Probably you'll need an ARRAY formula for this..
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  5. #5
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: Looking to create a list of unique names that match criteria without using an array

    This is an array solution but as Fotis I can not see non-array version.

    Is there any reason for not used Array?
    Is this workbokk going to be shared?
    Unique1.xlsx
    Last edited by RobertMika; 11-07-2013 at 08:56 AM.
    If you are http://www.excelforum.com/image.php?type=sigpic&userid=125481&dateline=1392355029happy with the results, please add to the contributor's
    reputation by clicking the reputation icon (star icon).




    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.
    To undo, select Thread Tools-> Mark thread as Unsolved.
    http://www.excelaris.co.uk

  6. #6
    Forum Contributor
    Join Date
    04-02-2010
    Location
    penrith
    MS-Off Ver
    Excel 2007
    Posts
    522

    Re: Looking to create a list of unique names that match criteria without using an array

    I need it be a normal formula as I have too many array formulas at the moment and the workbook is slowing down rapidly, just hope there is a way. This is reactive list that autopopulates as data is entered bythe user.

    Cheers

    JD
    Last edited by john dalton; 11-07-2013 at 09:01 AM. Reason: additional info

  7. #7
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: Looking to create a list of unique names that match criteria without using an array

    Generaly array are slower (this is the cost of multiple calculation in one go)but please better check your exisiting formulas and if they can not be amended or maybe the ranges can be cut down.

  8. #8
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,841

    Re: Looking to create a list of unique names that match criteria without using an array

    I don't know if there is a way to expand this concept and adapt it for this task. However, even if it did I'm not sure it would be any faster. It would still be an array formula, only difference is that INDEX is creating the array instead of CSE. What does the expertis say about this?
    http://www.get-digital-help.com/2013...rray-formulas/
    <----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left

    If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
    I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.

  9. #9
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: Looking to create a list of unique names that match criteria without using an array

    Even if this work this would requaied an INDEX for each range which will in fact add to the calcualtion time - so "false economy"

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

    Re: Looking to create a list of unique names that match criteria without using an array

    Here's a non-array formula method that uses a helper column.

    With your data in the range A2:B20...

    Enter this formula in C2 and copy down to C20:

    =IF(B2=1,IF(SUMPRODUCT(--(A$2:A2&B$2:B2=A2&B2))=1,MAX(C$1:C1)+1,""),"")

    Note that the formula refers to cell C1. C1 is assumed to be a TEXT column header or an empty cell. C1 must not contain a number.

    Then, for the unique names...

    Enter this formula in E2 and copy down until you get blanks:

    =IFERROR(INDEX(A$2:A$20,MATCH(ROWS(E$2:E2),C$2:C$20,0)),"")
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  11. #11
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Looking to create a list of unique names that match criteria without using an array

    And of course pls note that in fact SUMPRODUCT function is an ARRAY formula and ROW function is a Volatile Function.

    Of course Tony is the right man to explain all these better to you if he wants.

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

    Re: Looking to create a list of unique names that match criteria without using an array

    ROW is NOT a volatile function.

    It's easy to test.

    Open a new file and enter this formula in some cell:

    =ROW()

    Save the file then close it.

    Reopen the file, don't do anything to the file then close it.

    If ROW was volatile Excel would have asked you if you wanted to save the changes you made to the file.

  13. #13
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Looking to create a list of unique names that match criteria without using an array

    Thanks for sharing this trick

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

    Re: Looking to create a list of unique names that match criteria without using an array

    ----------

  15. #15
    Forum Contributor
    Join Date
    04-02-2010
    Location
    penrith
    MS-Off Ver
    Excel 2007
    Posts
    522

    Re: Looking to create a list of unique names that match criteria without using an array

    Thanks for the feedback guys.

+ 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. [SOLVED] How do you List unique names when the criteria is identical
    By john dalton in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 10-10-2013, 09:40 AM
  2. Replies: 2
    Last Post: 02-22-2013, 07:16 AM
  3. [SOLVED] Array formula, returning unique list of names w/ vlookup
    By BROWN_RY in forum Excel General
    Replies: 0
    Last Post: 03-29-2012, 04:31 PM
  4. Create Unique List with Criteria
    By Fos605 in forum Excel General
    Replies: 2
    Last Post: 07-20-2010, 07:57 AM
  5. Count Unique Names in list w/ Additional Criteria?
    By Nodak in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-25-2005, 08:06 PM

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