Newbie Question: Copying column entries if appearing in multiple columns

Filmco

New Member
Joined
Nov 18, 2016
Messages
3
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,
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
11,762
Office Version
2013
Platform
Windows
See if this works for you.

Code:
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

New Member
Joined
Nov 18, 2016
Messages
3
See if this works for you.

Code:
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

Well-known Member
Joined
Feb 7, 2012
Messages
11,762
Office Version
2013
Platform
Windows
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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,095,400
Messages
5,444,253
Members
405,278
Latest member
Rashford

This Week's Hot Topics

Top