Solved I want to Avoid duplicate email ids in excel while importing

July 9, 2013 at 15:45:07
Specs: Windows 7
i want like this
I have data base email ids and our team is collecting email ids .Original excel sheet kept by me and duplicate excel collected by my team (members 8) Problem is while copying email ids form other 8 members excel sheet to my original sheet i am finding duplicates which is already in my data base . So I need to avoid duplicate email ids

See More: I want to Avoid duplicate email ids in excel while importing

Report •


✔ Best Answer
July 10, 2013 at 08:10:36
OK, what am I doing wrong?

Your formula is in Column C, mine is in Column D.

With your formula I see a strange mixture of existing and new. With mine, I only see the new ones.

	A	B	C	D
1	Master	New	Mike	DD03
2	a	a		
3	b	s		s
4	c	z		z
5	d	c	c	
6	e	x		x
7	f	r	r	r
8	g	y	y	y
9	h	b	b	
10	i	t	t	t
11	j	q	q	q
12	k	e	e	
13	l	m	m	m

Seems to me that if you are returning the value from Column B if the value in Column A isn't found in Column B, you are going to get inconsistant results.

e.g. Look at Row 5. With your formula, "d" (A5) will not be found in Column B, so "c" (B5) will be returned. However, "c" is not new since it exists in Column A.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
July 9, 2013 at 19:19:33
Put all the ids in one long list and use Excel's Advanced Filter feature to create a list of unique ids.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
July 10, 2013 at 00:11:42
no I want new email ids only which is not duplicate . because I have to report to my manager (new email ids only)

Report •

#3
July 10, 2013 at 06:38:50
Try this:

With your Master List in column A
Your New collected id's in Column B
Put this formula in Column C and drag down 100 rows.
If you need more than 100 rows, modify the formula as needed

=IF(ISERROR(MATCH(A1,$B$1:$B$100,0)),B1,"")

Column C should now contain only those ID that do not appear in column A

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
July 10, 2013 at 07:42:29
Mike, are you sure you don't have that backwards?

With the Master List in A and the new ID's in B, shouldn't he use something like this?

=IF(ISNA(MATCH(B1,$A$1:$A$100,0)),B1,"")

Don't we want to check the new ID (B1) against the existing ID's in Column A and return B1 if it isn't found?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
July 10, 2013 at 07:53:53
It actually doesn't matter which way you write it, as it works both ways:

=IF(ISERROR(MATCH(B1,$A$1:$A$13,0)),B1,"")

or

=IF(ISERROR(MATCH(A1,$B$1:$B$13,0)),B1,"")

It's still a "one to many" comparison.

Also, to shorten it up a bit you could also do:

=IF(ISNA(MATCH(A1,$B$1:$B$13,0)),B1,"")

As #N/A is the actual error being returned.

MIKE

http://www.skeptic.com/


Report •

#6
July 10, 2013 at 08:10:36
✔ Best Answer
OK, what am I doing wrong?

Your formula is in Column C, mine is in Column D.

With your formula I see a strange mixture of existing and new. With mine, I only see the new ones.

	A	B	C	D
1	Master	New	Mike	DD03
2	a	a		
3	b	s		s
4	c	z		z
5	d	c	c	
6	e	x		x
7	f	r	r	r
8	g	y	y	y
9	h	b	b	
10	i	t	t	t
11	j	q	q	q
12	k	e	e	
13	l	m	m	m

Seems to me that if you are returning the value from Column B if the value in Column A isn't found in Column B, you are going to get inconsistant results.

e.g. Look at Row 5. With your formula, "d" (A5) will not be found in Column B, so "c" (B5) will be returned. However, "c" is not new since it exists in Column A.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#7
July 10, 2013 at 10:30:08
OK, I see what you mean, so we reverse the formula.

=IF(ISNA(MATCH(B3,$A$3:$A$14,0)),B3,"")

MIKE

http://www.skeptic.com/


Report •

#8
July 10, 2013 at 11:09:22
thank u very much

Report •

#9
July 10, 2013 at 11:10:22
thank u .. your my exact problem shooter.

Report •

Ask Question