Unique entries updated dynamically

[vacation]

Hi,

I have 5,000 rows of data in sheet1. Here is a sample:
Bob, ...
Tom, ...
Bob, ...
Pat, ...
Tom, ...

I would like to have:
1. Unique entries in column B of sheet2
2. These uinque entries come from column A of sheet1
3. So sheet2 column B would contain Bob, Tom, Pat
4. Every time the data is updated in sheet1 then sheet2 should be automatically updated with these non-duplicates from sheet1

How can I do this?

Thanks,

 

[north19701]

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Column > 1 Then Exit Sub
Range("a1", Range("a65536").End(xlUp)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheets("Sheet2").Range("B1" _
        ), Unique:=True
End Sub

Right-click on sheet1 and select view code, then paste the above code into the worksheet

 

[Aladin Akyurek]

Applying Advaced Filter thru a Worksheet_Change event is attractive.

A formula approach is also possible...

Sheet1

Book1
	A	B	C	D
1	12
2	Name	0
3	Bob	1
4	Tom	2
5	Bob
6	Pat	3
7	Tom
8
9	Jacob	4
10	Jon	5
11	Brian	6
12	Jon
13
Sheet1

A1:

=MATCH(REPT("z",255),Sheet1!A:A)

which delivers a value that can be used by code which copies formulas down.

B2 must house a 0.

B3 houses

=IF((A3<>"")*ISNA(MATCH(A3,$A2:A$2,0)),LOOKUP(9.99999999999999E+307,$B$2:B2)+1,"")

which is copied down (manually or by code).

Sheet2

Book1
	B	C	D	E
1	6
2	Name
3	Bob
4	Tom
5	Pat
6	Jacob
7	Jon
8	Brian
9
10
Sheet2

B1 houses:

=LOOKUP(9.99999999999999E+307,Sheet1!B:B)

whose result can also be used code which would copy formulas down.

B3 houses:

=IF(ROW()-ROW($B$3)+1<=$B$1,INDEX(Sheet1!A:A,MATCH(ROW()-ROW($B$3)+1,Sheet1!B:B)),"")

which is copied down either manually or by code.

 

[jpettit]

Can someone comment on a similar problem i have?:
Topic: http://www.mrexcel.com/board2/viewtopic.php?t=84902

My problem is less complex. Thanks.