How to index/match areas on worksheets?

[foggy1974]

Hi all,

I'm usually rather adept at Excel but not VBA; I think I have an issue that may only be solvable for VBA and would love your input as I'm really quite stuck!!!!

I've trawled the net looking for solutions, but everything works the wrong way round (as explained below)



I have two worksheets, "W_MasterArea" and "W_SubAreas"

"W_MasterArea"
------------------

"W_MasterArea" col A contains a list of "master area" entries in Column A

ARegion
My_Region
Region4
Another_Local_Area
The_Place_Around_The_Corner

I've no control over the length of this data, the number of sections (i.e. one, two, three or more hyphens) etc

"W_MasterArea" col B contains the name a person who looks after Col B (a "Rep"):

Steve
Dan
Malcolm
Robin
Terry

-----------------


"W_SubAreas"
----------------
"W_SubAreas" contains a list of sub-areas, based on those in "W_MasterArea"

e.g.

AA_My_Region_1
AA_My_Region_2
B_My_Area_aaa
C_My_Area_aab_a1
EEE_12_The_Area_Around_The_Corner_1_2_AA_cde

In this SECOND worksheet, for each row, I need to scan through each "Master Area" and, if any Master_Area forms part of the "sub_area", display the name of the person looking after that area....


I've attached a spreadsheet showing this - it's column B on W_SubAreas that I need to populate....



My issue is that everything I've seen would allow me to populate column B on W_MasterArea from column B on W_SubAreas, but not vice versa.....

Any help would be HUGELY appreciated as currently I'm having to do this manually and the fact that I can't come up with a solution is driving me nuts...!!!

[shg]

I B2 and copy down (I abbreviated the sheet names) this array formula:

=INDEX(M!$B$2:$B$6, MATCH(TRUE, LEN(SUBSTITUTE(S!A2, M!$A$2:$A$6, "")) < LEN(S!A2), 0) )

Array formulas MUST be confirmed with Ctrl+Shift+Enter, not Enter. You'll know you did it correctly if curly braces appear around the formula in the Formula Bar; you cannot type in the braces directly.

[NBVC]

Try:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER and copied down.

[shg]

Much nicer, NBVC.

[foggy1974]

A very elegant solution that works perfectly for my application - I'm very, very grateful.

I hope to become a regular visitor (and contributor) here :-)

With kindest regards,

Steve