Extracting singular values from a list

[nevjones]

Please can someone help me to extract singular values from a list. Using the advanced filter and selecting unique values is not what i'm after because it returns values that are recurring. Here is my list of names:

<TABLE style="WIDTH: 92pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=123 x:str><COLGROUP><COL style="WIDTH: 92pt; mso-width-source: userset; mso-width-alt: 4498" width=123><TBODY><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 92pt; HEIGHT: 16.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" id=td_post_332308 height=22 width=123>Donald</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=22>Edna Marie</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=22>Edna Marie</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=22>Bessie</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=22>Diane Florence</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=22>Diane Florence</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=22>Averil Eileen</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=22>Averil Eileen</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=22>Albert Walter</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=22>Albert Walter</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=22>Pauline</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=22>Lucy</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=22>Lilian</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=22>Lilian</TD></TR></TBODY></TABLE>

The result I'm after extracts

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 16.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" id=td_post_332308 height=22 width=64>Donald</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=22>Bessie</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=22>Pauline</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=22>Lucy</TD></TR></TBODY></TABLE>

or put another way eliminates duplicate entries.

Thanks in advance, Nev

 

[GlennUK]

Have an extra column with a formula to count the number of entries of that type, like
=COUNTIF($A$1:$A$15,A1)
and then filter by values of 1 in that column. You could even do advanced filter to copy the results to another location, using Criteria to control the filtering.

 

[nevjones]

Thanks Glenn, I've tried this but no joy.

 

[GlennUK]

Thanks Glenn, I've tried this but no joy.

Describe what you did exactly. And tell in what way it didn't work ( rather than just no joy ).

 

[GlennUK]

My example:

Excel Workbook
	A	B	C	D	E	F
1	Names	Count		Count		Names
2	Donald	1		1		Donald
3	Edna Marie	2				Bessie
4	Edna Marie	2				Pauline
5	Bessie	1				Lucy
6	Diane Florence	2
7	Diane Florence	2
8	Averil Eileen	2
9	Averil Eileen	2
10	Albert Walter	2
11	Albert Walter	2
12	Pauline	1
13	Lucy	1
14	Lilian	2
15	Lilian	2
Sheet11

using Advanced Filter with Criteria in D1:D2

 

[nevjones]

Apologies Glenn, it works now. I had a header row and I didn't adjust the formula accordingly. Doh!
Many thanks.

 

[GlennUK]

That's great!