Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Multiple column search and publish it

0

Hi, first I must thank WillieD24 for helping me to solve the 'Serial Number' issue 3 days ago.

I now have a new questions on 'multiple column search' and on 'publish the searched result'.

I attach here 'Search example 1' worksheet to show what I hope to achieve.

Wish all can help me.

Best regards.

Answer
Discuss

Discussion

Victor

Please note that your question should (at least) outline the nature of your problem, so that contributors and other users do not have to download/open you file to see if they can answer or it's of intetest. I'm pretty sure the Rules mention that. 

Kindly edit your original question to describe your issue.

I probably won't have time today to reply but maybe Willie can. 
John_Ru (rep: 6232) Jul 26, '23 at 1:40 am
@Victor

Glad I was able to solve your previous issue.
Once again you have provided a sample file which clearly illustrates the desired end result. To do what you are after will require some complex formulas. Do you want this done with worksheet formulas or with VBA?
Due to the complexity, I don't have the time right now to work on this. I will update you later.

Cheers   :-)
WillieD24 (rep: 557) Jul 26, '23 at 10:55 am
Thank you WillieD24.
I am not familar with either worksheet formulas or with VBA. I prefer whichever method that is simple or with less operating steps.
My new worksheet will now include 'serial numbers'. May I add a target E, which is to search for a range of rows. e.g. 'serial number 36 to 79.
Please take you time. I can wait.
Best regards.
Victor (rep: 12) Jul 26, '23 at 12:18 pm
@Willie - this seems pretty straightforward (though it feels like the mini-project precluded by the Rules). I'll leave it to you anyway since I have limited time until the weekend.
John_Ru (rep: 6232) Jul 26, '23 at 4:49 pm
@John
Agreed, however, I see this as a good learning experience for myself. I will peck away at this as time allows.
WillieD24 (rep: 557) Jul 27, '23 at 6:31 pm
@Willie - I'm sure you'll crack it.

