Replace word set in Excel

Question

I have an Excel spreadsheet with some columns, for example:

Fruta | Veículo | Profissões
--------------------------

Banana   Carro   Médico
Pera     Moto    Programador
Maça     Barco   Engenheiro
Morango  Avião   Advogado

And in another column...

Coluna 1
--------
Banana
Pera
Carro
Médico

And I’d like that column to turn into:

Coluna 1
--------
Fruta
Fruta
Veículo
Profissão

How could I perform this automated replacement of various spreadsheet data?

Obs.: No matter the tool or programming language to accomplish such a feat

Answer 1

You can define a function that does this search and return the column title, which is the name of each group. Below is a functional example, based on the question data:

Defining the VBA Function

The function below will receive the searched value and a range of columns, from this it loops in each column, searching for the searched value. When locating (or not), it closes and returns the result. You have comments for more understanding:

Public Function ProcMultiplasColunas(ByVal ValorProcurado As String, ByVal RangeColunas As Range) As String

Dim ColunaLoop As Long
Dim ColunaLocalizada As String
Dim Corresp As Variant

ColunaLocalizada = ""

For ColunaLoop = 1 To RangeColunas.Columns.Count Step 1

    'Usa função nativa de corresp(match)
    Corresp = 0
    On Error Resume Next
    Corresp = Application.WorksheetFunction.Match(ValorProcurado, RangeColunas.Columns(ColunaLoop), 0)
    On Error GoTo 0
    
    'Verifica se localizou, seta a coluna e encerra For
    If Corresp <> 0 Then
        ColunaLocalizada = RangeColunas.Cells(1, ColunaLoop)
        Exit For
    End If

Next ColunaLoop

'Testa se localizou algum valor e encerra a função
If ColunaLocalizada = "" Then
    ProcMultiplasColunas = "#NÃO LOCALIZADO!"
Else
    ProcMultiplasColunas = ColunaLocalizada 'retorna a coluna localizada para a função
End If

End Function

This procedure returns the expected result:

Sub to replace contents

We need the process to run line by line, this was defined from a For, which applies the Function defined earlier. It overwrites the contents of the cell under analysis. It is with comments for more understanding:

Sub substituiValores()

Dim linha As Integer 'declara a linha como inteiro

For linha = 2 To 5 'verificará da linha 2 até a 5 - precisa ajustar ou aprimorar
    If Cells(linha, 1) <> "" Then 'verifica se tem conteúdo na célula e executa a fórmula
        Cells(linha, 1) = ProcMultiplasColunas(Cells(linha, 1), ThisWorkbook.Sheets("Planilha2").Range("A:C"))
    End If 'fecha if
Next linha 'próxima linha, se não foi encerrado no if verdadeiro

End Sub

Points of attention

1. May have performance problems, depending on the machine and size of the bases;
2. When running the sub, the original values are lost, without recovering or "undo (Ctrl z)". Note that it is set to leave a "#NOT LOCATED!" however, it is impossible to know what the original content was not localized (since it overwritten).
3. This is not the only way. There are other ways around this need, such as reorganizing the group base (title and list). If it is reorganized (if possible), a simple procv function would meet, for example. Another possibility may exist with matrix definitions (formulas "Ctrl-Shift-Enter" or "CSE"), only as an example too.