concatenate corresponding values of two cells with comma separated multiple values in it

Question

In excel sheet I have cells with mulitple comma separated values. I want to concatenate the corresponding values of two cells. Example:

Cell_A1 (A,B,C,D) and Cell_B1 (E,F,G,H)  
Then in Cell_C1 it should be (AE,BF,CG,DH). 

Answer 1

Nice question. Please find the image below for your answer.
Note-1: Add a comma as the last character to your cells which you want to split - otherwise you will get #value error in the last row.

Now you have to do the same thing with your other cell and obtain a similar result.

As a last part use the concatenate function to join pairs from the two columns and get your end result.
Concatenate would look something like this (assume cell A2 contains the comma character)
=concatenate(C3,$A$2,E3) Also assumed that E3 contains the result which has to be joined with C3.
If you have any questions feel free to comment.

Answer 2

This does it

Option Explicit

Sub DoTheThing()

Dim row As Integer
row = 1 ' WHAT IS THE STARTING ROW

'Let's clear the answers

Range("D:F").Cells.Clear

Do While (Range("A" & row).Value <> "")

    Dim lookUpValue As String
    lookUpValue = Range("A" & row).Value

    Dim vals() As String
    vals = Split(Range("B" & row).Value, ",")

    Dim result As String
    result = ""

    Dim i As Integer

    For i = 0 To UBound(vals)

        If CSng(lookUpValue) >= CSng(vals(i)) Then
            result = result & "Yes, "
        Else
            result = result & "No, "
        End If

    Next i

    result = Trim(result)
    result = Left(result, Len(result) - 1)

    Range("D" & row).Value = result

    result = "" ' reset it
     Dim valD() As String
    valD = Split(Range("C" & row).Value, ",")

     For i = 0 To UBound(valD)

        If CSng(lookUpValue) <= CSng(valD(i)) Then
            result = result & "Yes, "
        Else
            result = result & "No, "
        End If

    Next i

    result = Trim(result)
    result = Left(result, Len(result) - 1)

    Range("E" & row).Value = result


'finally

    Dim splitD() As String
    splitD = Split(Range("D" & row).Value, ",")


    Dim splitE() As String
    splitE = Split(Range("E" & row).Value, ",")

    result = ""

    Dim length As Integer
    length = UBound(splitD)
    For i = 0 To length 'both should have the same values according to @Raw
        If (i = length) Then
            Range("F" & row).Value = Range("F" & row).Value & splitD(i) & splitE(i)
        Else
            Range("F" & row).Value = Range("F" & row).Value & splitD(i) & splitE(i) & ","
        End If
    Next i

    row = row + 1

Loop

End Sub

Before

After