98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » One cell multiple criteria Search and Sorting activated by Keyst
One cell multiple criteria Search and Sorting activated by Keyst
Resolved · Urgent Priority · Version 2016
Wed 27 Sep 2017, 15:08Delegate Jordan said...
Jordan has attended:
Excel VBA Introduction course
Excel VBA Intermediate course
Excel VBA Advanced course
One cell multiple criteria Search and Sorting activated by Keyst
This is in regards to the advanced Excel VBA course.
Hi
I want my table in Excel to highlight rows based on multiple criteria typed into one specific cell (like a Google search box)
Then sort rows in table by highlighted colour
So a user can search for a supplier based on location and category fields etc. and the appropriate highlighted row(s) will appear at the top.
I also want the macro code to activate on letter input/keystroke, in affect be automatic
Example: In cell A1 user types ‘finance London’, then table A3:P2000 highlights all rows that contain finance AND London and sorts them to the top. So just typing finance will highlight 10, but typing finance and London will further ‘filter’ and highlight 5 rows.
Hope you can help
Thanks
Jordan
Mon 2 Oct 2017, 13:38Trainer Jens said...
RE: One cell multiple criteria Search and Sorting activated by K
Hi Jordan,
Thank you for the forum question and sorry the late answer. It is not and easy task.
I have not found a solution which can do it all.
Conditional formatting solution:
=IF($G$1="",FALSE,IF($G$1="*"&" "&"*",AND(SEARCH(LEFT("*"&$G$1&"*",SEARCH(" ",$G$1)-1),$A6&$B6&$C6&$D6&$E6&$F6&$G6),SEARCH(RIGHT("*"&$G$1&"*",SEARCH(" ",$G$1)),$A6&$B6&$C6&$D6&$E6&$F6&$G6)),SEARCH("*"&$G$1&"*",$A6&$B6&$C6&$D6&$E6&$F6&$G6)))
This will not do it as you type. The search cell is G1 and the list starting from A1 and has 7 columns.
If you look at the link below you can filter as you type.
https://trumpexcel.com/dynamic-excel-filter/
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Mon 2 Oct 2017, 23:08Delegate Jordan said...
RE: One cell multiple criteria Search and Sorting activated by K
Hi Jens
Formula doesn’t seem to be working..
1) Just to confirm, as I noticed formula references can change when you paste them into conditional formatting..
Search cell G1
Table A6:G200 where Row 6 contains header values?
2) Looks like I can only search text strings left to right
e.g. A7 =”happy”, G7=”days”.. searching “ha*da” highlights row, however search “da*hap” highlights nothing..
3) Also I wanted excel to recognise space. Instead I have to manually type in * after word.
(But I’ve found a workaround for users)
G1 = J1, J1 =SUBSTITUTE(D1," ","*"), where D1 = user search box,
Is there a less laborious way of achieving this.
Please this is quite urgent as I have to present to head of department by Friday
Big Thanks
Thu 12 Oct 2017, 12:58Trainer Jens said...
RE: One cell multiple criteria Search and Sorting activated by K
Hi Jordan,
I am sorry for the late answer, but unfortunately I have not been able to find a solution which will do exactly what you want. I have asked our other Excel trainers to find a solution, but no one has been able to get it right. I have also tried to Google a solution, but again I haven't been able to find one.
* is a wildcard in Excel. * is any number of characters. ? is one character.
The entry below tells Excel to look at the entry in cell D1, but there can be any number of characters before and after the entry. If you type da in D1 it will find Friday,dashboard,Canada all words where you have da somewhere in the text string.
"*"&$D$1&"*"
I am sorry that I cannot provide you with a better solution.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Mon 16 Oct 2017, 14:19Trainer Wendy said...
Will be marked as resolved in 5 days
Notice: This is an automated message. Due to inactivity, this forum post will be marked as 'resolved' if there are no further responses in the next 5 days.
Mon 16 Oct 2017, 14:39Trainer Wendy said...
Will be marked as resolved in 5 days
Notice: This is an automated message. Due to inactivity, this forum post will be marked as 'resolved' if there are no further responses in the next 5 days.
Mon 23 Oct 2017: Automatically marked as resolved.
Training information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Viewing Many WorksheetsIf there are more worksheets in your workbook than there is room to show all their tabs at the bottom of the screen - Right click on the navigation arrows. A list of all your worksheets is shown. You just click on the one that you want to access. If you have more than 15 worksheets, select in the list and choose your worksheet from hundreds. |