@Victor- please be patient while Willie creates a solution for you. Don't forget he's giving his own spare time for free.
John_Ru (rep: 6232) Jul 27, '23 at 6:49 pm
To Willie and John, thank you very much. I am really very much appreciated for the help that you are giving me. Because once this solution is found, it will cut down a lot of my working hours to find and collect the "search result". Willie, I can wait until end of next week since you are so short of time. Best regards.
Victor (rep: 12) Jul 27, '23 at 7:59 pm
@Victor
I have come up with 6 different ways to tackle this. (That's the beauty of VBA - there is always multiple ways to achieve a goal.) I am now testing each to find the one with the cleanest, simplest code. Hope to have a solution in a few days.
WillieD24 (rep: 557) Jul 29, '23 at 1:11 pm
Add to Discussion

Answers

1
Selected Answer

@Victor

I had more time today than expected and everything came together nicely. I chose the "Select Case" method as it is clean and is easily modified should the need arise.

I tweaked the "source" table (col's "A" thru "J") slightly to keep things consistant. I modified the Worksheet_Change event to include the code from my answer to your previous question/post (July 23).

The search results (col's "L" thru "T") are listed on the same sheet as the source table. The search criteria is limited to a max of 4. Dates are not allowed because in the source table date and time are together in the same cell. They would need to be split to work with them - more effort than I am willing to devote.

I then asked a bunch of "What if?" qustions and added a bunch of Data Validation and Conditional Formatting elements to the cells above the "results" table to prevent errors. I added a "button" to click after the search criteria has been entered to trigger the search macro. As matches are listed in the results table, the coorsponding "A" cell of the source table row is filled with yellow.

The revised is attached.

IMPORTANT: If the sheet name or layout is changed then the macro will need to be modified to match the changes.

If this solution answers your question please mark my answer as selected.

Cheers   :-)

Discuss

Discussion

Thank you Willie. It seems something is missing from the 'Auto fill serial number and Search List V1' file and I was unable to use it. When I clicked the 'Search for Selected Criteria' button. this following message appeared. (Cannot run the macro"Auto fill serial number and Serach List V1.xlsm'!Final_Code_Build'. The macro may not available in this workbook or all marcos may be disabled). Next, this may give you some headache to change the serach criteria and, this is all my fault. I did not notice earlier that in the 'Product' and 'Made' columns, some keywords are not the begining text in that column. For example, there are " Dallas Texas, USA" and "back spare tyre solid" in which "USA" and "tyre" are not the begining word in those columns. My apologies. Please take time off to enjoy your weekend first and continue next week. Best regards.
Victor (rep: 12) Jul 29, '23 at 8:45 pm
@Victor
I was bothered that you encountered a problem when I hadn't, so I had to check things. The error after clicking the button was an oversight on my part. I changed the name of the macro and failed to check that Excel had updated the button; it had not, but is now correct.
Also, when doing my checking I had missed updating a couple of cell references in the Worksheet_Change event code after making small tweaks to the table layout (now fixed).
The corrected file (V2) is attached to my answer above.
As for the search criteria, the keyword does not need to be the first word in the cell, unless that is how you would like it (easy fix). Also, criteria can be listed in any order (ie: Product, Name, Made  etc.)

Now to crack another cold one.

Cheers   :-)
WillieD24 (rep: 557) Jul 29, '23 at 10:48 pm
Hi Willie, what can I say. You are simply marvellous. This is exactly what I had hope for and you had just given it to me. Many thanks and best wishes.
Victor (rep: 12) Jul 30, '23 at 2:20 am
@Willie - well done! 
John_Ru (rep: 6232) Jul 30, '23 at 2:57 am
@Victor
Glad I was able to help. I have added one small extra to the file - a button to reset the criteria categories and clear the chosen criteria. (new file attached above)

@John   Thanks for the props.
WillieD24 (rep: 557) Jul 30, '23 at 3:29 pm
Thanks Willie, received the new file. Three small points may I ask:  1) if I were to change the heading of my raw data, how do I changes the new heading names in the drop-down list of the 4 Criteria column.  2) How do I purposely leave a Criteria empty. Like in cells C10 and C11, there are empty ones. 3) Your solution here is really great. But, I am not sure it is because I did not input the Criteria properly or correctly, hence there are some records not found from a sample of a 2500-plus rows data test. I compared the search result with the ordinary Excel Ctl 'F' and there are difference in the number of records counted. I am still doing my own checks now. If I really cannot solve the issue, is there a way to unload an Excel file to you. I am not going to send you the 2500+ row file, but with a smaller number of rows, if I can, just to illustrate what and how it happened. This is because now in dicussion mode, there is no provision for me to attach files. Best regards.
Victor (rep: 12) Jul 30, '23 at 11:14 pm
Hi Willie, sorry, I meant to upload an Excel file, not unload. Typo error. Apologies.
Victor (rep: 12) Jul 31, '23 at 4:03 am
@Victor - you can attach a second file to your original question

@Willie - I'm away for a couple of days but could help after if you have an issue 
John_Ru (rep: 6232) Jul 31, '23 at 6:10 am
Hi Willie, attached "Search_Soon_Grouper 1b" is actually fresh data put into your new "Auto fill serial number and Search List V2". It contains about 113 rows of data from a 2500+ row file. I also did the test on this 113 row data and the result is same as what I got them from using the larger data sheet. Shown here are examples of tests. You may like to follow the test I made shown in the 'Illustration sheet' and see where I had gone wrong. Take your time, I am really not in a hurry.
Victor (rep: 12) Jul 31, '23 at 11:08 am
@Victor
This one is puzzling. With the testing I have done I am thinking it has something to do with the data entry.
Note: macro shows last row of source table as 99 - not 113; that's because rows 100 thru 113 -- col "B" is blank.
1) I expanded my "source" table to 151 rows - no search problems.
2) I copied 8 rows from your source table to the middle of my table - search couldn't find them.
3) I added to your file, a record at row 114 - no search problems.
4) I copied 8 rows from my source table to the middle of your table - no search problems.
5) I did a search in your file for "HAPPY KENNY" and it only returned the first record - not both.
Based on these simple tests I conclude it has something to do with the data entry into your source file. But, what, that is the mystery. I will continue to chip away at this to try and find the cause.
@John
If you have any ideas, please share with us.
WillieD24 (rep: 557) Aug 1, '23 at 2:07 pm
@Victor
(All of the testing I have been doing is using only one criteria to keep it simple)

