How to interleave columns and separate what is found only in each list and what is common to both?

Question

Consider the following:

I have an MS Excel spreadsheet file where I have two lists I need to combine into one, while maintaining sort order. There are names in each list that exist only in that list, and there names that are common to both.

For example:

List 1:

gitlab_ci_active_jobs
gitlab_ci_job_failure_reasons
gitlab_ci_pipeline_builder_scoped_variables_duration
gitlab_ci_pipeline_creation_duration_seconds
gitlab_ci_pipeline_failure_reasons
gitlab_ci_pipeline_processing_events_total
gitlab_ci_pipeline_size_builds
gitlab_ci_queue_operations_total

List 2:

gitlab_ci_active_jobs
gitlab_ci_build_trace_errors_total
gitlab_ci_current_queue_size
gitlab_ci_pipeline_builder_scoped_variables_duration
gitlab_ci_pipeline_creation_duration_seconds
gitlab_ci_pipeline_size_builds
gitlab_ci_queue_active_runners_total
gitlab_ci_queue_depth_total
gitlab_ci_queue_iteration_duration_seconds
gitlab_ci_queue_operations_total

The desired result:

List 1	List 2	Common
		gitlab_ci_active_jobs
	gitlab_ci_build_trace_errors_total
	gitlab_ci_current_queue_size
gitlab_ci_job_failure_reasons
		gitlab_ci_pipeline_builder_scoped_variables_duration
		gitlab_ci_pipeline_creation_duration_seconds
gitlab_ci_pipeline_failure_reasons
gitlab_ci_pipeline_processing_events_total
		gitlab_ci_pipeline_size_builds
	gitlab_ci_queue_active_runners_total
	gitlab_ci_queue_depth_total
	gitlab_ci_queue_iteration_duration_seconds
		gitlab_ci_queue_operations_total

Is there a way to do this in Excel? Doing it manually is extremely tedious and very error prone.

I don't care whether the "common" column comes first or last (if that makes things easier). I've done VBA programming in the past, but it has been like 15 years.

Answer 1

It needs the last part where, you check if the Value is in the First and the second List, Now it just writes it in a 2d Array, where the First Dimension is the value and the second is the List. Maybe I have later more time, to add the rest. When you are a little familiar with VBA It takes like 20 min. PS you can execute the SUB ListToArray, it shows you the values from the Arrays in the console. If you would like to write it in the Excel you can with Cells( Rowindex, Columindex) And I added some comments of course. Read it first and do not just execute code from strangers...

Sub ListsToArrays()
    ' Assuming the lists are in columns A and B starting from the second row
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to the name of your sheet
    
    ' Find the last row in columns A and B
    Dim lastRowA As Long
    Dim lastRowB As Long
    lastRowA = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    lastRowB = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
    
    ' Declare arrays
    Dim arrayA() As Variant
    Dim arrayB() As Variant
    
    ' Resize arrays
    ReDim arrayA(1 To lastRowA)
    ReDim arrayB(1 To lastRowB)
    
    ' Populate arrays
    arrayA = ws.Range("A1:A" & lastRowA).value
    arrayB = ws.Range("B1:B" & lastRowB).value
    
    ' Now, you have the data in the arrays (arrayA and arrayB)
  
  
    'Sort the Arrays:
    arrayA = SortArray(arrayA)
    arrayB = SortArray(arrayB)
    
    ' Example: Print the arrays in the Immediate Window
    Dim i As Integer
    
    Debug.Print "Array A:"
    For i = 1 To lastRowA
        Debug.Print arrayA(i, 1)
    Next i
    
    Debug.Print "Array B:"
    For i = 1 To lastRowB
        Debug.Print arrayB(i, 1)
    Next i
    
    'Write The Array in a 2 dim List, first Dimension is the value, second is in which List
    Dim combinedArray() As Variant
    combinedArray = CombineArrays(arrayA, arrayB)
    
    Debug.Print "Array Combined:"
    combinedArray = SortArray(combinedArray)
    
    For i = 1 To UBound(arrayA) + UBound(arrayB) + 1
        Debug.Print combinedArray(i, 1)
        
    Next i

    
    
    
    
     End Sub


Function SortArray(arr() As Variant) As Variant
    Dim temp As Variant
    Dim i As Long, j As Long
    
    ' Use bubble sort algorithm
    For i = LBound(arr) To UBound(arr) - 1
        For j = i + 1 To UBound(arr)
            If arr(i, 1) > arr(j, 1) Then
                ' Swap elements if they are in the wrong order
                temp = arr(i, 1)
                arr(i, 1) = arr(j, 1)
                arr(j, 1) = temp
            End If
        Next j
    Next i
    
    SortArray = arr End Function

Function CombineArrays(arr1() As Variant, arr2() As Variant) As Variant
    Dim combinedArray() As Variant
    Dim totalRows As Long
    Dim i As Long
    Dim counterA As Integer
    Dim CounterB As Integer
    
    ' Determine the total number of rows in the combined array
    totalRows = UBound(arr1) + UBound(arr2) + 1
    
    ' Resize the combined array
    ReDim combinedArray(1 To totalRows, 1 To 3)
    
    ' Populate the combined array
     
    'ToDo Add here a 3th Case by the filling
    For i = 1 To UBound(arr1)
        combinedArray(i, 1) = arr1(i, 1)
        combinedArray(i, 2) = 1 ' Indicates the value is from the first list
    Next i
    
    For i = 1 To UBound(arr2)
        combinedArray(i + UBound(arr1), 1) = arr2(i, 1)
        combinedArray(i + UBound(arr1), 2) = 2 ' Indicates the value is from the second list
    Next i
    
    CombineArrays = combinedArray End Function