Excel formula to compare partial match cells -


i looking formula.

i need compare text in 2 cells (not columns).

one of cells has last name, other cell may have part of last name additional numbers , letters (not in specific order). these pairs locate.

not of cells match, ones part of text matches, "match".

for example in cell e2= 000034568mill walli , in cell j2=wallinger should bring match, since walli in cell e2 matches part of text.

another example:

e2= benjamin p rouamba , j2=roumbamoore should bring match.

i hope makes sense , there possible formula this.

there no built-in function in excel meets need. need customize own formula in vba project module. managed program function counts number of matches. adter setting module, can use normal formula in worksheet.

if not familiar vba programming, not worry. follow instructions , copy code below vba module.

press 'alt+f11' and, in menu, select insert > module

function countpartialmatch(r1 string, r2 string, m long) long      dim n long, l1 integer, l2 integer, min integer, c integer, s1 integer, s2 integer     n = 0     l1 = len(r1)     l2 = len(r2)     min = application.worksheetfunction.min(l1, l2)      c = m min         s1 = 1 (l1 - (c - 1))             s2 = 1 (l2 - (c - 1))                 if mid(r1, s1, c) = mid(r2, s2, c) n = n + 1                 next s2             next s1         next c      countpartialmatch = n  end function

this formula requires 3 arguments:

=countpartialmatch(first_cell, second_cell, min_chrt)

the argument min_chrt minimum number of characters match must have. if state 2, count matches 2 characters, 3 characters, 4, 5, 6... reduces number of coincidences. if 1, formula count every "a" = "a", letter = letter.

please, let me know if works fine you!


Comments

Post a Comment

Popular posts from this blog

c# - Update a combobox from a presenter (MVP) -

i using mvp in project, new in mvp. have 2 comboboxes. when select option in combobox, combobox should filled new data. this action in presenter. view 'view1' in presenter, , introduced combobox1 , combobox2 properties in 'view1', because need 'datasource', 'displaymember', 'valuemember' , 'refresh()' in method below. but, when using pattern, enough send property like public string combobox2 { { return combobox1.selectedvalue.tosstring(); } } into presenter not whole combobox. how can solve problem? public void onselectedindexchangedcombobox1() { if (view1.combobox1.selectedindex == -1) { return; } datatable dt = tools.getdatatable("a path"); var query = (from o in dt.asenumerable() o.field<string>("afield") == farmerview.combobox1.selectedvalue.tostring() orderby o.field<string>("anotherfield") select...
Read more

How to understand 2 main() functions after using uftrace to profile the C++ program? -

i trying uftrace profile following simple c++ program: #include <iostream> class { public: a() {std::cout << "a created" << std::endl;} ~a() {std::cout << "a destroyed" << std::endl;} }; int main() { a; return 0; } the profile result this: # uftrace a.out created destroyed # duration tid function 2.026 [ 4828] | __cxa_atexit(); [ 4828] | main() { [ 4828] | __static_initialization_and_destruction_0() { 89.397 [ 4828] | std::ios_base::init::init(); 0.768 [ 4828] | __cxa_atexit(); 93.029 [ 4828] | } /* __static_initialization_and_destruction_0 */ 94.425 [ 4828] | } /* main */ [ 4828] | main() { [ 4828] | a::a() { 11.104 [ 4828] | std::operator <<(); 10.825 [ 4828] | std::basic_ostream::operator <<(); 24.514 [ 4828] | } /* a::a */ [ 4828] | a::~a() { 0.978 [ 4828] | ...
Read more

How to put a lock and transaction on table using spring 4 or above using jdbcTemplate and annotations like @Transactional? -

i trying put lock on table while writing on table , if happened in between roll-back . trying convert below code lock table test_g1 read; lock table test_g write; -- begin; start transaction; insert test_g1 values(143); insert test_g values(145); select * test_g1; select * test_g; rollback; select * test_g; unlock tables; how convert above code @transactional spring jdbctemplate code? @transactional(rollbackfor={dataaccessexception.class}) public void test(){ jdbctemplate.execute("insert test1 (id, nam) values (4, 'a')"); throw new dataaccessexception("error") { }; } here trying throw error, insert statement should rollback it's not happening . thanks edit-1 attaching code , doing in jdbcdaoimpl.java , mentioned problem comment above test() . app.java package com.cgiri.javabrains.spring4; import org.springframework.beans.factory.annotation.autowired; import org.springframework.context.applic...
Read more