1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to filter a column of alphanumeric text to ultimately sort it.

Discussion in 'Ask an Excel Question' started by Eloise T, Jan 3, 2019.

  1. Eloise T

    Eloise T Active Member

    Messages:
    851
    .
    Last edited: Jan 4, 2019
  2. vletm

    vletm Excel Ninja

    Messages:
    4,805
    Eloise T
    Could You give more details or even a sample file?
  3. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    945
    Eloise T, did you forget to but content in your post?
  4. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    648
    @Eloise Are you interested in
    1. A manual process involving 'Remove Duplicates' and 'Sort'
    2. A formula-based approach (distinctly cumbersome at present)
    3. A future dynamic array approach
    4. A Power Query solution
    5. A macro automating 1 or 4
    6. A purpose-written code solution
  5. Eloise T

    Eloise T Active Member

    Messages:
    851
    I apologize to all. I was distracted in the middle of trying to create a question here, and then realizing I could not properly finish the question, I tried to delete the question so I could return later to properly pose the question...and I failed at properly deleting the question. Ugh!

    So, here is the question with accompanying spreadsheet:

    I need a formula to determine the number of 70 inch, 73", 75", 80", 84", 85", & 90" TVs in Column F.

    Attached Files:

    Last edited: Jan 5, 2019
  6. Pete Mccann

    Pete Mccann Member

    Messages:
    101
    Hi Eloise. I'm not sure if this is the sort of thing you were looking for.....

    Attached Files:

    Thomas Kuriakose and Eloise T like this.
  7. vletm

    vletm Excel Ninja

    Messages:
    4,805
    Eloise T
    ~25690 rows data ... with formulas ... hmm?

    I cleaned something
    and
    Press [ Do It ] to get something ...

    Notes:
    You can add more sizes, if needed.
    You can see counted rows from B-column.

    Attached Files:

    Eloise T likes this.
  8. Pete Mccann

    Pete Mccann Member

    Messages:
    101
    Hi Vletm - I agree that a VBA solution is going to be a lot quicker on this sort of thing.....unfortunately I am not very good at VBA. :)
    Eloise - is there a reason why there are so many duplicates in this list? If you strip out the duplicates, you are left with approx. 1320 individual TV model numbers.

    Attached Files:

  9. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    648
    Oh well, I failed at the first hurdle! Given a single model number 'E550VA' I failed to see how one would determine the screen size. Since I can't do it once, I am not best placed to aggregate over 25,000 instances.

    Out of curiosity, I normally stick to worksheet formulae because they evaluate faster than VBA. We can not all be right?
  10. Eloise T

    Eloise T Active Member

    Messages:
    851
    Thank you to both of you. Both work nicely!
  11. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    2,150
    Or,

    Try this shorter formula in replace of the 15 IFs formula in column F and the helper in column E

    In G5, copied down :

    =IF(LEN(C5)<51,IFERROR(0+MID(C5,MIN(FIND({0,1,2,3,4}+{0;5},C5&1/19,ISERR(-MID(C5,MIN(FIND({0,1,2,3,4}+{0;5},C5&1/19))+1,1))*MIN(FIND({1,2,3,4,5,6,7,8,9,0},C5&1/19))+1)),2),0),0)

    Regards
    Bosco
    Last edited: Jan 7, 2019
    Eloise T likes this.
  12. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    648
    Am I being dim. What is wrong with using the same approach as your conditional formatting?

    By the way, the standard way of looking up substrings that match any specified pattern within a search string is to use Regular Expressions. This can be called from Excel using UDF to access functions within the Microsoft Scripting Library.

    Attached Files:

    Eloise T likes this.
  13. Eloise T

    Eloise T Active Member

    Messages:
    851
    Fantastic!
  14. Eloise T

    Eloise T Active Member

    Messages:
    851
    I like your approach. Seems simple yet does the job!
    Thanks!
  15. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    648
    Just for the record. This version is one you might wish to save in a remote folder somewhere as insurance against a rainy day. It uses a RegEx function written by Patrick G. Matthews and it is called as a user-defined function. The particular pattern I have set simply returns the first pair of digits it finds within the string representing the model. The approach is capable of searches against far more subtle combinations of requirements but it is slow (approx. 1 min to update) applied to a list as large as yours.
    Peter
    p.s. The alternative pattern LC-?(\d{2}) would restrict the search to a pair of digits that are preceded by LC or LC- (the ? makes the - optional).

    Attached Files:

  16. Eloise T

    Eloise T Active Member

    Messages:
    851
    Thank you. I will put it in my rainy day file. :)

Share This Page