makeing a unique list from a column that updates

[demslam]

i am trying to filter through a list (column) so it will pull out unique values and place it on another sheet. what is the easiest way of doing this so that if i add information to the column my unique list will automatically update if a value that is added is unique.

PS. dont know much about Macros

-Matt

 

[Oaktree]

Welcome to the board.

There are several ways you could do this. One way is:

1) create a new column which counts the unique records through the current row. To do this, put 1 in cell A2 and put

=IF(COUNTIF($B$2:B2,B3)>0,A2,A2+1)

in A3. Copy down through the end of your range
2) Your unique list can be calculated as:

D2 =IF(ROW(D2)-ROW($D$1)>MAX(A:A),"",VLOOKUP(ROW(D2)-ROW($D$1),A:B,2,0))

copied down

If you insert a new item at the bottom of column B, the most you would have to do is copy down the formulas in columns A and D.

Book19
	A	B	C	D
1	Running Count Unique Records	Label		Unique List
2	1	abc		abc
3	1	abc		def
4	1	abc		ghi
5	2	def		jkl
6	3	ghi
7	3	ghi
8	4	jkl
Sheet1

 

[lenze]

Say your list is on Sheet1 and you wish to copy to Sheet2

Give your list on Sheet1 a name (You may want to make it dynamic), say "Source"
Now on Sheet2, name the first cell where you want the filtered results placed, say "Target"

Here is the code

Sub myFilter()
    Sheet2.Activate
    Range("Source").AdvancedFilter Action:=xlFilterCopy,  CopyToRange:=Range("Target"), Unique:=True
End Sub

lenze

 

[Domenic]

Here's another way...

Assuming that A2:A10 contains your data, try the following...

B1: leave empty

B2, copied down:

=IF(OR(COUNTIF($B$1:B1,A2:$A$10)=0),INDEX(A2:$A$10,MATCH(0,COUNTIF($B$1:B1,A2:$A$10),0)),"")

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

 

[Aladin Akyurek]

i am trying to filter through a list (column) so it will pull out unique values and place it on another sheet. what is the easiest way of doing this so that if i add information to the column my unique list will automatically update if a value that is added is unique.

PS. dont know much about Macros

-Matt

aaExtractUniqueList demslam.xls
	A	B	C	D
1		0	5
2	Item	Idx	D-List
3	jon	1	jon
4	damon	2	damon
5	bob	3	bob
6	brian	4	brian
7	jon		dan
8	damon
9	brian
10	dan	5
11
Sheet1

B1 must house a 0.

B3, copied down:

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

C1:

=LOOKUP(9.99999999999999E+307,B3:B10)

C3, copied down:

=IF(ROW()-ROW(C$3)+1<=$C$1,LOOKUP(ROW()-ROW(C$3)+1,$B$3:$B$10,$A$3:$A$10),"")