excel - Solution to Find string from a list , to another string VBA -


what is, have excel load every day football, tennis

[daily list] : https://www.dropbox.com/s/45ulku7val6q6lj/sample.jpg?dl=0

additionally have excel file/sheet name of tennis players, football teams names, football leagues in format :

[redlist] : https://www.dropbox.com/s/h9nqx2zpq696o38/redlist.jpg

of course can change format of list test list.

now want check second list (red list) if of teams names appear on main sheet names , highlight row, facing issues additional parameters .

one way tried sumproduct function : 

=if(sumproduct(countif(a4,"*"&$f$2:$f$6&"*"))=0,"none found",index($f$2:$f$6,match(true,index(isnumber(search($f$2:$f$6,a4)),),0)))

which works fine not working cause instance if have in red list name yankel zemel , , in daily list name appears yankel z, or zemel yankel, or zemel y. , function return false.

another issue comma between 2 names or special characters anif / salzburg ii , askÖ gmünd, blau-weiß linz ... etc

so tried figure out how solve problem.

one solution use find in vba again im not sure if work. solution little bit more tricky create access database , try store there different versions of each name, instance zemel yankel = yankel zemel = yankel z = zemel y. = yankel, zemel etc rename them unique name instance zemel yankel. truth dont know if easy dont have lot of knowledge in access , dont know if solution too.

another solution have no idea again it, use regexp

did ask ? how solve problem ?

like tom mentioned not easy problem slight twist can achieve want. did similar project yours employed method , client pretty happy arrangement.

create new sheet called, say, alias , maintain table there. this. whenever come across alias, put in table. these football games, not come across more 10 aliases (as far have seen till date). if come across more names update table.

enter image description here

now use .find loop through aliases find match.

you can try , evaluate formula in vba code when looping through aliases. example

dim rngalias range  '~~> hard coding. loop through range set rngalias = thisworkbook.sheets("alias").range("a2")  'sformula = "=if(sumproduct(countif(" & _            "a4" & _            ",""*""&$f$2:$f$6&""*""))=0,""none found""," & _            "index($f$2:$f$6,match(true,index(isnumber(search($f$2:$f$6," & _            "a4" & _            ")),),0)))"  sformula = "=if(sumproduct(countif(" & _            rngalias.address & _            ",""*""&$f$2:$f$6&""*""))=0,""none found""," & _            "index($f$2:$f$6,match(true,index(isnumber(search($f$2:$f$6," & _            rngalias.address & _            ")),),0)))"

hope gets started!


Comments

Post a Comment

Popular posts from this blog

powershell Start-Process exit code -1073741502 when used with Credential from a windows service environment -

i'm running strange behavior powershell start-process call. here call: $process = start-process ` "c:\somepath\mybinary.exe" ` -passthru ` -credential $defaultcredential ` -wait ` -workingdirectory "c:\somepath" ` -loaduserprofile if ($process.exitcode -ne 0) { #do } this call return exit code - 1073741502 . after quick search, exit code seems related generic error when program not load required dll (aka. status_dll_init_failed ). when run without -credential $credential program runs correctly. in order isolate problem, manually launched some.exe in prompt target credential , runs smoothly. so problem seems come way start-process cmdlet launch process. i found potential solutions problem tried apply no luck : link , link . would have idea of what's going on here ? edit 1: run proc mon monitoring program activities when launched directly or via powershell script. problem seems occur when loading kernelbase
Read more

twig - Using Twigbridge in a Laravel 5.1 Package -

i'm trying create laravel 5.1 package uses twigbridge instead of .php views. can normal .php views display fine, when try make use of .twig templates i'm met error. possible use twigbridge templates when creating views laravel package or pretty stuck blade , php ? edit 1: loading twig template without extending works. it's when start using {% extends 'file' %} error comes out. i error error loading /virtual/laravel/packages/username/mypackage/src/views/sample.twig: template "templates.master" not defined () in "/virtual/laravel/packages/username/mypackage/src/views/sample.twig" @ line 1. my folders username |_ mypackage |_ src |_ controllers - samplecontroller.php |_ views |_ templates - master.twig - sample.twig twig template sample.twig {% extends 'templates.master' %} {% block content %} {{ parent()
Read more

c# - LINQ join Entities from HashSet's, Join vs Dictionary vs HashSet performance -

i have hashset's each stores t, have written test application compares different relation algorithms can think of, i'm not pleased results getting. does there exist more efficient ways of achieving object relations, once testing? using system; using system.collections.generic; using system.linq; using system.text; using system.threading.tasks; using system.diagnostics; namespace linqtests { class program { static void main(string[] args) { hashset<user> usertable = new hashset<user>(); hashset<userproperty> userpropertytable = new hashset<userproperty>(); #region lets create dummy data. console.writeline("please wait while creating dummy data, can take while..."); console.writeline(""); int rows = 1000000; for(int x = 0; x < rows; x++) { random rnd = new random(); // ad
Read more