Match Data in 2 Columns - Load Blank Array with 'Relative' Data from Matched "Key"

Some people swear by Vlookup and others love Index,Match. 
Personally, I don't like either option so I have come up with a method for matching 2 data points and then copying data from the second sheet to the first sheet if a match is found.

There are 3 keys to making the process Efficient, Accurate and Dynamic.

Accurate:

• The text values to match are always converted to UCase so that the value "TestMe" on the first sheet will match to "TESTme" on the second sheet.
• Both columns containing the "Keys" are sorted alphabetically

Efficient:

• An array is created that contains Numeric values 0-9, and Alpha values "A" to "Z".  One more location in the Array will store information for all other characters.
• Strings to match are reduced to the 1st character only so that smaller sub-loops are used.  This is especially important when your lists to match start to number in the 1000's.

Dynamic:
Sometimes your headers will not always be in Row 1
Whenever possible, you don't want to change the source data.  This method does all sorting on a separate blank sheet so that your original sheets only get "read", not manipulated.  (With the exception of dumping the desired results back to your original Sheet.)

---

Custom Functions: Tools

This method relies on some custom functions created to handle each step of the process.

InsertTabOrClear

• This accepts a tab name.  If the tab is found, it calls another function to clear the data.  If it is not found, it creates a new tab.
  
• udfReset99
  
• This is the function that clears data on an existing tab
  

udf_Sort_FullSheet

• This accepts input used to define the sort.  If the header is located on the first row, it uses a Boolean so that the Header data is not included in the list of values to match.  (If the header is on a row other than Row 1, it is not as useful.
  
• It defines a single column to use for sorting plus it defines the entire sheet range (starting with Cell A1 through the last cell/column)

Custom Functions: Steps-Start to Finish

SupersonicMethod
This is the Sub that defines all of the objects, then calls each of the functions to complete the process

Create123ABC

• This creates a Public Array [a123ABC] with 37 rows and 8 columns
• Rows 1-10 are used to hold Numbers 0-9
• Rows 11-36 are used to hold Letters (UCASE) A-Z
• Row 37 is used to hold all other characters

LoadArrayMatchA

• This creates a Public (2 column) Array [aMatchA] containing the list of data from our 1st Sheet
• The list is copied to a blank sheet, loaded into an array with original row numbers, sorted and stripped of all data in rows up to and including the header
• The result is a Sorted list of ONLY values to match

LoadArrayMatchB

• This creates a Public (2 column) Array [aMatchB] containing the list of data from our 2nd Sheet
• The list is copied to a blank sheet, loaded into an array with original row numbers, sorted and stripped of all data in rows up to and including the header
• The result is a Sorted list of ONLY values to match

a123ABC_LoadRows
This uses one of the Public Arrays [aMatchA][aMatchB] and loads the other Public array [a123ABC] with the First/Last Row + a Concatenated String

• aMatchA: Col2: First Row  Col3: Last Row Col4: Concatenated String (Input determines Column to be loaded)
• aMatchB: Col5: First Row  Col6: Last Row Col7: Concatenated String (Input determines Column to be loaded)

MatchData_BtoA

• This uses our Public Array [aMatchA] to check the first digit of the String and then compare it to our Public Array [a123ABC] to initiate smaller sub-loops that check for matches to our original Sheet 2 Array.
• When a match is found, the Blank Array for Sheet 1 is loaded with Offsetting data from the same Row on Sheet 2