-4

For example if in the celle A1 you have: 1, 2, 3, 4 ,5 ,6 ,7 ,8 ,9 And in A2 you have: 1, 3, 6 ,9 , 15 I would like to find a VBA function F which will do that: F(A1; A2)= 15, what it means that it will give the only character which is not in the chain of data seen in A1 And if we have in B1: 1, 2 ,5 ,8, 15, 20. F will do that: F(A1; B2)=15, 20 (separated by "," or something else), which are the only 2 characters which don't appear in my cell A1.Etc and etc, if you have 2, 3 or 4 characters which don't appear in the data chain A1. Someone could give me such a code please ?

Thanks.

nilmar4
  • 1
  • 3
  • What have you tried? Also your function description is contradictory, shouldn't you include 2,4,5,7, and 8 as an output for your first example? – cronos2546 Mar 18 '15 at 18:37
  • No justly so! I don't have necessarily all the data of the initial chain in my output; that's all the difficulty! Otherwise I won't post my question here.. – nilmar4 Mar 18 '15 at 18:59
  • I have tried to convert my cell into a line in order to sort by cells but I don't manage to obtain the result that i want. My problem is to compare 2 chains of data and to only keep data which don't appear in my initial chain, that's why I don't have to include 2, 4, 5, 7 and 8 as an output in my first example. So, my is my example clear now ? – nilmar4 Mar 18 '15 at 19:04

1 Answers1

0

This function will work for you 

 Function ReturnUnique(cell1 As Range, cell2 As Range) As String
        ReturnUnique = ""
        Dim v1 As Variant, v2 As Variant
        v1 = Split(cell1.Value, ",")
        v2 = Split(cell2.Value, ",")
        Dim i As Long, j As Long
        Dim bool As Boolean
        For i = LBound(v1, 1) To UBound(v1, 1)
            bool = True
            For j = LBound(v2, 1) To UBound(v2, 1)
                If v2(j) = v1(i) Then
                    bool = False
                    Exit For
                End If
            Next j
            If bool Then
                If ReturnUnique = "" Then
                    ReturnUnique = v1(i)
                Else
                    ReturnUnique = ReturnUnique & ", " & v1(i)
                End If
            End If
        Next i

        For i = LBound(v2, 1) To UBound(v2, 1)
            bool = True
            For j = LBound(v1, 1) To UBound(v1, 1)
                If v1(j) = v2(i) Then
                    bool = False
                    Exit For
                End If
            Next j
            If bool Then
                If ReturnUnique = "" Then
                    ReturnUnique = v2(i)
                Else
                    ReturnUnique = ReturnUnique & ", " & v2(i)
                End If
            End If
        Next i
    End Function

EDIT:

Try this function instead

Function ReturnUnique(cell1 As Range, cell2 As Range) As String
    ReturnUnique = ""
    Dim v1 As Variant, v2 As Variant
    v1 = Split(Trim(cell1.Value), ",")
    v2 = Split(Trim(cell2.Value), ",")
    Dim i As Long, j As Long
    Dim bool As Boolean
    For i = LBound(v2, 1) To UBound(v2, 1)
        bool = True
        For j = LBound(v1, 1) To UBound(v1, 1)
            If Trim(v1(j)) = Trim(v2(i)) Then
                bool = False
                Exit For
            End If
        Next j
        If bool Then
            If ReturnUnique = "" Then
                ReturnUnique = v2(i)
            Else
                ReturnUnique = ReturnUnique & ", " & v2(i)
            End If
        End If
    Next i
End Function
Jeanno
  • 2,769
  • 4
  • 23
  • 31
  • Hi, First thanks for posting this function. It is very nice of you. But I don't manage to make it work with my case, can you give an example with which you manage to make it work. I tried with the value I mention in my example but the result is #NOM?. Which type of data do we have to use to make it work ? Thanks. – nilmar4 Mar 20 '15 at 09:50
  • finally, i manage to make it work but it is not what I want, because when I for example use the 2 cells I mention in my example, the result is 2, 4, 5 , 7, 8, 15 wheareas I would like that the function gives as a result 15, am I clear ? – nilmar4 Mar 20 '15 at 10:48
  • It is just PERFECT ! Thank you so much for your help ! I mean really thank you. – nilmar4 Mar 20 '15 at 13:47