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 :-)
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: 5572)
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.
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: 5572)
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.
@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 :-)
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: 5572)
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: 5572)
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: 5572)
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 :-)
@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: 5572)
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: 5572)
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: 5572)
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.
@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: 5572)
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 :-)
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: 5572)
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 :-)
@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: 5572)
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: 5572)
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 :-)
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 :-)
@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: 5572)
Aug 7, '23 at 5:42 pm
