Create a list box of unique values

[ChrisEngland]

Can anyone help please?

I have a column A in my worksheet which has a list of user name names and a column B which has a value against each user. Each user will appear several times in column A

I would like to create a list box which just holds the unique names from column A but I'm struggling!

I have found a suggestion via Google but it end in e reference error and I'm struggling to understand why. The formula I'm using to populate the source of a list box is:

=OFFSET(Sheet1!$A$2, 0, 0, COUNT(IF(Sheet1!$A$1:$A$1000=””, "", 1)), 1)

Could anyone shed any light on what I'm doing wrong please?

Thanks

Chris

 

[ChrisEngland]

I managed to resolve this using the following formula to create a list starting at AA2 which I used for the list box source

=INDEX($A$2:$A$9999, MATCH(0,COUNTIF($AA$1:AA1, $A$2:$A$9999), 0))

(Ctrl, shift, Enter) then drag the cell at AA2 down to get the unique list

Works a treat