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:
Efficient:
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.)
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.
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
udf_Sort_FullSheet
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
LoadArrayMatchA
LoadArrayMatchB
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
MatchData_BtoA
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