I did some more testing on the pair of "HAPPY KENNY" records. I copied the formatting of the first record to the second but no luck. However, when I deleted the hyperlink in the second record - presto, the second entry was found and added to the results table.
Another interesting thing is, if I search on a third column item (to the right of the hyperlink) the search finds both records.
What I don't have an answer for is why some hyperlinks affect the search and some don't.

Cheers   :-)
WillieD24 (rep: 557) Aug 2, '23 at 11:04 pm
Thanks Willie. I will try out what you did. I may try it on another source file. I will update you about 14 to 15 hours later as I have something urgent to settle first. Best regards.
Victor (rep: 12) Aug 3, '23 at 6:27 am
@Willie, @Victor

Took a quick look at this (so may not be sure) but think you just need two simple fixes :

1) In Sub Final_Code, change the CountIf test to allow for more than 1 instance of a criterion in B:K. E.g. with a singe criteria search for "dji" under Name, the test:
        If Application.WorksheetFunction.CountIf(Range(Cells(iSR, 2), Cells(iSR, 10)), "*" & Cells(6, 12) & "*") = 1 Then

fails since there are 2 instances of that text in B28:K28. Suggest you change it (and other similar lines) to read:
        If Application.WorksheetFunction.CountIf(Range(Cells(iSR, 2), Cells(iSR, 10)), "*" & Cells(6, 12) & "*") >= 1 Then



2) When you write any search results, that triggers the Worksheet_Change event behind Search2. I just changed the start to read:
Private Sub Worksheet_Change(ByVal Target As Range)
Exit Sub
but you may be able to delete it.
John_Ru (rep: 6232) Aug 3, '23 at 12:48 pm
(Extra to the above)....

Finally, I think in Sub Final_Code you need to change this line to read:

' clear "results table"
Range(Cells(9, 12), Cells(rtLR, 20)).Clear 'Contents

so previous formatting is removed too. Finally, cell L7 has a typo and should read "Search Result:"

Hope this helps.
John_Ru (rep: 6232) Aug 3, '23 at 12:49 pm
I saw a notification just tbat the post was updated but I don't know how- can someone explain please? 
John_Ru (rep: 6232) Aug 3, '23 at 7:24 pm
@Victor, @John

Victor,

John made a couple of good observances: a code error and a spelling mistake. Both have been corrected and the amended file has been attached to my original answer (both files have the corrected code).

The problem with the code was the CountIf  lines were checking for only one occurrance of the criteria ( = 1 ) but some hyperlinks (col "C" ) also contained the criteria found in col "B". By changing this (as suggested by John) to " >= 1 " has resolved the issue of some records not being returned.
I tested this change and I am confident that change will now yield the results you are hoping for.

Also, to change the drop-down list choices: 1) select any of the four criteria "title" fields; 2) on the Data tab of the ribbon select "Data Validation; 3) edit the "Source" field as needed and click OK.

John,
Thanks so much for reviewing the code. Your suggestion has resolved the issue of some reords not being returned/listed. It hadn't occurred to me to allow for repitition of the criteria.
The Worksheet_Change event was something Victor requested in the original post to control when/how a serial number is added.

Cheers   :-)
WillieD24 (rep: 557) Aug 3, '23 at 7:37 pm
@Willie - Glad that helped. It's often difficult to see such small things in reviewing code. 

I forgot to make one suggestion - currently the search results are shown until a new search is done; personality I'd prefer the results to be cleared if the Reset button is pressed or the user changes any of the criteria (since the results won't then reflect the criteria). 

@Victor - Please confirm that Willie's revised file now works as you expect.
John_Ru (rep: 6232) Aug 4, '23 at 1:46 am
Thank you Willie & John. It has been very hard work from both of you and I am very much appreciated. Tested the latest "Search Soon Grouper 1c.xlsm" file. May be some little adjustment is still needed on hyperlink. (1) I used it on a completely hyperlink-free source file. The result returned is 100% perfectly okay. (2) On this latest file, if you search "grouper" only 2 out of 3 records appeared. Search "soon" 12 out of 15 records appeared. (3) I input a fresh 200-row source file with hyperlink, 3 out of 8 searches had error result. I think we will get the solution very soon. Best regards.
Victor (rep: 12) Aug 4, '23 at 3:10 am
Victor- when you search for "soon" say, are the missing instances the ones containing "Soon" perhaps? If so, there's an easy fix.

