Deleting almost unique records

N

Nimrod

MrExcel MVP

Joined

Apr 29, 2002

Messages

6,259

• Dec 13, 2003

• #2

Please give a couple of examples of the Text and what you would like extracted from it .

 

Upvote 0

N

normpam

Active Member

Joined

Oct 30, 2002

Messages

362

Office Version

1. 365

Platform

1. Windows

• Dec 13, 2003

• #3

NAME TEL EMAIL
John 1234 nn@ssss.com
Steve 5555 bb@sssss.com
John 4444 nn@ssss.com
etc
etc
etc

If I use Filter Unique Records I would still get both "John" records. Without writing code, how to get a list with only unique records based on the E-mail address being unique?

 

Upvote 0

N

nisht

Active Member

Joined

Feb 27, 2002

Messages

304

• Dec 13, 2003

• #4

nosduplicate.xls
	F	G	H	I	J
8	COL2
9	keyur	nsdesai@eth.net	0	firstzeroisyourunqiuerecords
10	mait	na@excite.com	0
11	manisha	na@excite.com	2		byusingadvancefilter
12	manit	ma@pinfra.com	0		nsdesai@eth.net
13	mehul	na@excite.com	3		na@excite.com
14	ni****h	ma@pinfra.com	2		ma@pinfra.com
15	utpal	ut@guj.com	0		ut@guj.com
16	uy	uy@new.com	0		uy@new.com
17	mehul	manit@yahoo.com	0		manit@yahoo.com
18	manit	ma@pinfra.com	3		shahil@india.com
19	shahil	shahil@india.com	0		new@exciet.com
20	utpal	new@exciet.com	0		utpal@yahoo.com
21	utpal	utpal@yahoo.com	0		utpal@hotmail.com
22	utpal	utpal@hotmail.com	0		pexcel@pexcel.com
23	keyur	pexcel@pexcel.com	0		nsdesai@eth.net
24	hiren	nsdesai@eth.net	2		nisht@mrexcel.com
25	keyur	nsdesai@eth.net	3		ndd@aol.com
26	ni****h	nisht@mrexcel.com	0
27	mait	ndd@aol.com	0
unique value (2)

Use advance filter and then select only the column where you want the unique records.

dont give any conditions in criteria range.

filter that record at other place.

see if that is what you want.

 

Upvote 0

N

normpam

Active Member

Joined

Oct 30, 2002

Messages

362

Office Version

1. 365

Platform

1. Windows

• Dec 13, 2003

• #5

Thanks. Problem is, that I also need to get the other columns of information as well. I am not only trying to get the isolated E-mail address.

 

Upvote 0

Y

Yogi Anand

MrExcel MVP

Joined

Mar 12, 2002

Messages

11,454

• Dec 13, 2003

• #6

normpam said:

Thanks. Problem is, that I also need to get the other columns of information as well. I am not only trying to get the isolated E-mail address.

Click to expand...

Hi normpam:

How about ...

y031213h1.xls
	A	B	C	D	E	F	G
1		source
2		criteria			TRUE
3		copy_to_forAdvancedFilter
4
5	NAME	TEL	EMAIL		NAME	TEL	EMAIL
6	John	1234	nn@ssss.com		John	1234	nn@ssss.com
7	Steve	5555	bb@sssss.com		Steve	5555	bb@sssss.com
8	John	4444	nn@ssss.com		Kay	3333	kk@kkkk.com
9	Kay	3333	kk@kkkk.com
10	Kay	3331	kk@kkkk.com
Sheet4

The formula in cell E2 is =COUNTIF(C6:$C$6,C6)=1

Would this do?

 

Upvote 0