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,
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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.
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,099
Members
452,301
Latest member
QualityAssurance

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top