Resources
  • Home
  • December-Chrono
  • Psalm136Personalized
  • ExcelResources
    • 99PublicFunctions
    • aDelimitedCodeTemplate
    • aCodeTemplate
    • aBetter_Code_Template
    • ApplyFormulaValue
    • ArrayPublic
    • ArrayMatchCopyRow
    • Capitalization
    • Colors
    • DataTypes
    • DeleteRows
    • Filter
    • FormatStandard
    • Forms
    • Grid
    • Headers
    • ImageScrape
    • InputBox
  • ExcelResources2
    • Like
    • List Review
    • MatchAndReturn
    • mod_Import
    • Numbers
    • PDF_Parse
    • RandomGenerator
    • iRe1
    • Rept
    • RelatedItemsLIst
    • RomanNumerals
    • SheetSplitter
    • Sort
    • Split Method
    • String_DataType
    • TabDelimited
    • Tab_InsertNew_or_ClearExisting
    • Unique_List
      • UniqueLIsts_FilterMethod
  • Excelresources_Files
    • FileLists
    • FileTextParse
    • Folders
  • excelresources_WebScrape
    • CreateList_1-Href_2-Jpg
    • TableData_Load_PlugPlay
    • CreateList_MultiPage_Image_Link
      • ImageDownload_WebLink_List
    • TableData_Load_ClassDiv
    • TableData_Load_ClassSpan
  • Houseboat Vacation
  • At the Foot of the King
  • Devotions
  • Mentoring
  • Inspirational Stories
    • Vacuum Lifting
  • Job Help
  • Resources
    • MS
    • Florida History
  • Contact
  • Heroscape
    • Fish
    • Playstation
  • You Tube Videos
  • House
    • Tiny Houses
    • Backyards
  • CampingResources
    • CampingLand
    • LED Throwies
  • Interesting
  • Recipes
  • Xbox
  • Skydiving
  • ReadingPlan-5Year
  • Desoto
  • BibleStudy
  • HurricaneIrma
  • Home
  • December-Chrono
  • Psalm136Personalized
  • ExcelResources
    • 99PublicFunctions
    • aDelimitedCodeTemplate
    • aCodeTemplate
    • aBetter_Code_Template
    • ApplyFormulaValue
    • ArrayPublic
    • ArrayMatchCopyRow
    • Capitalization
    • Colors
    • DataTypes
    • DeleteRows
    • Filter
    • FormatStandard
    • Forms
    • Grid
    • Headers
    • ImageScrape
    • InputBox
  • ExcelResources2
    • Like
    • List Review
    • MatchAndReturn
    • mod_Import
    • Numbers
    • PDF_Parse
    • RandomGenerator
    • iRe1
    • Rept
    • RelatedItemsLIst
    • RomanNumerals
    • SheetSplitter
    • Sort
    • Split Method
    • String_DataType
    • TabDelimited
    • Tab_InsertNew_or_ClearExisting
    • Unique_List
      • UniqueLIsts_FilterMethod
  • Excelresources_Files
    • FileLists
    • FileTextParse
    • Folders
  • excelresources_WebScrape
    • CreateList_1-Href_2-Jpg
    • TableData_Load_PlugPlay
    • CreateList_MultiPage_Image_Link
      • ImageDownload_WebLink_List
    • TableData_Load_ClassDiv
    • TableData_Load_ClassSpan
  • Houseboat Vacation
  • At the Foot of the King
  • Devotions
  • Mentoring
  • Inspirational Stories
    • Vacuum Lifting
  • Job Help
  • Resources
    • MS
    • Florida History
  • Contact
  • Heroscape
    • Fish
    • Playstation
  • You Tube Videos
  • House
    • Tiny Houses
    • Backyards
  • CampingResources
    • CampingLand
    • LED Throwies
  • Interesting
  • Recipes
  • Xbox
  • Skydiving
  • ReadingPlan-5Year
  • Desoto
  • BibleStudy
  • HurricaneIrma
Connect on Facebook

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
Powered by Create your own unique website with customizable templates.
  • Home
  • December-Chrono
  • Psalm136Personalized
  • ExcelResources
    • 99PublicFunctions
    • aDelimitedCodeTemplate
    • aCodeTemplate
    • aBetter_Code_Template
    • ApplyFormulaValue
    • ArrayPublic
    • ArrayMatchCopyRow
    • Capitalization
    • Colors
    • DataTypes
    • DeleteRows
    • Filter
    • FormatStandard
    • Forms
    • Grid
    • Headers
    • ImageScrape
    • InputBox
  • ExcelResources2
    • Like
    • List Review
    • MatchAndReturn
    • mod_Import
    • Numbers
    • PDF_Parse
    • RandomGenerator
    • iRe1
    • Rept
    • RelatedItemsLIst
    • RomanNumerals
    • SheetSplitter
    • Sort
    • Split Method
    • String_DataType
    • TabDelimited
    • Tab_InsertNew_or_ClearExisting
    • Unique_List
      • UniqueLIsts_FilterMethod
  • Excelresources_Files
    • FileLists
    • FileTextParse
    • Folders
  • excelresources_WebScrape
    • CreateList_1-Href_2-Jpg
    • TableData_Load_PlugPlay
    • CreateList_MultiPage_Image_Link
      • ImageDownload_WebLink_List
    • TableData_Load_ClassDiv
    • TableData_Load_ClassSpan
  • Houseboat Vacation
  • At the Foot of the King
  • Devotions
  • Mentoring
  • Inspirational Stories
    • Vacuum Lifting
  • Job Help
  • Resources
    • MS
    • Florida History
  • Contact
  • Heroscape
    • Fish
    • Playstation
  • You Tube Videos
  • House
    • Tiny Houses
    • Backyards
  • CampingResources
    • CampingLand
    • LED Throwies
  • Interesting
  • Recipes
  • Xbox
  • Skydiving
  • ReadingPlan-5Year
  • Desoto
  • BibleStudy
  • HurricaneIrma