Excel Data Reduction

[lurpy101]

Hello, I am new to the more complex side of using Excel, but can I find a specific word or phrase in a cell that contains a sentence? For instance in the following I want to find the word corrosion and return corrosion.

MODERATE CORROSION IN SITE WEATHER COVER

I made a separate table with two columns, The first is the word I want to find and the second is the word I want to be placed in a cell which is the "Catagory". This is a sample of the reduction table:

Change Key	Tumblers
Corrosion	Corrosion
Serial	Serial Number

<tbody>
</tbody>

This is the command that I have used in the past to find an element then return something else:

=VLOOKUP(F2,Sheet1!$A$1:$B$36,2)



But it is searching the first column returning the 3rd column: (Ideally I would like to be able to use the tool to search through both the 1st column and the second column and return the value it finds in the 3rd column.)

SITE IS CORRODED BEYOND TOLERANCE	INW/ SITE C/C IN PROGRESS (PRIMED & 1ST TOPCOAT DONE)	0
ERROR, UNRESTABLE IZ, REQS C/O	REPLACED SYSTEM DO TO BAD PART	Corrosion
SITE HAS EXCESSIVE CORROSION	INW - SITE C/C IN WORK	0

<tbody>
</tbody>

So as you can see it is not functioning the right way. Is there a better way to perform this task. I have been told nested IF statements could work, but the reduction table could potentially be over 100 items.



Basically I am trying to find a way to use a table of values to reduce a large list of problems into a definitive list of issues. The alternative is to look at the data line by line and type the "Category" manually.



I will apologize in advance because I am trying to explain it as best as I can, but it is a hard problem to define. Thanks in advance for any help on this topic. If anything is not clear I can attempt another explanation.

 

[onlyadrafter]

Hello,

I'm sure there is a formula solution to this, but just cannot fathom it out.

So have created a VBA solution.

Sub REDUCE_DATA()
    Application.ScreenUpdating = False
    For MY_ROWS = 2 To Range("A" & Rows.Count).End(xlUp).Row
        For MY_NEXT_ROWS = 1 To Range("F" & Rows.Count).End(xlUp).Row
            If Len(Replace(UCase(Range("F" & MY_NEXT_ROWS).Value), UCase(Range("A" & MY_ROWS).Value), "")) _
                 <> Len(Range("F" & MY_NEXT_ROWS).Value) Then
                Range("H" & MY_NEXT_ROWS).Value = Range("B" & MY_ROWS).Value
            End If
            If Len(Replace(UCase(Range("g" & MY_NEXT_ROWS).Value), UCase(Range("A" & MY_ROWS).Value), "")) _
                 <> Len(Range("G" & MY_NEXT_ROWS).Value) Then
                Range("H" & MY_NEXT_ROWS).Value = Range("B" & MY_ROWS).Value
            End If
        Next MY_NEXT_ROWS
    Next MY_ROWS
    Application.ScreenUpdating = False
End Sub

This works for your data set provided, you will have to test it on the full data.

 

[lurpy101]

Thank you for your fast reply. I am new to VBA so I am trying to learn quickly.

How can I modify this so that it searches through sheet 1 column F and G for the keyword in column A of sheet 2 and returns the value in column B if it is found?

 

[onlyadrafter]

Hello,

does this work as expected?

Dim ws1 As Worksheet
Dim ws2 As Worksheet
Sub REDUCE_DATA()
    Set ws1 = Sheets("Sheet1")
    Set ws2 = Sheets("Sheet2")
    Application.ScreenUpdating = False
    For MY_ROWS = 2 To ws2.Range("A" & Rows.Count).End(xlUp).Row
        For MY_NEXT_ROWS = 1 To ws1.Range("F" & Rows.Count).End(xlUp).Row
            If Len(Replace(UCase(ws1.Range("F" & MY_NEXT_ROWS).Value), UCase(ws2.Range("A" & MY_ROWS).Value), "")) _
                 <> Len(ws1.Range("F" & MY_NEXT_ROWS).Value) Then
                ws1.Range("H" & MY_NEXT_ROWS).Value = ws2.Range("B" & MY_ROWS).Value
            End If
            If Len(Replace(UCase(ws1.Range("G" & MY_NEXT_ROWS).Value), UCase(ws2.Range("A" & MY_ROWS).Value), "")) _
                 <> Len(ws1.Range("G" & MY_NEXT_ROWS).Value) Then
                ws1.Range("H" & MY_NEXT_ROWS).Value = ws2.Range("B" & MY_ROWS).Value
            End If
        Next MY_NEXT_ROWS
    Next MY_ROWS
    Application.ScreenUpdating = False
End Sub