I'll try to find time later to look again. 
John_Ru (rep: 6232) Aug 4, '23 at 3:20 am
Hi John, in Sheet 2 of "Search Soon Grouper 1c.xlsm", I had highlighted all the 15 records of "soon hock"  and 5 cells but 3 records of "grouper" appearances. none of  the "soon"s is  in Cap.
Victor (rep: 12) Aug 4, '23 at 5:14 am
Victor. I think I've found the cause of the missing records. Don't have time to fully test it (e.g. on hyperlinks) and write it up but will do so over the weekend,

Just to be sure you understand that Willie's code performs an AND function on the criteria- if you add 2 criteria, it gives only records where both criterion 1 ANDS criterion 2 include their respective values.
John_Ru (rep: 6232) Aug 4, '23 at 2:17 pm
@Victor, @John

I found the problem ! It has nothing to do with upper/lower case used in the criteria because at the top of the macro is: Option Compare Text   ' not case sensitive. The problem was in how I coded incrementing the row number for the “results” table. I made the assumption that the “Name” column would never be empty. When a record without a “name” was copied, the row number would be calculated wrongly resulting in the last record with a blank “name” being overwritten with the new record. I have changed and moved the code to happen after data is copied (before the “End If” line)
Now, if you search for "soon", "Soon", or"SoOn" all 15 records get listed.
(I'm glad I have column "A" of copied rows filled with yellow; this made chasing this down much easier)

Cheers   :-)   I have replaced the files in my original answer with the updated files.
WillieD24 (rep: 557) Aug 4, '23 at 2:46 pm
@Willie - I came to the same conclusion (having seen result rows being overwritten). I recalculated the last results row (using current region / rows count) and got the missing rows 

I also realised that my >=1 suggestion yesterday was flawed (and bypassed the criteria match). I fixed that (and shaded matched results cells) but didn't check with hyperlinks   If Victor is okay with your revision, I won't bother to share that 
John_Ru (rep: 6232) Aug 4, '23 at 3:29 pm
@Victor, @John

I discovered that the code for clearing the "results" table needed to be revised since there can be blanks at the end of the first column ("L") which results in some rows not being cleared. Initially I didn't expect there to be any blanks in "Col L". I have revised the code so all rows get cleared.

The revised files are attached to my initial answer.

Also, I have not noticed any problems with the revised "CountIf" code ( >= ); all seems fine.

Cheers   :-)
WillieD24 (rep: 557) Aug 5, '23 at 12:35 am
First, a salute and big thank you to both of you for coming out with this solution. ( Search Soon Grouper 1d.xlsm) But, and this is just a small 'but'. I think there is still little bug somewhere. I tested it with a 2500+ row source file with hyperlink. 3 out of the 17 searches had error result like what happened to the 'soon' issue earlier, and the 3 errors are: 28 appeared out of 52 records, 27/32 and 38/54.
On the other hand, in the other 14 searches on this same source file, some consist larger number record counts like 112, 100, 81, and 76 records and these had no problem at all. Please enjoy your weekend first and continue next week. Best regards.
Victor (rep: 12) Aug 5, '23 at 2:46 am
Victor. It's a rainy day here so Iill try to fix things if I can BUT I really need you to confirm what you want.

