How to clean a column based on a list of blacklisted terms (without Macros) ?

[ben74]

Hi everyone,

Long time lurker, I've already learned a lot but I'm stuck here...


I need to clean a column based on a list of blacklisted terms stored in another column. Ideally the solution should be flexible enough to extend the list of blacklisted terms (col B) or the list of phrases to analyse (col A).
Also need to do this without running a MACRO.


Can someone kindly help me?

Example of the wanted behavior:

[TABLE="width: 482"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]ORIGINAL-LIST[/TD]
[TD]BLACKLIST[/TD]
[TD]CLEANED-LIST[/TD]
[/TR]
[TR]
[TD]plural like greens should remain[/TD]
[TD]green[/TD]
[TD]plural like greens should remain[/TD]
[/TR]
[TR]
[TD]overgreen shouldn't be deleted[/TD]
[TD]red[/TD]
[TD]overgreen shouldn't be deleted[/TD]
[/TR]
[TR]
[TD]some greener stuff should stay[/TD]
[TD][/TD]
[TD]some greener stuff should stay[/TD]
[/TR]
[TR]
[TD]some string green stuff[/TD]
[TD][/TD]
[TD]plural like reds should remain[/TD]
[/TR]
[TR]
[TD]Green should get blacklisted too[/TD]
[TD][/TD]
[TD]overred shouldn't be deleted[/TD]
[/TR]
[TR]
[TD]And of course GREEN uppercase should be deleted[/TD]
[TD][/TD]
[TD]some reder stuff should stay[/TD]
[/TR]
[TR]
[TD]plural like reds should remain[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]overred shouldn't be deleted[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]some reder stuff should stay[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]some string red stuff[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Red should get blacklisted too[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]And of course RED uppercase should be deleted[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[michaelsmith559]

So far this is what I have, see if this is what you want:

Excel Workbook
	A	B	C
1	ORIGINAL-LIST	BLACKLIST	CLEANED-LIST
2	plural like greens should remain	green	plural like greens should remain
3	overgreen shouldn't be deleted	red	overgreen shouldn't be deleted
4	some greener stuff should stay	*	some greener stuff should stay
5	some string green stuff	*	some string green stuff
6	Green should get blacklisted too	*	*
7	And of course GREEN uppercase should be deleted	*	*
8	plural like reds should remain	*	plural like reds should remain
9	overred shouldn't be deleted	*	overred shouldn't be deleted
10	some reder stuff should stay	*	some reder stuff should stay
11	some string red stuff	*	some string red stuff
12	Red should get blacklisted too	*	*
13	And of course RED uppercase should be deleted	*	*
Sheet1

 

[ben74]

Thanks, any idea why it doesn't eliminate :


some string green stuff


and :


some string red stuff


from the clean list as it should ?


That's kinda weird...

I'm not sure using a FIND can do it, as a FIND will find any words/phrase that contain the STRING instead of the word (the goal is to blacklist words, not a chain of chars...)

 

[michaelsmith559]

I cannot find a formula solution. Others on here may know of one. Is there a particular reason why you can't use a vba solution?

 

[michaelsmith559]

If you can use a udf here is what I used courtesy of Rick Rothstein:

Excel Workbook
	A	B	C
1	ORIGINAL-LIST	BLACKLIST	CLEANED-LIST
2	plural like greens should remain	green	*
3	overgreen shouldn't be deleted	red	*
4	some greener stuff should stay	*	*
5	some string green stuff	*	*
6	Green should get blacklisted too	*	*
7	And of course GREEN uppercase should be deleted	*	*
8	plural like reds should remain	*	*
9	overred shouldn't be deleted	*	*
10	some reder stuff should stay	*	*
11	some string red stuff	*	*
12	Red should get blacklisted too	*	*
13	And of course RED uppercase should be deleted	*	*
Sheet1

Results:

Excel Workbook
	A	B	C
