• 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...

  • 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.

Peter Bartholomew

Well-Known Member
@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
 

Eloise T

Active Member
@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
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.
 

Attachments

Last edited:
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.
 

Attachments

Peter Bartholomew

Well-Known Member
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?
 

bosco_yip

Excel Ninja
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:

Peter Bartholomew

Well-Known Member
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.
 

Attachments

Eloise T

Active Member
Or,

Try this shorter formula in replaced 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
Fantastic!
 

Eloise T

Active Member
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.
I like your approach. Seems simple yet does the job!
Thanks!
 

Peter Bartholomew

Well-Known Member
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).
 

Attachments

Eloise T

Active Member
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).
Thank you. I will put it in my rainy day file. :)
 
Top