In Excel, how do I ensure that the words in one column (one per cell) are found in the database column?

Question

In Excel, I have a column of words and another column with a "database" of all "allowed" words, both columns have one word per cell. I want to remove any non-allowed words from the first column. Column 1 will have up to 200 words and Column 2 will have 4000 words. Ideally I would like a bit of code which will do this for me so that I am left with a column with just the allowed words from column 1.

Answer 1

Below is a VB procedure which will do what you want. Copy and paste it into a macro module for the workbook.

You also have to do the following:

1. Name the range of allowed words, e.g. B1 through B4000, "Allowed"
2. Name the range of words to test, e..g. A1 through A200, "InputList"
3. Name a range for where the allowed words from the InputList will be output by the macro, e.g. C1 through C200, "OutputList"

If you just want the output of allowed words to overwrite the InputList, then you don't have to do 3. above, and instead in the 3rd line from the end of the macro, change "OutputList" to "InputList"


Sub EliminateUnallowed()

Dim cell As Range
Dim c As Range
Dim HitArray(1 To 200) As String
Dim i As Integer

i = 1

Range("InputList").Select
For Each cell In Selection
With Range("Allowed")
Set c = .Find(cell.Value, LookIn:=xlValues, _
LookAt:=xlWhole)
If Not c Is Nothing Then
HitArray(i) = cell.Value
i = i + 1
End If
End With
Next cell

Range("OutputList").Value = _
Application.WorksheetFunction. _
Transpose(HitArray)

End Sub

Answer 2

VLookup.
Column A has the words you want to check.
Column C has the database. For the formula below, we are assuming 4000 rows
Column D will be a column of identical cells. Put ALLOWED in each cell. (you can do this in the top cell and then copy down as far as you need.)

Column b has the following formula

=VLookup(A1,C1:d4000,2)

Copy the formula all the way down to match thenumber of entries in Column A. You will see that Column B now has either "#N/A" or the word "ALLOWED". Sort your Data using Column B and all the #N/A will be together at the bottom of the list. You can then delete them by highlighting the CELLS of in Column A which correspond with #N/A and delete them.

Answer 3

lets say column A contains your list and its a list of hues and also you want to count number the range of cells containing the be conscious "blue" in B1 placed =COUNTIF(A:A, "blue") note: that's case comfortable. BLUE isn't an same as blue this may verify all cells in column A and could placed a volume in cellular B1 representing the range of situations the be conscious "blue" looks on your list.

Answer 4

Compare the ranges using VLOOKUP.

Answer 5

You can use advanced filter trick