XLA routines: EE_SortTwoRangesOnCommonIds

Nick's picture
EE_SortTwoRangesOnCommonIds - Advanced sub routine to find out what are common and what are missing between 2 data sets
Sub EE_SortTwoRangesOnCommonIds(rngTableWithHeader1 As range, rngTableWithHeader2 As range, strColName As String)
'a sub that takes 2 ranges of data, with a common unique ID, and sorts both sets
'of data so that the common IDs are at the top in the same row on both sheets, and the ones that
'don't match are at the bottom - this should be optimised so that it is very quick with huge amounts
'of data (500,000 rows)..
    Dim arr1            As Variant
    Dim arr2            As Variant
    Dim arrCommon       As Variant
    Dim strSortOrder    As String
'http://excelexperts.com/xla-routines-eeSortTwoRangesOnCommonIds    for updates on this sub routine
    arr1 = EE_GetUnique(EE_GetColElements(rngTableWithHeader1, strColName))
    arr2 = EE_GetUnique(EE_GetColElements(rngTableWithHeader2, strColName))
    arrCommon = EE_ArrayCommonElements(arr1, arr2)
    strSortOrder = Join(arrCommon, ",")
    Call EE_CustomSort(rngTableWithHeader1, strColName, strSortOrder)
    Call EE_CustomSort(rngTableWithHeader2, strColName, strSortOrder)
    Erase arr1
    Erase arr2
    Erase arrCommon
End Sub