• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need formula to compare and remove data

S

Sreekanth247

New Member
Hello Ninjas,

Basically there are 2 lists, one is master list of 9333 names, other list is of 2574 names which is also present in the master list which i want to remove. How to ?
 
Thanks, but I need the formula, Column A in a worksheet has Master Data of 9333, Column A in the next worksheet has 2574.
 
Hi,

Well formulas can't delete entries from the cell. So here my solution as per your sample file:

1. Create a helper column in Column C with the formula like one below in C2 and copy down:
=ISNUMBER(MATCH(Sheet1!$A2,Sheet1!$B$2:$B$15,0))

This will return TRUE or FALSE. TRUE if the id of column A is in bounced list and FALSE if it is not there.

2. Now apply filter to all the three columns A,B & C. Put TRUE as criteria in column C. You will get a list of ids which got bounced.

3. Delete the rows.

Regards,
 
Deepak said:
Do you want a new list without "Bounced Email ID" from "Master Email ID" ??
Click to expand...

I your reply would be Yes then this will be helpful !!!

With Ctrl+Shift+Enter

C2 =

IFERROR(INDEX($A$2:$A$21,SMALL(IF((COUNTIF($B$1:B2,$A$2:$A$21)=0)*(ROW($A$2:$A$21)-1)>0,ROW($A$2:$A$21)-1,""),ROWS($B$2:B2))),"")
 

Attachments

  • Sample File for adding formula.xlsx
    11.4 KB · Views: 2
Thanks Ninjas, you people rock,
Deepak said:
I your reply would be Yes then this will be helpful !!!

With Ctrl+Shift+Enter

C2 =

IFERROR(INDEX($A$2:$A$21,SMALL(IF((COUNTIF($B$1:B2,$A$2:$A$21)=0)*(ROW($A$2:$A$21)-1)>0,ROW($A$2:$A$21)-1,""),ROWS($B$2:B2))),"")
Click to expand...

Thanks buddy, it worked, you rock.
 
Ninjas, it worked on one file and did not work on the other, maybe iam at mistake, is there a way i can send the file it to ninja alone without exposing to other members ?
 
Sreekanth247 said:
Ninjas, it worked on one file and did not work on the other, maybe iam at mistake, is there a way i can send the file it to ninja alone without exposing to other members ?
Click to expand...

You are free to send it to me.

**********
 
Last edited:
Sreekanth247 said:
Hi Deepak

It has been sent, kindly do the needfull.
Click to expand...

Hi,

In the large database it will slow down the process & take much time.


Using VBA speedup the same.

Code: 
Sub email_loop()
Dim master As Range, bounced As Range, r As Range

Set master = Range("A2").Resize(Application.CountA(Columns(1)) - 1)
Set bounced = Range("b2").Resize(Application.CountA(Columns(2)) - 1)

For Each r In master
Application.StatusBar = r.Row
    If Not Application.CountIf(bounced, r.Value) > 0 Then _
        Cells(Application.CountA(Columns(3)) + 1, 3) = r.Value
Next
Application.StatusBar = False
MsgBox "Done", vbInformation
End Sub

Check inbox...

I have also optimize the workbook & size has been reduced by about 90%.
 
You must log in or register to reply here.
Back
Top