VBA - VLookup and Replace InStr no2

Hi again, this is to follow the first topic: https://www.experts-exchange.com/questions/29081968/VBA-VLookup-and-Replace-InStr.html

In Sheet1 column C, i will have multiple values inside the cells. They are called Options.
In Sheet DBASE, i will have the same distinct single Options values in column M and in column N i will have the Option description.

My goal is to replace in Sheet1 column C all the Options by the Option Descriptions from the DBASE sheet.

The problem now is that in column C, I can have duplicated values. I want to remove the duplicate values. I just want one of each option descriptions.

Example: TEST1, TEST4, TEST1, TEST6 would become: CAR, TRUCK, TRAILER.

This is the code i use right now:
Dim wsDbase As Worksheet, wsQuestions As Worksheet
    Dim arQuestions() As Variant, arAnswers() As Variant
    Dim arSearch() As Variant
    Dim arOptions() As String
    
    Dim rwQuestions As Long
    Dim rwSearch As Long, i As Integer
    Dim Found As Integer
    Dim rwEnd As Long
        
    Application.ScreenUpdating = False
    
    Set wsDbase = Sheets("DBASE")
    Set wsQuestions = Sheets("Sheet1")
        
    arQuestions = wsQuestions.Range("B1:B" & wsQuestions.Range("B1048576").End(xlUp).Row)
    wsQuestions.Range("C2:C" & UBound(arQuestions, 1)).ClearContents
    arAnswers = wsQuestions.Range("C1:C" & UBound(arQuestions, 1))
    arSearch = wsDbase.Range("M1:N" & wsDbase.Range("M1048576").End(xlUp).Row)
        
    For rwQuestions = 2 To UBound(arQuestions, 1)
        If arQuestions(rwQuestions, 1) <> "" Then
            arOptions = Split(Replace(arQuestions(rwQuestions, 1), " ", ""), ",")
            For i = LBound(arOptions, 1) To UBound(arOptions, 1)
                For rwSearch = 2 To UBound(arSearch, 1)
                    If arSearch(rwSearch, 1) = arOptions(i) Then
                        If arAnswers(rwQuestions, 1) = "" Then
                            arAnswers(rwQuestions, 1) = arSearch(rwSearch, 2)
                        Else
                            arAnswers(rwQuestions, 1) = arAnswers(rwQuestions, 1) & ", " & arSearch(rwSearch, 2)
                        End If
                        Exit For
                    End If
                Next rwSearch
            Next i
        End If
    Next rwQuestions
    wsQuestions.Range("C1:C" & UBound(arQuestions, 1)) = arAnswers
End Sub

Open in new window


How can i do this?

The sample is on the accepted answer from previous post.

Thanks for your help
LVL 11
Wilder1626Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ejgil HedegaardCommented:
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Wilder1626Author Commented:
Thanks again. This is exactly what i needed.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.