# makeing a unique list from a column that updates

#### demslam

##### New Member
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

### Excel Facts

Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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
Code:
``=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:
Code:
``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
ABCD
1Running Count Unique RecordsLabelUnique List
21abcabc
31abcdef
41abcghi
52defjkl
63ghi
73ghi
84jkl
Sheet1

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
Code:
``````Sub myFilter()
Sheet2.Activate
End Sub``````

lenze

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!

demslam said:
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
ABCD
105
2ItemIdxD-List
3jon1jon
4damon2damon
5bob3bob
6brian4brian
7jon dan
8damon
9brian
10dan5
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),"")

Replies
3
Views
245
Replies
3
Views
77
Replies
10
Views
269
Replies
1
Views
289
Replies
2
Views
307

1,203,203
Messages
6,054,117
Members
444,703
Latest member
pinkyar23

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back