The title of your question suggests you might want to search from the same string across multiple columns (but your battery, USA examples don't).

Willie's solution (with CountIf) has multiple input cells and seemed to want to filter data based on an AND combination of 1 or more columns based on each criterion/value. Version 1d (with my >=1 suggestion) however doesn't do that e.g. if you have Criterion 1 set to Made and value set to soon,  you get those PLUS records where sooon doesn't appear in Made but does in Products.

Am I right to think you want a solution which delivers only matches for Criterion1 (AND Criterion 2  AND 3 etc.)?
John_Ru (rep: 6232) Aug 5, '23 at 6:04 am
Just like finding the last row in the "results" table had to be revised to account for blanks at the bottom, the code for determining the last row of the "source" table had to be revised. This revision should take care of the missing records. The files in my original answer have been replaced with these revised files.

The order of choosing criteria does not matter. The search code does not refeence the criteria "title".  A search can be performed without selecting a category. Selecting a category is there only to serve as a reference as to which category the criteria should be in. (and was designed as per the original sample table, which is different than the current table). The search does not care which column the criteria is in but only that Row "X" contains the criteria somewhere.

Let me know if this finally solves your issues. If there are still "tweaks" needed let me know.

Cheers   :-)
WillieD24 (rep: 557) Aug 5, '23 at 1:03 pm
@Victor - I hope Willie's new code fix sorts things for you

@Willie - You said "Selecting a category is there only to serve as a reference as to which category the criteria should be in". I'd avoid that but if Victor wants that, who am I to object? 
John_Ru (rep: 6232) Aug 5, '23 at 3:44 pm
Sorry, I think it was my fault that I did not express and communicate well in the Illustration sheet.     Yes, until now, this solution is what I wanted and it is working perfectly well on the "Search1" i.e. battery, USA.     Currently, there are 5 'soon' in 'Products' column and 10 'soon' in 'Made' column. (Total = 15 records)     Followings are the expected search result:     (1) Search Criteria 1 (C1) Name,soon = 0 (records);  (2) C1 Department,soon = 0;  (3) C1 Products,soon = 5;  (4) C1 Made,soon = 10;   (5) C1 Products,soon + C2 Made,soon = 0  because no record has 'soon' in both columns together. (6) C1 Name,soon + C2 Department,soon = 0;  (7)  C1 Name,soon + C2 Department,soon + C3 Products,soon = 0;   (8) C1 Name,soon + C2 Department,soon + C3 Products,soon + C4 Made,soon= 0.       In the next scenario, add 4 records onto the same file in which 2 records with soon in both Department and Products column while the other 2 only have soon in Department column. (now total = 19 records)     (9) C1 Department,soon = 4;    (10) C1 Department,soon + C2 Products,soon = 2;   (11) C1 Products,soon = 7;   C1 Made,soon = 10;   (12) C1 Products,soon + C2 Made,soon = 0;  (13) C1 Department,soon + C2 Products,soon + C3 Made,soon = 0;    (14) C1 Name,soon + C2 Department,soon + C3 Products,soon + C4 Made,soon = 0.     I hope this time I explained better.
Victor (rep: 12) Aug 5, '23 at 5:13 pm
Victor. Thanks for that but it's too late here for me to focus! (Sleep beckons)

In summary , are you now happy with Willie's revised solution? 
John_Ru (rep: 6232) Aug 5, '23 at 6:33 pm
To add onto what I posted earlier, may I suggest that we keep cells L6 to O6 permanently refer to Criteria search in corresponding columns B to E.     In other words, I can type-in any criteria word into any one to four of cells from L6 to O6 and start the search.    For example, I type 'Kenny' in cell L6 and 'fish' in cell N6, it will only search for kenny in column B and fish in column D and, ignore or bypass column C and E then return the result. Regards.
Victor (rep: 12) Aug 5, '23 at 6:58 pm
Victor,
I think you may be misinformed/misunderstanding as to what a record is. A record does not refer to each instance of a word (criteria) but rather a row of data. That row may contain the criteria (such as “fish”) three times (row 30; col’s B, C, & D) but that is one record – not three. If you search for “fishing” the complete word must be found, records with only “fish” will not be returned. By entering a criterion in L6, the search looks for that anywhere in the row col B thru J – not just col B. When choosing to use multiple criteria they must be entered in order – L6, M6, N6, O6. I have amended the Data Validation error message to properly explain this. By entering “fish” in all four criteria causes the search to look for “fish” four times in a given row. By locking “L6” to col B, “M6” to col C, etc. is possible but would require a complete re-write of the search code, which would need to include a check to not repeat copying a record. ( ex: finding “fish” in col “B” that row would be copied to the results, then when found in col “C” that same row would be copied again).   Now you need to decide how much more complex you want this to be.

