Find/Delete value from unique array
Results 1 to 6 of 6

Thread: Find/Delete value from unique array
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Aug 2016
    Posts
    53
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Find/Delete value from unique array

    My Idea: Hey Guys, so I have a Row of data, with a bunch of Sample ID's in them. I add all those values from a row into an array, then only keep the unique ones. Now I want VBA to search the array for the values in the array, if there's a match, then deletes the value from the array and also does other stuff. Since it deletes the value from the Array then the second time that value occurs, the value will not be found in the unique array.

    Example Data:
    A C A
    First Occurance, delete "A" from unique array, and also do stuff First Occurance, delete "A" from unique array, and also do stuff Value shouldn't be found in unique array, so not first occurance, do nothing.

    My (mostly from forums) code:
    Code:
    Dim ArrayOfSampleIDs() As Variant
    Dim UniqueArrayOfSampleIDs() As Variant
    Dim i As Long
    
    ReDim ArrayOfSampleIDs(3 To LastCol)
    
    For i = 3 To LastCol
        ArrayOfSampleIDs(i) = Cells(11, i).value ' Array Of ALL sample ID's
    Next
    
    UniqueArrayOfSampleIDs = RemoveDupes(ArrayOfSampleIDs) ' Array Of UNIQUE sample ID's
     
    For i = 3 To LastCol
          IsInArray = (UBound(Filter(UniqueArrayOfSampleIDs, Cells(11, i).value)) > -1)
          If IsInArray Then
    
          'Delete This Value From Array(UniqueArrayOfSampleIDs, Cells(11, i).value)       <------- Need Help Here (this is obviously not a real function)
    
          'Do Otherstuff (this is ready to go)
          
          End If
          
    Next i
    
    Function RemoveDupes(InputArray As Variant) As Variant
      Dim X As Long
      With CreateObject("Scripting.Dictionary")
        For X = LBound(InputArray) To UBound(InputArray)
          If Not IsMissing(InputArray(X)) Then .Item(InputArray(X)) = 1
        Next
        RemoveDupes = .Keys
      End With
    End Function

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,768
    Post Thanks / Like
    Mentioned
    65 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Find/Delete value from unique array

    If you are interested, with the following you can store the unique values ​​in an array.

    Code:
    Sub Test()
        Dim i As Long, ky As Variant
        With CreateObject("scripting.dictionary")
          For i = 3 To Cells(11, Columns.Count).End(xlToLeft).Column
            .Item(Cells(11, i).Value) = Empty
          Next
          For Each ky In .Keys
            MsgBox "Unique value: " & ky
          Next ky
        End With
    End Sub
    Regards Dante Amor

  3. #3
    Board Regular
    Join Date
    Aug 2016
    Posts
    53
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find/Delete value from unique array

    Quote Originally Posted by DanteAmor View Post
    If you are interested, with the following you can store the unique values ​​in an array.

    Code:
    Sub Test()
        Dim i As Long, ky As Variant
        With CreateObject("scripting.dictionary")
          For i = 3 To Cells(11, Columns.Count).End(xlToLeft).Column
            .Item(Cells(11, i).Value) = Empty
          Next
          For Each ky In .Keys
            MsgBox "Unique value: " & ky
          Next ky
        End With
    End Sub
    Dante, that part is already working(though your code is more concise). The part I'm stuck in is searching the array values and deleting the value I want to delete from the array... aka If i have A, B, C, D in an array, and I want to delete D in the array. I can't figure out how to do that.

  4. #4
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,768
    Post Thanks / Like
    Mentioned
    65 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Find/Delete value from unique array

    I don't understand, why do you want to delete a duplicate. The most practical: Do not load duplicates in the array.


    I give you an example to delete a data in the array.


     ABCDEFG
    1       
    9       
    10       
    11  data1data2data3data4 
    12       




    Code:
    Sub test2()
      Dim dict As New Scripting.Dictionary
      Dim i As Long, ky
      'load dict
      For i = 3 To Cells(11, Columns.Count).End(xlToLeft).Column
        dict.Add Key:=Cells(11, i).Text, Item:=Cells(11, i).Text 'the key and item is the same
      Next
      
      'for example delete data3
      dict.Remove "data3"   'make reference to the key "data3"
      For Each ky In dict.Keys
        MsgBox "key : " & ky
      Next
    End Sub
    
    
    Sub test3()
      Dim dict As New Scripting.Dictionary
      Dim i As Long, it
      'load dict
      For i = 3 To Cells(11, Columns.Count).End(xlToLeft).Column
        dict.Add Key:=i, Item:=Cells(11, i).Text 'the key is the column number and the item is the cell value
      Next
      
      'for example delete data3
      dict.Remove 5   'make reference to the key column 5
      For Each it In dict.Items
        MsgBox "item : " & it
      Next
    End Sub
    Note: How to Add a Reference to the Microsoft Scripting Runtime
    Go to "Tools" and click "References." This opens the References dialog box.
    Scroll down the list and put a check next to "Microsoft Scripting Runtime."
    Regards Dante Amor

  5. #5
    Board Regular
    Join Date
    Aug 2016
    Posts
    53
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find/Delete value from unique array

    Hey Dante, you are misunderstanding me.. I have an array with all the unique values, A, B, C, D, E.. Then I want to SEARCH that array with a string, "X". If it's found, I want to delete it. I just realized that it was far easier to set the value of the element in the array to "" instead of deleting it.

    Just in case someone else is in the same boat as me, I fixed it by doing this:
    Code:
    Dim ArrayOfSampleIDs() As Variant
    Dim UniqueArrayOfSampleIDs() As Variant
    Dim i As Long
    
    ReDim ArrayOfSampleIDs(3 To LastCol)
    
    For i = 3 To LastCol
        ArrayOfSampleIDs(i) = Cells(11, i).value ' Array Of ALL sample ID's
    Next
    
    UniqueArrayOfSampleIDs = RemoveDupes(ArrayOfSampleIDs) ' Array Of UNIQUE sample ID's
    
     
    For i = 3 To LastCol
          IsInArray = (UBound(Filter(UniqueArrayOfSampleIDs, Cells(11, i).value)) > -1)
          
          If IsInArray Then
                      Dim Location As Variant
                      Location = WhereInArray(UniqueArrayOfSampleIDs, Cells(11, i).value) 
                      If IsNull(Location) = True Then ' Testing to see if the search match is exact.  
                      'Do Something
                      Else
                      UniqueArrayOfSampleIDs(Location) = ""    'Delete the Value from array
                        End If
                        
          End If
    Next i
    Last edited by IREALLYambatman; Aug 16th, 2019 at 10:40 PM.

  6. #6
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,768
    Post Thanks / Like
    Mentioned
    65 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Find/Delete value from unique array

    Sorry for that, I don't understand what you needed, I got confused because your function is called "RemoveDupes".
    I'm glad to hear you solved it. Good luck
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •