+ Reply to Thread
Results 1 to 12 of 12

Return Unique Text Values without Advanced Filter

  1. #1
    Registered User
    Join Date
    04-17-2009
    Location
    Dallas
    MS-Off Ver
    Excel 2007
    Posts
    9

    Return Unique Text Values without Advanced Filter

    Hey guys I have spent the last 30 minutes searching and found answers too simple and too complicated. I think what I need is simple code, but I don't know how to write it:

    I have a list of Window sizes and types in Range B4:B:43. The descriptions repeat because in Column K I am listing a location for each window in a house.

    Example:
    B K
    3050 SH 1/1 Dining Room
    3050 SH 1/1 Kitchen
    2030 Fixed Foyer
    2030 Fixed Living Room

    In the Same sheet Starting on B:45 I want a list of only Unique Window Types:

    B:45 and Down:
    3050 SH 1/1
    2030 Fixed

    I'd like this to automatically appear after populating the first list. I used an advanced filter the first time and it worked, but it is not reliable and sometimes returns duplicate values or give me an error message. Plus once again having it happen automatically as the list will change each time I access the sheet would be great.

    Thanks,
    Nick
    Last edited by nicknick5219; 05-21-2009 at 10:13 AM. Reason: New problem - but related

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Return Unique Text Values without Advanced Filter

    If you write it in 3 columns:
    Please Login or Register  to view this content.
    Then filtering by second column must work

  3. #3
    Registered User
    Join Date
    04-17-2009
    Location
    Dallas
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Return Unique Text Values without Advanced Filter

    Does anyone else have any ideas for me?

  4. #4
    Forum Expert contaminated's Avatar
    Join Date
    05-07-2009
    Location
    Baku, Azerbaijan
    MS-Off Ver
    Excel 2013
    Posts
    1,430

    Re: Return Unique Text Values without Advanced Filter

    Maybe it'll help you
    Attached Files Attached Files
    Люди, питающие благие намерения, как раз и становятся чудовищами.

    Regards, ?Born in USSR?
    Vusal M Dadashev

    Baku, Azerbaijan

  5. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Return Unique Text Values without Advanced Filter

    Perhaps the below (non array) will work for you ?

    Please Login or Register  to view this content.
    Last edited by DonkeyOte; 05-19-2009 at 01:23 PM. Reason: Qualifying as non-array to avoid confusion

  6. #6
    Registered User
    Join Date
    04-17-2009
    Location
    Dallas
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Return Unique Text Values without Advanced Filter

    The last array formula did it with a slight fine tuning. Once again guys thank you for your responses. Your excel knowledge blows my mind and it will make my job easier every day so I appreciate it.

    Nick

  7. #7
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Return Unique Text Values without Advanced Filter

    Quote Originally Posted by DonkeyOte View Post
    Perhaps the below (non array) will work for you ?

    Please Login or Register  to view this content.

    Now I'll ask DonkeyOte to explain what LOOKUP(REPT("Z",255),CHOOSE({1,2},"".... doing.

    Ty

    Edit: Found it... here

    http://www.excelforum.com/excel-gene...ious-post.html
    Last edited by zbor; 05-19-2009 at 03:29 PM.

  8. #8
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Return Unique Text Values without Advanced Filter

    Yes, the use of CHOOSE allows you to build an array of 2 values against which you can search according to the criteria value (in our case Z repeated 255 times)

    =LOOKUP(REPT("Z",255),CHOOSE({1,2},"",formula result))

    The first value in the 2 value array is always a Null (text), the second is the variant output of the formula (may be anything - text or error).
    One of the advantages of LOOKUP is that it will ignore values in the array that do not match the type of the criteria, this includes errors...
    By using a BIGTEXT type criteria value the LOOKUP will return the last text string it finds in the 2 value array, ie:

    =LOOKUP(REPT("Z",255),CHOOSE({1,2},"","Apple"))

    will return Apple

    =LOOKUP(REPT("Z",255),CHOOSE({1,2},"",NA())

    will return Null as #N/A is ignored given it is not text like the criteria.
    Last edited by DonkeyOte; 05-19-2009 at 04:20 PM.

  9. #9
    Registered User
    Join Date
    04-17-2009
    Location
    Dallas
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Return Unique Text Values without Advanced Filter

    Guys I tried copying the array formula to this sheet and it will not populate the same for some reason. I want to return only unique values from B:5;B33 starting in cell B:36 and down B column as far as needed. Also any ideas why the copy and paste of the array didn't work after adjusting cell references?Thanks again.
    Attached Files Attached Files

  10. #10
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Return Unique Text Values without Advanced Filter

    Based on revised ranges:

    Please Login or Register  to view this content.
    copy down as required

    Note: this is not an array and can be entered as normal (ie with ENTER)

  11. #11
    Registered User
    Join Date
    04-17-2009
    Location
    Dallas
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Return Unique Text Values without Advanced Filter

    That worked. I figured out the reason why I had to use the array on the first sheet is my list contained spaces. The solution worked perfectly without them. Thank you all again. I can't wait to take an advanced excel course to wrap my head around the index function.

  12. #12
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Return Unique Text Values without Advanced Filter

    Indeed, I should have qualified that... in the sample this didn't seem as though it was an issue.

+ 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