Cheers   :-)
WillieD24 (rep: 557) Aug 5, '23 at 9:11 pm
Hi Willie, thank you for taking time to explain all these things to me as you may remember in my reply to you much earlier then, that I am not familiar with either worksheet formulas or with VBA. In fact, I am more fond of having the locking “L6” to col B, “M6” to col C, etc method. Still, wishing you can do it for me if you have the time. For now, I will mark answer "Search Soon Grouper 1d2.xlsm" as selected and move on. My thanks to John as well and best regards.
Victor (rep: 12) Aug 6, '23 at 2:43 am
Oh, I clicked the Answer button and there is no response. What should I do next please?
Victor (rep: 12) Aug 6, '23 at 2:48 am
Victor,
The only thing you might be able to do is go to the "Selected Answer" section at the top of this discussion and give it an "up vote" by clicking on the "up" arrow at the left.
This has been a nice learning challenge for me. But, as for re-writting the search code, I don't foresee myself expending that amount of effort.
John, thanks too for your help along the journey.

Cheers   :-)
WillieD24 (rep: 557) Aug 6, '23 at 8:39 pm
@Willie- a user can only vote (up) if their Reputation is 20 or more. I'll vote you up, given the huge effort here.

@Victor- I've offered a simpler Answer but please don't mark it as Selected (which would detract from Willie)
John_Ru (rep: 6232) Aug 7, '23 at 5:42 pm
Add to Discussion
0

Victor

Willie's answer (based on a VBA solution) led to a very long discussion (possibly a Forum record!) which included your late statement "I am not familiar with either worksheet formulas or with VBA."

You may not be aware of a very powerful function in Excel- autofilters.

In the attached file, I selected columns B:F (autofilter can go worng when you have lots of blank rows) and clicked Autofilter (under ribbon Home/ Editing/ Find & Select).

This adds down arrows on the first row; when you click them, you see a list of the now visible values (alphabetically sorted, without duplicates) and you can untick/uncheck SelectAll and tick/check just the values you want to see. More importantly you can specify powerful filters (which depend on the values in a column). E.g. in column D I have chosen a Text Filter of Contains "soon": it shows a different symbol (like a dot and a funnel shape" to show the list is filtered by that column.

The filters for other columns will then show only values from the rows where D contains "soon" but you can clear that filter and all the values will be shown.

Alternatively you can add further filters on other columns (e.g. between 2 dates for column H or I) and the list (and autofilter values) will reduce further. You can print a page like that.

I was surprised that Don doesn't seem to have done a tutorial on basic autofilter but here's the Microsoft guidance: Use AutoFilter to filter your data

Hope this helps but if so, in this case do NOT mark this Answer as Seelected- Willie deserves the Reputation Points for his huge effort.

Discuss

Discussion

Hello John, a whole hearty big thankyou from this not well educated 77-year-old-man for bringing me this solution. It is so much simpler and easy to use. I am so shameful that after using Excel for more than 30 years, I do not even know that there is this such filter thing exist and is so useful. This is really what I needed. Also, you are right, I got no permission to click the up or down arrow on answer. Best regards.
Victor (rep: 12) Aug 8, '23 at 2:58 am
Pleased to have helped, Victor.

I'm amazed you're using Excel at your age but there is no shame in in not knowing about this feature- Excel has so many functions and features that we all learn new things all the time, me included. As Willie said, there are many ways to solve problems in Excel  (but sometimes we assume that the easiest solution was known by the user/ has been tried).
John_Ru (rep: 6232) Aug 8, '23 at 5:59 am
It's a function with many possibilities (I suggest you play/ investigate) and VBA can be used to make it even more advanced. If you get stuck, please ask a new question. 
John_Ru (rep: 6232) Aug 8, '23 at 6:03 am
Thanks for your encouragement to explore more into Excel. Sure, I will ask a new question whenever I get stuck again. Goodbye for now and all the best going forward.
Victor (rep: 12) Aug 8, '23 at 9:54 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login