Array insertion of Duplicated and not duplicated data to different column in VBA

Question

Good day! in my worksheet i have (1) textbox as TextBox1 and 1 button for submit button. I have here sample code that gives splitted text as an output. I just want that if there's duplicated word in textbox1 and the user enters the submit button it will saves to worksheet(DatabaseStorage) and categorize the output from No Duplicated Word and With duplicated Word. Because this two different fields will be needed for some function of the system.

Private Sub CommandButton1_Click()
Call SplitText
End Sub
Sub SplitText()
Dim WArray As Variant
Dim TextString As String
TextString = TextBox1
WArray = Split(TextBox1, " ")
If (TextString = "") Then
MsgBox ("Error: Pls Enter your data")
Else


With Sheets("DatabaseStorage")
    .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Resize(UBound(WArray) + IIf(LBound(WArray) = 0, 1, 0)) = Application.Transpose(WArray)
End With

MsgBox ("Successfully inserted")

End If

End Sub

Can you give a bit more info on how data is entered into the text box? Is there one word per line in the box? — kschindl
– kschindl, Commented Sep 5, 2017 at 17:30
Hi sir i already updated the post check the new image, you answer is what i need but there is a unexpected output in column "No duplicate word" i want to the word financial and delete the other word that duplicates the word financial. like the example above sir — Pablo
– Pablo, Commented Sep 5, 2017 at 23:10

kschindl · Accepted Answer · 2017-09-06 13:39:15Z

1

This should accomplish what you need. I loop through the array to check if the given value exists in the "No Duplicates" column. If not, don't print it there.

Any time I encounter a situation where I need to check a single value against a list (ex. check for duplicates, GT/LT, etc.), I consider looping.

Sub SplitText()
Dim WArray As Variant
Dim TextString As String
Dim col_no_dup As Long
Dim col_dup As Long
Dim counter As Integer
Dim sht_database As Worksheet

With ThisWorkbook
    Set sht_database = .Sheets("DatabaseStorage")
    TextString = LCase(.Sheets("Sheet1").Shapes("Textbox1").DrawingObject.Text)
End With

WArray = Split(TextString, " ") 'load array

If (TextString = "") Then
    MsgBox ("Error: Pls Enter your data")
    End
Else: End If

'set column locations for duplicates/no duplicates
col_no_dup = 1
col_dup = 2

With sht_database
    .Range("A2:B10000").ClearContents 'clear existing data. Change this as needed

    'Print whole array into duplicates column
    .Cells(Cells.Rows.Count, col_dup).End(xlUp).Offset(1, 0).Resize(UBound(WArray) + IIf(LBound(WArray) = 0, 1, 0)) = Application.Transpose(WArray)

    'Loop through array
    For i = LBound(WArray) To UBound(WArray)
        counter = 0
        lrow_no_dup = .Cells(Cells.Rows.Count, col_no_dup).End(xlUp).Row
        For n = 1 To lrow_no_dup 'loop through and check each existing value in the no dup column
            If .Cells(n, col_no_dup).Value = WArray(i) Then
                counter = counter + 1 'account for each occurence
            Else: End If
        Next n
        If counter = 0 Then 'counter = 0 implies the value doesn't exist in the "No Duplicates" column
            .Cells(lrow_no_dup + 1, col_no_dup).Value = WArray(i)
        Else: End If
    Next i

End With

MsgBox ("Successfully inserted")

End Sub

edited Sep 6, 2017 at 13:39

answered Sep 5, 2017 at 18:10

kschindl

2332 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Pablo Over a year ago

Hi mr kschindl it works like in the picture but the none duplicated output doesnt save the financial. I want to keep in the column of with no duplicated the financial example if i input this "financial My problem is financial" then in this sentence the the only that will be save to the database is "my problem is financial" or "financial my problem is" something like this

kschindl Over a year ago

My apologies, my first answer wasn't entirely correct then. I've updated the code, so it should be doing what you need now. I tested it with your example.

Pablo Over a year ago

Thankyou sir for helping me your help means a lot because this system that we are doing is for our university thankyou a lot

Pablo Over a year ago

Sir wait, one thing how can i validate if i enter something like this "Financial financial is my real problem" because i tried to enter this sentence and it saves to the column of no duplicate. i think this is because the upper case and lower case of the Financial and financial hmmm

kschindl Over a year ago

Code is updated to fix that issue. If when initializing the TextString for the text box contents you enclose it in LCase(), it will make everything lower case. Similarly, you could use UCase() to make it uppercase.

|

Collectives™ on Stack Overflow

Array insertion of Duplicated and not duplicated data to different column in VBA

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related