1	ORIGINAL-LIST	BLACKLIST	CLEANED-LIST
2	plural like greens should remain	green	plural like greens should remain
3	overgreen shouldn't be deleted	red	overgreen shouldn't be deleted
4	some greener stuff should stay	*	some greener stuff should stay
5	some string green stuff	*	*
6	Green should get blacklisted too	*	*
7	And of course GREEN uppercase should be deleted	*	*
8	plural like reds should remain	*	plural like reds should remain
9	overred shouldn't be deleted	*	overred shouldn't be deleted
10	some reder stuff should stay	*	some reder stuff should stay
11	some string red stuff	*	*
12	Red should get blacklisted too	*	*
13	And of course RED uppercase should be deleted	*	*
Sheet1

UDF:

Function ExactWordInString(Text As String, Word As String) As Boolean
     ExactWordInString = " " & UCase(Text) & " " Like "*[!A-Z]" & UCase(Word) & "[!A-Z]*"
End Function

 

[ben74]

Thanks Michael !!

It kinda does what I need but it's not flexible at all (the list of forbidden/blacklisted words can change a lot).

Anyone has an idea to extend this formula to be able to handle a variable list of forbidden words ?

Thanks a lot !

Cheers,

 

[michaelsmith559]

I am not sure if you changed your mind about a macro, but if so, here is how I did it:

Sheet 1 Setup

Excel Workbook
	A	B	C	D
1	ORIGINAL-LIST	BLACKLIST	CLEANED-LIST	In List
2	plural like greens should remain	green	*	*
3	overgreen shouldn't be deleted	red	*	*
4	some greener stuff should stay	*	*	*
5	some string green stuff	*	*	*
6	Green should get blacklisted too	*	*	*
7	And of course GREEN uppercase should be deleted	*	*	*
8	plural like reds should remain	*	*	*
9	overred shouldn't be deleted	*	*	*
10	some reder stuff should stay	*	*	*
11	some string red stuff	*	*	*
12	Red should get blacklisted too	*	*	*
13	And of course RED uppercase should be deleted	*	*	*
Sheet1

After macro:

Excel Workbook
	A	B	C
1	ORIGINAL-LIST	BLACKLIST	CLEANED-LIST
2	plural like greens should remain	green	plural like greens should remain
3	overgreen shouldn't be deleted	red	overgreen shouldn't be deleted
4	some greener stuff should stay	*	some greener stuff should stay
5	some string green stuff	*	plural like reds should remain
6	Green should get blacklisted too	*	overred shouldn't be deleted
7	And of course GREEN uppercase should be deleted	*	some reder stuff should stay
8	plural like reds should remain	*	*
9	overred shouldn't be deleted	*	*
10	some reder stuff should stay	*	*
11	some string red stuff	*	*
12	Red should get blacklisted too	*	*
13	And of course RED uppercase should be deleted	*	*
Sheet1

macro:

Sub Cleaned_List()
Application.ScreenUpdating = False
Dim myval As String, lastcolumn As Long
Dim add1 As Long, add2 As Long
Dim xrow As Long, newlastrow As Long

Dim i As Long, lastrow As Long
Dim arr As Variant, item As Variant
lastrow = Cells(Rows.Count, 2).End(xlUp).Row
arr = Range(Cells(2, 2), Cells(lastrow, 2))

Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row).Copy Range("C2")
Range(Range("C2"), Range("C2").End(xlDown)).EntireColumn.AutoFit

For Each item In arr
    Range("D2:D" & Cells(Rows.Count, "C").End(xlUp).Row).Formula = _
   "=IF(OR(ISNUMBER(SEARCH({"" " & item & " ""},"" ""&C2&"" ""))),""Remove"",""Leave"")"

myval = "Remove"
newlastrow = Cells(Rows.Count, 3).End(xlUp).Row
lastcolumn = Cells.Find(What:="*", After:=Range("A1"), SearchOrder:= _
    xlByColumns, SearchDirection:=xlPrevious).Column
add1 = Columns(4).Find(What:=myval, LookIn:=xlValues, LookAt:=xlWhole).Row
xrow = add1

Do
If Cells(xrow + 1, 4).Value <> myval Then
add2 = xrow + 1
Exit Do
Else
xrow = xrow + 1
End If
Loop Until xrow = lastrow + 1
add2 = xrow

Range(Cells(add1, 3), Cells(add2, 4)).Select
Selection.Delete Shift:=xlUp

Next
Columns("D").Delete
Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Select
Application.ScreenUpdating = True
End Sub