0

I am pulling information from 2 different databases. The databases use different naming protocol for the exact same item/specified placement however they always have certain components of the name in common. The length of these names can vary throughout each of the databases (see the pic below) so I don't think counting characters would help. I need a formula (probably a vlookup/match/index of some sort) to pair up the names from the 2nd database name with the 1st database name and then place it in the adjacent column(B2) on sheet1. Until this point I've had to match, copy, and paste the pairs manually from one sheet to the other and it takes FOREVER. Any help would be much appreciated!!!

For example:

Database1 Name in Sheet1,A2: 728x90_Allstate_629930_ALL_JUL_2013_MASSACHUSETTSAUTO_BAN_MSN_ROSMSNAUTOSMASSACHUSETTS_7.2.13

Database2 Name in Sheet2, A13: BAN_MSN_ROSMSNAUTOSMASSACHUSETTS728X90_728X90_DFA

Common Factors: "ROSMSNAUTOSMASSACHUSETTS" & "728X90" Therefore A2 and A13 need to pair up

In some cases, Database 1 and 2 will have a common name aspect but sizing will be different. They need to have BOTH aspects in common in order to be paired so I would NOT want the below example to pair up.

Database1 Name in Sheet1,A2: 728x90_Allstate_629930_ALL_JUL_2013_MASSACHUSETTSAUTO_BAN_MSN_ROSMSNAUTOSMASSACHUSETTS_7.2.13

Database2 Name in Sheet2, A12: BAN_MSN_ROSMSNAUTOSMASSACHUSETTS300X250_300X250_DFA

Common Factor: Only "ROSMSNAUTOSMASSACHUSETTS" matches. "728x90" is not equal to "300X250" - Sizing is different so they should not be paired.

2
  • 1
    Examples are great, but they don’t take the place of an explanation. I guess you’re saying that you want to match pairs of text cells that have two substrings in common, case insensitive. Note that your introductory, explanatory paragraph says “certain components … in common” (it doesn’t say two). Are they both case insensitive? Do they have to match in reverse order? ... (continued) Nov 6, 2013 at 21:26
  • 1
    (continued) ... How do you define a match? Does “different” match “entropy” because they both contain “ent”, or do you require a match that goes from underscore to underscore (or a digit)? Similarly, does “80x160” match “280x160” and, if not, how can Excel tell? Why don’t you list “BAN” and “MSN” as common substrings in the first example? Etc. Nov 6, 2013 at 21:27

1 Answer 1

0

If there is at least one consistent aspect of each naming convention, you can use wildcards and formulae to find a match.

For example, if you know that the naming convention for DB1 always follows the size_company_date_... format, you can use something like this to find the size in the first DB ID:

=MID(A1,1,FIND("_",A1,1)-1)

where MID returns the size by looking at a cell (A1) and starting with the first character returns a certain number of characters (which is when you use Find to find the position number of the first underscore of the string minus 1).

If you knew, then, where the second matching value always was in the naming convention, you could use a formula to find the second part of your match. This article gives a tip on this.

Using that knowledge, if you knew that your second matching value was always nine underscores from the left, you could use this formula to return the starting position of your second matching value:

=FIND(CHAR(1),SUBSTITUTE(G1,"_",CHAR(1),9))+1

So now that we know how to find both the size and other matching value, we can use a Vlookup and wildcards to match the rest. (I apologize for the long formula...)

=VLOOKUP("*" & MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,"_",CHAR(1),9)),FIND(CHAR(1),SUBSTITUTE(A1,"_",CHAR(1),10))-FIND(CHAR(1),SUBSTITUTE(A1,"_",CHAR(1),9))) & "*" & MID(A1,1,FIND("_",A1,1)-1) & "*",B:B,1,FALSE)

This formula makes the following assumptions:
1. The Database1 naming convention always starts with the size.
2. The Database1 naming convention always has nine underscores before the second matching value.
3. The Database1 values are in column A (the above formula references the first DB1 value in A1).
4. The DB2 values are in column B.

I used this formula and your examples in Excel 2010 and it worked for me. Good luck!

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .