-1
123,123,123
456,789,456,258,652

I have 3 values in a single cell and have 5 values in the next cell delimited by a comma. I want to compare all the values in the A1, if they all are unique the result should be True. if not unique the result should be false.

Expected Output:

123,123,123 True
456,789,456,258,652 False

How to do this in excel. Kindly provide me an idea. Thanks in advance

Aravind
  • 61
  • 5
  • 1
    Check [Split text into different columns with the Convert Text to Columns Wizard](https://support.microsoft.com/en-us/office/split-text-into-different-columns-with-the-convert-text-to-columns-wizard-30b14928-5550-41f5-97ca-7a3e9c363ed7) so you can split those numbers into different cells and get advantage of using some common formulas. – Foxfire And Burns And Burns Jul 22 '21 at 09:45
  • What I see is if the values in a cell are same then it's true othewise it's false. Is that correct? – Bharat Jul 22 '21 at 09:46
  • You can create a VBA function that does exactly that. Unless there is a limited number of comma delimited numbers, I do not believe you can do that using a formula. – Tarik Jul 22 '21 at 09:49
  • 2
    If A1 is text, you could use a formula `=LEN(SUBSTITUTE(SUBSTITUTE(A1,LEFT(A1,FIND(",",A1)-1),""),",",""))=0` – Nathan_Sav Jul 22 '21 at 09:51
  • It's fairly simple through formulae if you know how to use `FILTERXML()` to split the string into an array and analyse it through an xpath expression. See [this](https://stackoverflow.com/q/61837696/9758194) – JvdV Jul 22 '21 at 09:54
  • @Bharat ..yes..But how to do that..splitting is not possible as I have a different count of values in each cell – Aravind Jul 22 '21 at 09:55
  • @ Nathan_Sav it worked.Thank you so much – Aravind Jul 22 '21 at 10:01
  • @Nathan_Sav, `=SUBSTITUTE(A1&",",LEFT(A1&",",FIND(",",A1)),"")=""` will do too I guess. – JvdV Jul 22 '21 at 10:12
  • @JvdV will that need a final comma though? – Nathan_Sav Jul 22 '21 at 10:36
  • I'd say so in case it's a numeric value on it's own without a 2nd number which would be comma-seperated @Nathan_Sav – JvdV Jul 22 '21 at 12:00

2 Answers2

1

Here is one simple implementation with VBA, however with formula also it could be possible

Sub CheckIfSame()
    Dim counter As Integer
    'Dim arrSplitStrings1() As Variant
    counter = 2
    Do While True
        If Cells(counter, 1) <> "" Then
            Cells(counter, 2) = ElementsSame(Split(Cells(counter, 1), ","))
        Else
            Exit Do
        End If
       counter = counter + 1
    Loop

End Sub

Function ElementsSame(arr As Variant) As Boolean
    Dim l As Long
    ElementsSame = True
    For l = 1 To UBound(arr)
        If arr(0) <> arr(l) Then
            ElementsSame = False
            Exit For
        End If
    Next l
End Function

enter image description here

Bharat
  • 1,192
  • 7
  • 14
0

Solution based on VAR.S() function and Evaluate(). If all the numbers are equal then VAR.S()=0

Function IsEqual(txt As String)
    IsEqual = Evaluate("VAR.S(" & txt & ")") = 0
End Function
Алексей Р
  • 7,507
  • 2
  • 7
  • 18