Newbie Question: Copying column entries if appearing in multiple columns

[Filmco]

Sorry for what is probably silly newbie question. I have a mailing list with 8000 email addresses, and I want to remove all email addresses who have not opened an email in last 8 newsletters sent to reduce to only those who actively read the email.
I have column A containing 8000 email addresses, and columns b, c, d, e , f, g, h, i, j, which contain the email addresses of those that have opened various newsletters. I removed duplicate entries from within each individual column and need to compare duplicate entries between column a and b-j to then delete from column A all entries which are not in at least one (or more) of columns b-j.
eg
column a b c d
bob@bob.com jane@jane.com bob@bob.com jane@bob.com
ruth@ruth.com
doris@doris.com
jane@jane.com bob@bob.com

in this case I would want to remove ruth and doris since they do not exist in any of the other columns so then column A will end up representing a trimmed down active list.

Is there a simple formula to do this, thanks,

 

[JLGWhiz]

See if this works for you.

Sub t()
Dim i As Long, lr As Long, fn As Range
lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    For i = lr To 2 Step -1
        With ActiveSheet
            Set fn = .Range("B2:J" & lr).Find(.Cells(i, 1).Value, , xlValues, xlWhole)
                If fn Is Nothing Then
                    .Cells(i, 1).Delete xlShiftUp
                End If
        End With
    Next
End Sub

Be sure you have a back up since you cannot reverse deletions by code.

 

[Filmco]

See if this works for you.

Sub t()
Dim i As Long, lr As Long, fn As Range
lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    For i = lr To 2 Step -1
        With ActiveSheet
            Set fn = .Range("B2:J" & lr).Find(.Cells(i, 1).Value, , xlValues, xlWhole)
                If fn Is Nothing Then
                    .Cells(i, 1).Delete xlShiftUp
                End If
        End With
    Next
End Sub

Be sure you have a back up since you cannot reverse deletions by code.

thanks for this, but unfortunately vba code is beyond me, was hoping for simole excel formula count if sort of thing.n

 

[JLGWhiz]

Is there a simple formula to do this, thanks

then the anser to this is, "No". But you might be able to use Conditional Formatiing with an If() function and get the cells in column A to show as blank if a match is not found in the other columns. i am not very good at formulas, so I will let someone else try that.

 

[Filmco]

anyone else any ideas for a simple excel formula for this? thanks.