+ Reply to Thread
Results 1 to 6 of 6

Generating a list of unique values based on specific conditions

  1. #1
    Registered User
    Join Date
    06-03-2012
    Location
    St. Petersburg, Russia
    MS-Off Ver
    Excel 2007
    Posts
    14

    Generating a list of unique values based on specific conditions

    Hi guys,

    Really need your input on how to generate a list of unique values based on the following conditions:

    • Ignore blank cells
    • Ignore cells starting with a "dash"
    • Take into account only the first 5 letters of every entry

    So far, I've only managed to come up with a formula to ignore the blanks:

    Please Login or Register  to view this content.
    Could you please help me out with the rest of it. I can't seem to figure out where the LEFT(kor!C$2:C$999,1)="–" bit goes to make the formula ignore "dash"-initial cells as well, and also how to make it match only the first 5 characters of every entry...

    Thanks in advance. Every bit of advice will be deeply appreciated.

  2. #2
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Generating a list of unique values based on specific conditions

    Kindly upload a sample worksheet along with the desired results. Would be easier to resolve!
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  3. #3
    Registered User
    Join Date
    06-03-2012
    Location
    St. Petersburg, Russia
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Generating a list of unique values based on specific conditions

    Quote Originally Posted by Ace_XL View Post
    Kindly upload a sample worksheet along with the desired results. Would be easier to resolve!

    OK, here is a sample file. I didn't upload anything with my initial post, as I thought the conditions were self-explanatory. Sorry.
    Attached Files Attached Files

  4. #4
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: Generating a list of unique values based on specific conditions

    maybe macro
    Please Login or Register  to view this content.
    edited:
    or slightly shorter
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by nilem; 07-05-2012 at 10:46 AM.

  5. #5
    Registered User
    Join Date
    06-03-2012
    Location
    St. Petersburg, Russia
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Generating a list of unique values based on specific conditions

    Thank you for your input, nilem

    Unfortunately, in this case manually running a macro is not an option. The column that serves as a basis for this list is being constantly updated and the results should be visible immediately in real time.

    So far I only managed to think of an unholy construction that uses two auxiliary columns and three different formulas feeding into one another. But this is a makeshift solution, obviously, seeing that it nearly doubled the size of the file and slowed everything down to a crawl.

    Right now, I'm trying to think of a way to do all of these transformations within one formula without bringing much additional garbage into the file...

  6. #6
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: Generating a list of unique values based on specific conditions

    This macro is fast enough and you can assign it to an event Worksheet_Change, for example.
    Well if it's suitable for you.

+ 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