delete repeated words in 2 different cells

Question

delete repeated words in 2 different cells

Navigation

#1 by (0 votes)

0

I'm stuck making a sheet in which I want to delete a series of words that I have in 2 different cells.

En la celda A1 tendría GLUTEN, SOJA,
En la celda A2 tendría SOJA, GLUTEN, SESAMO, PESCADO,

I would like to get in a 3rd cell, A3: SÉSAMO, PESCADO, only. That is, eliminate the words, delimited by commas and spaces, that are repeated in both cells (in the example "SOJA", "GLUTEN") even if they are disordered within the text string.

The formula I have tried to use is:

= TRIM(SUBSTITUTE(A2;A1;""))

But I get SOJA , GLUTEN , SESAMO , PESCADO . Since I do not have the words "GLUTEN" and "SOYA" in the same order, when making the formula, it leaves all the words, without eliminating any of the ones that are repeated.

Could someone suggest some formula or effective macro to get the words out, even if they are disordered within a text string?

Thank you very much

vba macros excel

asked by Ruy 26.06.2018 в 13:05

source

1 answer

What should I do to display the URL of an ODK AGGREGATE project created in Google Cloud Platform-App Engine? DataTables replace point with comma

score 0 · Accepted Answer

You can create this function in a module and then call it in A3

=EliminarDuplicados(A1:A2;",")

The function is as follows (each part is commented):

Function EliminarDuplicados(celdas As Range, Optional delim As String = ",") As String
    Dim x
    Dim txt As String
    txt = ""

    'Cadena que contiene el texto de todas las celdas
    For Each c In celdas.Cells
        txt = txt + c.Value + delim + " "
    Next

    'Objeto tipo matriz con elementos clave - valor
    With CreateObject("Scripting.Dictionary")
        .CompareMode = vbTextCompare

        'Se obtiene cada palabra dividiendo la cadena txt por el delimitador pasado como parámetro
        For Each x In Split(txt, delim)
            If Trim(x) <> "" Then
                'Si la palabra no existe se añade a la matriz
                If Not .Exists(Trim(x)) Then
                    .Add Trim(x), Nothing
                'Si ya exite se elimina esa palabra de la matriz
                Else
                  .Remove Trim(x)
                End If
            End If
        Next
        If .Count > 0 Then EliminarDuplicados = Join(.keys, delim + " ")
    End With
End Function

I show you the example in image:

I hope it serves you.