Autofilter > Unique Values

[MarkAndrews]

I have a list, which i run the auto filter on to extract a unique list

However - Contained in this list are Employee names and also document types

What i need to do is get rid of anything that isnt a name, is there a way Excel can do this?

TIA

Mark

 

[Andrew Poulsom]

How would one determine if it's an employee name or a document type?

 

[MarkAndrews]

Andrew - Here's a sample of my data (although my list is 200+ rows)

Red = Employee Names

Efunds.xls
	A	B	C	D
1	Name
2	A94B94INsProcessed:
3	AlwinFernandes
4	AmjadShaikh
5	AnupBatwar
6	ArunLazur
7	AudreyNataliaSoares
8	AUTHACTsProcessed:
9	BlandinaTrindale
10	BLDPOLINsProcessed:
11	BonaventureDsouza
12	BROKACCTsProcessed:
13	BROKAUTHsProcessed:
14	CANCELsProcessed:
15	CassandraMascarenhas
16	CharlesChettiar
17	CHCERTINsProcessed:
18	ChetanVikas
19	COMMSINsProcessed:
20	CONFINsProcessed:
21	COTsProcessed:
22	DelilahCowan
23	Detail:
24	DhananjayThingalaya
25	DivyaDesai
26	DocumentClass:
27	DocumentClass:A94B94IN
28	DocumentClass:AUTHACT
29	DocumentClass:BLDPOLIN
30	DocumentClass:BROKACCT
Sheet2

 

[Andrew Poulsom]

C1 must be blank. In C2 enter:

=ISNUMBER(SEARCH(":",A2))

In C4 type Name.

Data|Filter|Advanced Filter:

Copy to another location
List Range $A$1:$A$30
Criteria Range $C$1:$C$2
Copy to $C$4

 

[MarkAndrews]

Thanks Andrew - I'll give it a whirl

 

[MarkAndrews]

Andrew - Here's what i end up with

Efunds.xls
	C	D	E	F
1
2	TRUE
3	AlwinFernandes
4	Name
5	A94B94INsProcessed:
6	AUTHACTsProcessed:
7	BLDPOLINsProcessed:
8	BROKACCTsProcessed:
9	BROKAUTHsProcessed:
10	CANCELsProcessed:
11	CHCERTINsProcessed:
12	COMMSINsProcessed:
13	CONFINsProcessed:
14	COTsProcessed:
15	Detail:
16	DocumentClass:
17	DocumentClass:A94B94IN
18	DocumentClass:AUTHACT
19	DocumentClass:BLDPOLIN
20	DocumentClass:BROKACCT
21	DocumentClass:BROKAUTH
22	DocumentClass:CANCEL
23	DocumentClass:CHCERTIN
24	DocumentClass:COMMSIN
25	DocumentClass:CONFIN
26	DocumentClass:COT
Sheet2

 

[Andrew Poulsom]

And what did you expect? How did Alwin Fernandes get in C3?

 

[MarkAndrews]

Sorry, I've follwed your instructions as above & I get

"The extract range has a missing or illegal field name"

?

 

[Andrew Poulsom]

I didn't get that when I followed my instructions. Please delete column C and try again.

 

[MarkAndrews]

Hi Andrew, I've followed your instructions again & it still gives me an error

"The extract range has a missing or illegal field name"