Removing Duplicates from 2d array and updating it in VBA

Question

I have a 2d array holding values like the following:

array - JK(K, NC)

"K" Stores Total No of Items "NC" Stores Items

I need to remove the duplicates From "NC" and also update "K" (i.e the total items) after removing the duplicates.

4 - 5, 6, 7, 5
6 - 7, 6, 9, 10, 11, 7
4 - 8, 7, 15, 8
9 - 12, 15, 16, 12, 17, 18, 19, 20, 16
3 - 26, 27, 26
3 - 20, 19, 20
6 - 21, 33, 33, 34, 35, 21
8 - 19, 33, 34, 18, 38, 39, 40, 34
5 - 39, 40, 38, 43, 40
6 - 41, 44, 44, 45, 46, 41
3 - 20, 19, 20
6 - 21, 33, 33, 34, 35, 21
8 - 19, 33, 34, 18, 38, 39, 40, 34

What is the code you have already tried? Is the order of values in the string of importance? — Luuklag, Sep 01 '17 at 07:20
Possible duplicate of [VBA, remove duplicates from array](https://stackoverflow.com/questions/11870095/vba-remove-duplicates-from-array) — Moosli, Sep 01 '17 at 07:27
@Moosli This question is different, as it doesn't want to remove duplicate array entries, it wants to remove duplicates from strings, which are in an array. — Luuklag, Sep 01 '17 at 07:31
@Luuklag For me not really evident from the question, it could also be a 2 Dimensional Array be with different lengths of the two second dimension — Moosli, Sep 01 '17 at 07:37

Moosli · Answer 1 · 2017-09-03T19:09:30.670

Here is a Solution based on the Entries and Code from @tigeravatar and @Jeeped with you could have find here on Stack overflow, so a big thanks to this guys.

Removing Duplicate values from a string in Visual Basic

and

Multidimensional Arrays with For Loops VBA

Sub Test()
        Dim strArray(8, 1) As String
        Dim newString As String
        strArray(0, 0) = "4"
        strArray(0, 1) = "5 6 7 5"
        strArray(1, 0) = "6"
        strArray(1, 1) = "7 6 9 10 11 7"
        strArray(2, 0) = "4"
        strArray(2, 1) = "8 7 15 8"
        strArray(3, 0) = "9"
        strArray(3, 1) = "12 15 16 12 17 18 19 20 16"
        strArray(4, 0) = "4"
        strArray(4, 1) = "5 6 7 5"
        strArray(5, 0) = "6"
        strArray(5, 1) = "7 6 9 10 11 7"
        strArray(6, 0) = "9"
        strArray(6, 1) = "12 15 16 12 17 18 19 20 16"

        For i = 0 To UBound(strArray, 1)
                newString = DeDupeString(strArray(i, 1), " ")
                strArray(i, 0) = UBound(Split(newString, " ")) + 1
                strArray(i, 1) = newString
        Next i
    End Sub

    Function DeDupeString(ByVal sInput As String, Optional ByVal sDelimiter As String = ",") As String
        Dim varSection As Variant
        Dim sTemp As String
        varSection = Split(sInput, sDelimiter)
        For Each varSection In Split(sInput, sDelimiter)
            If InStr(1, sDelimiter & sTemp & sDelimiter, sDelimiter & varSection & sDelimiter, vbTextCompare) = 0 Then
                sTemp = sTemp & sDelimiter & varSection
            End If
        Next varSection
        DeDupeString = Mid(sTemp, Len(sDelimiter) + 1)
    End Function

Nice solution. If only people themselves learned to google ;) — Luuklag, Sep 01 '17 at 08:30

Nathan_Sav · Accepted Answer · 2017-09-01T08:00:49.723

You could use a function, something like this

Function RemoveDupes(strInput As String) As Variant()

'   Uses Microsoft Scripting Runtime referece

Dim arrSplit() As String
Dim lngCounter As Long
Dim dicDupeCheck As New Scripting.dictionary

arrSplit = Split(strInput, Chr(32))

For lngCounter = 0 To UBound(arrSplit) - 1

    If Not dicDupeCheck.Exists(arrSplit(lngCounter)) Then
        dicDupeCheck.Add arrSplit(lngCounter), arrSplit(lngCounter)
    End If

Next lngCounter

RemoveDupes = Array(dicDupeCheck.Count, Join(dicDupeCheck.Items(), " "))

Erase arrSplit

End Function

This will then be used as follows

RemoveDupes("12 15 16 12 17 18 19 20 16")(0) will give the count, and RemoveDupes("12 15 16 12 17 18 19 20 16")(1) will give the non-dupe output.

Or set an array to removedupes and use that, so arr=RemoveDupes("12 15 16 12 17 18 19 20 16") then OriginalArray(x)=arr(0) & " - " & arr(1)

Removing Duplicates from 2d array and updating it in VBA

2 Answers2