How to merge excel data together based on variable that contains some matching elements?

[Ramsha]

How would I be able to merge these two data tables together based on the variable ID1 and ID2. As you can see, the 3 right most numbers in the Variables ID1 and ID2 will match. However I do not know how to find a solution that would merge the data sets to each other based off of ID1 and ID2. The solution should have two duplicates of City, State/ZIP, State, and ZIP. Basically I need to put these two data sets together based on the Variable ID1, ID2. 

[Kurt_Bremser]

What is the rule for matching, and which ID1 would therefore be matched with which ID2?

[Ramsha]

The New Haven observation on Table 1  should match with Portland observation Table 2. The Bainbridge observation on Table 1 should match with Fairfield observation on Table 2. Basically you are matching based on last 3 digits on the ID1/ID2 column. 

[Reeza · Accepted solution]

The SQL approach I showed in your other thread is still correct, just change the values in the SUBSTR to get only the last 3. Probably to substr(var, 4, 3)

---

@Ramsha wrote:

The New Haven observation on Table 1  should match with Portland observation Table 2. The Bainbridge observation on Table 1 should match with Fairfield observation on Table 2. Basically you are matching based on last 3 digits on the ID1/ID2 column. 

---

[Kurt_Bremser]

Use

scan(id1,2,'-')
scan(id2,2,'-')

to extract the relevant portion.