SOLVED

VBA code to find matching data in other sheets

%3CLINGO-SUB%20id%3D%22lingo-sub-2372110%22%20slang%3D%22en-US%22%3EVBA%20code%20to%20find%20matching%20data%20in%20other%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2372110%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20everyone%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%20to%20know%20how%20to%20code%20a%20macro%20for%20searching%20partial%20data%20match%20in%20other%20sheet%20(or%20other%20workbook).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%20%3A%20%3CIF%20current%3D%22%22%20sheet%3D%22%22%20name%3D%22%22%3E.%3C%2FIF%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20would%20be%20greatly%20appreciated%2C%20thanks%20in%20advance%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2372110%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2372218%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20code%20to%20find%20matching%20data%20in%20other%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2372218%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1055613%22%20target%3D%22_blank%22%3E%40Soran0311%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYour%20description%20is%20sufficiently%20vague%20that%20I%20feel%20the%20need%20to%20ask%20that%20you%20post%20an%20example%20of%20the%20worksheet(s)%20you%20have%2C%20or%20a%20mock-up%20of%20them%20if%20that's%20necessary%20to%20avoid%20posting%20confidential%20information.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt's%20entirely%20possible%20that%20you%20don't%20need%20a%20macro%3B%20Excel%20has%20some%20marvelously%20powerful%20functions%20that%20can%20retrieve%20info%20from%20other%20sheets%20or%20other%20workbooks.%20But%20either%20way--macro%20OR%20function--it%20would%20help%20to%20see%20exactly%20the%20situation.%20Without%20that%2C%20you%20can't%20expect%20more%20than%20generalizations%20in%20the%20way%20of%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2374785%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20code%20to%20find%20matching%20data%20in%20other%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2374785%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20reply%2C%20please%20find%20attached%20the%20mock-up%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20probably%20need%20a%20macro%20because%20I%20have%20more%20than%20200%20sheets%20to%20run%20the%20same%20action.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%20I%20need%20to%20fill%20the%20cell%20L8%20of%20every%20sheet%20with%20the%20address%20data%20in%20range%20D2%3AD6%26nbsp%3Bof%20addressDATA%20sheet%20according%20to%20the%20name%20matching%20(partially)%20in%20the%20range%20B2%3AB6.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20just%20use%20a%20VLOOKUP%20function%20if%20i%20had%20only%20few%20sheets%2C%20but%20it's%20not%20the%20case.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20in%20advance%20for%20your%20help%20%3CLI-EMOJI%20id%3D%22lia_grinning-face-with-smiling-eyes%22%20title%3D%22%3Agrinning_face_with_smiling_eyes%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2376286%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20code%20to%20find%20matching%20data%20in%20other%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2376286%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1055613%22%20target%3D%22_blank%22%3E%40Soran0311%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMay%26nbsp%3B%20ask%2C%20now%20that%20I've%20seen%20the%20sample%2C%20why%20there%20are%20more%20than%20200%20sheets%20in%20each%20of%20which%20cell%20L8%20needs%20to%20be%20filled%20with%20that%20address%20data%20from%20the%20database%20sheet%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20don't%20mean%20to%20be%20impertinent%20in%20asking.%20There%20may%20be%20entirely%20legitimate%20reasons%2C%20and%20if%20so%20perhaps%20a%20macro%20is%20indeed%20the%20solution.%20I'm%20not%20a%20macro%20person%3B%20I%20am%20more%20of%20a%20design%20person%20who%20strives%20to%20use%20Excel%20with%20its%20built-in%20functions%2C%20often%20finding%20that%20there%20are%20far%20more%20elegant%20solutions%20than%20(what%20I%20will%20refer%20to%20as)%20the%20%22brute%20force%22%20of%20a%20macro.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20in%20this%20case%2C%20where%20all%20of%20the%20subsequent%20sheets%20are%20exactly%20the%20same%20format%2C%20it%20makes%20me%20wonder%20whether%20they%20could%20all%20be%20consolidated%20into%3A%3C%2FP%3E%3CUL%3E%3CLI%3Eanother%20single%20database%2C%20where%20it%20would%20be%20easy%20(as%20you%20note)%20to%20write%20a%20simple%20LOOKUP%20function%20for%20the%20data%20to%20be%20retrieved%20from%20the%20address%20database%2C%20PLUS%3C%2FLI%3E%3CLI%3Ea%20nicely%20formatted%20%22dashboard%22%20that%20mimics%20the%20layout%20you%20have%20on%20each%20of%20these%20individual%20sheets%20now%3C%2FLI%3E%3C%2FUL%3E%3CP%3EAs%20I%20said%2C%20there%20may%20be%20a%20reason%20why%20that%20can't%20be%20done%2C%20but%20I%20view%20part%20of%20my%20(volunteer)%20job%20here%20in%20the%20techcommunity%20to%20raise%20such%20questions%20about%20design%20rather%20than%20simply%20give%20answers%2C%20where%20I%20think%20such%20questions%20are%20warranted.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2385259%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20code%20to%20find%20matching%20data%20in%20other%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2385259%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1055613%22%20target%3D%22_blank%22%3E%40Soran0311%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20your%20workbook%2C%20click%20on%20the%20Sheet%20next%20to%20the%20AddressDATA%20Sheet%20then%20right%20click%20and%20click%20Select%20All%20Sheets%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Yea_So_0-1621973884705.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F283669i5C9E33A63016732F%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Yea_So_0-1621973884705.png%22%20alt%3D%22Yea_So_0-1621973884705.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Ethen%20press%20ctrl%20and%20keep%20holding%20it%20then%20click%20the%20AddressDATA%20Sheet%20to%20exclude%20it%20from%20the%20selection.%3C%2FP%3E%3CP%3Ethen%20click%20on%20cell%20L8%2C%20then%20paste%20the%20formula%20below%20and%20press%20enter%3A%3C%2FP%3E%3CP%3E%3DVLOOKUP(CONCATENATE(LEFT(B2%2CFIND(%22.%22%2CB2)-1)%2C%22%20%22%2CRIGHT(B2%2CLEN(B2)-FIND(%22.%22%2CB2)-1))%2CAddressDATA!%24B%242%3A%24D%246%2C3%2C0)%3C%2FP%3E%3CP%3Ethen%20check%20the%20result%20on%20the%20first%20few%20sheet%20tabs%20to%20see%20if%20the%20result%20is%20correct.%3C%2FP%3E%3CP%3ETry%20it%20in%20your%20Mockup1.xlsx%20sheet%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2385327%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20code%20to%20find%20matching%20data%20in%20other%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2385327%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1055613%22%20target%3D%22_blank%22%3E%40Soran0311%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20your%20workbook%2C%20click%20on%20the%20Sheet%20next%20to%20the%20AddressDATA%20Sheet%20then%20right%20click%20and%20click%20Select%20All%20Sheets%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Yea_So_0-1621974494989.png%22%20style%3D%22width%3A%202160px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F283672iAF095F6D8CFD964B%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Yea_So_0-1621974494989.png%22%20alt%3D%22Yea_So_0-1621974494989.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethen%20press%20ctrl%20and%20keep%20holding%20it%20then%20click%20the%20AddressDATA%20Sheet%20to%20exclude%20it%20from%20the%20selection.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Yea_So_1-1621974606158.png%22%20style%3D%22width%3A%202160px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F283675i9A5519009613B8C7%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Yea_So_1-1621974606158.png%22%20alt%3D%22Yea_So_1-1621974606158.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Ethen%20click%20on%20cell%20L8%2C%20then%20paste%20the%20formula%20below%20and%20press%20enter%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3E%3DVLOOKUP(CONCATENATE(LEFT(B2%2CFIND(%22.%22%2CB2)-1)%2C%22%20%22%2CRIGHT(B2%2CLEN(B2)-FIND(%22.%22%2CB2)-1))%2CAddressDATA!%24B%242%3A%24D%246%2C3%2C0)%3C%2FPRE%3E%3CP%3E%3CSTRONG%3E%3CEM%3E(Note%3A%20Make%20sure%20to%20adjust%20the%20range%20of%20the%20formula%20before%20applying%20to%20the%20actual%20workbook%3C%2FEM%3E%3C%2FSTRONG%3E)%3C%2FP%3E%3CP%3Ethen%20check%20the%20result%20on%20the%20first%20few%20sheet%20tabs%20to%20see%20if%20the%20result%20is%20correct.%3C%2FP%3E%3CP%3ETry%20it%20in%20your%20Mockup1.xlsx%20sheet%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hello everyone,

 

I would like to know how to code a macro for searching partial data match in other sheet (or other workbook).

 

For example : <If current sheet name (or string of cell A1) is (partially) found in range "A1:C20" of sheet 1 (database sheet), show value in cell C1 of current sheet>.

 

Any help would be greatly appreciated, thanks in advance

3 Replies

@Soran0311 

 

Your description is sufficiently vague that I feel the need to ask that you post an example of the worksheet(s) you have, or a mock-up of them if that's necessary to avoid posting confidential information.

 

It's entirely possible that you don't need a macro; Excel has some marvelously powerful functions that can retrieve info from other sheets or other workbooks. But either way--macro OR function--it would help to see exactly the situation. Without that, you can't expect more than generalizations in the way of help.

@mathetes 

 

Thank you for your reply, please find attached the mock-up file.

 

I would probably need a macro because I have more than 200 sheets to run the same action.

 

For example I need to fill the cell L8 of every sheet with the address data in range D2:D6 of addressDATA sheet according to the name matching (partially) in the range B2:B6.

 

I would just use a VLOOKUP function if i had only few sheets, but it's not the case.

 

Thank you in advance for your help

best response confirmed by Soran0311 (Occasional Contributor)
Solution

@Soran0311 

 

May  ask, now that I've seen the sample, why there are more than 200 sheets in each of which cell L8 needs to be filled with that address data from the database sheet?

 

I don't mean to be impertinent in asking. There may be entirely legitimate reasons, and if so perhaps a macro is indeed the solution. I'm not a macro person; I am more of a design person who strives to use Excel with its built-in functions, often finding that there are far more elegant solutions than (what I will refer to as) the "brute force" of a macro.

 

So in this case, where all of the subsequent sheets are exactly the same format, it makes me wonder whether they could all be consolidated into:

  • another single database, where it would be easy (as you note) to write a simple LOOKUP function for the data to be retrieved from the address database, PLUS
  • a nicely formatted "dashboard" that mimics the layout you have on each of these individual sheets now

As I said, there may be a reason why that can't be done, but I view part of my (volunteer) job here in the techcommunity to raise such questions about design rather than simply give answers, where I think such questions are warranted.