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

@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 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

  • TV Repair - year-end totals - 4.xlsx
    874.7 KB · Views: 11
Last edited:
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.
 

Attachments

  • TV Repair - year-end totals - 4.xlsb
    201.4 KB · Views: 7
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

  • Copy of TV Repair - year-end totals - 4_PMc.xlsx
    591.4 KB · Views: 5
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?
 
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:
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

  • TV Repair - year-end totals - 4 (PB).xlsx
    919.4 KB · Views: 3
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!
 
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!
 
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

  • TV Repair - year-end totals - scripting (PB).xlsb
    681.2 KB · Views: 2
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. :)
 
Back
Top