VBA Array cell value duplication problem

Question

I'm struggling with the same kind of problem for weeks. I thought now is the time to as professionals on the web:

Dim txt, show As String
Dim NameList(1 to 50) as Varriant
Dim i, j, t As Integer
t = 1
For i = 1 To 10
  For j = 1 To 5
    NameList(t) = Sheets("Sheet2").Cells(i, j).Value
    nshow = nshow & i & " " & t & " " & NameList(t) & vbCrLf
    t = t + 1
  Next j     
Next i
MsgBox nshow

This is the code to put all the following data in an array(see picture):

after I run I get the following:

I think I got the following cell value in an array: NameList(a 8 , k 2, u 2,e 2, o 2, etc....)

My question is how can I clean my array so that their is no duplicated (more than 1 same value). I would like to have NameList(a 8 , k 2, u 2, e 2, x 11, c 1, m 1, d 2, r 1, o 2, y 1, i 1, f 1, p 1, z 1, g 2, q 2, h 1, b 2, l 1, v 1, j 1, t 2, n 1) No duplicated data.

I would like to appreciate, help!

Answer 1

Give this a try:

Sub zxcvb()
    On Error Resume Next
    Dim NameList(1 To 50) As Variant, v As Variant
    Dim i As Integer, j As Integer, t As Integer
    Dim nshow As String, c As Collection
    Set c = New Collection
    t = 1
    For i = 1 To 10
      For j = 1 To 5
        v = Sheets("Sheet2").Cells(i, j).Value
        c.Add v, CStr(v)
        If Err.Number = 0 Then
            NameList(t) = Sheets("Sheet2").Cells(i, j).Value
            nshow = nshow & i & " " & t & " " & NameList(t) & vbCrLf
            t = t + 1
        Else
            Err.Number = 0
        End If
      Next j
    Next i
    MsgBox nshow
End Sub

Answer 2

You should add following line of code to your script:

If InStr(nshow, Sheets("Sheet2").Cells(i, j).Value) = 0 Then
End If

Basically InStr function here checks if current iterated value is already in nshow array.

If yes - it does nothing, if no (function returns 0) - it lets the inside block of code being run and as a result of this the new value is being added to the nshow array.

Finally your code should look like this:

Dim txt, show As String
Dim NameList(1 To 50) As Variant
Dim i, j, t As Integer
t = 1
For i = 1 To 10
  For j = 1 To 5
    If InStr(nshow, Sheets("Sheet2").Cells(i, j).Value) = 0 Then
        NameList(t) = Sheets("Sheet2").Cells(i, j).Value
        nshow = nshow & i & " " & t & " " & NameList(t) & vbCrLf
        t = t + 1
    End If
  Next j
Next i
MsgBox nshow