VBA code that returns a unique list of non consecutive items, for a lookup value from a table

[Nando1988]

I need some vba code, that will return a list of unique non consecutive items from a table, when a certain value changes in one of the sheets.
I have been trying with this formula, but I have had no luck.

[COLOR=#333333][FONT=arial]=INDEX('Price List'!$A$3:$C$57000, SMALL(IF('Price Sheet'!$B$4='Price List'!$A$3:$A$57000, ROW('Price List'!$A$3:$A$57000)-MIN(ROW('Price List'!$A$3:$A$57000))+1, 0), ROW(A1)),COLUMN(A1))[/FONT][/COLOR]

It would be a lot easier if I used VBA, instead of the formula, but I don't know how to do this.
Any help will be greatly appreciated.
Thanks.

 

[StephenCrump]

I'm not sure what you mean by a list of unique non consecutive items from a table?

You're using an INDEX/SMALL construction, which typically is used to return the 1st, 2nd, 3rd etc match. Is this what you want?

If so, this 0 is going to cause you problems:

=INDEX('Price List'!$A$3:$C$57000, SMALL(IF('Price Sheet'!$B$4='Price List'!$A$3:$A$57000, ROW('Price List'!$A$3:$A$57000)-MIN(ROW('Price List'!$A$3:$A$57000))+1, 0), ROW(A1)),COLUMN(A1))

You're also returning column A with your formula:
=INDEX('Price List'!$A$3:$C$57000, .... ,COLUMN(A1)) when this is the column you're testing against 'Price Sheet'!$B$4.

If you want Column B, say, then try:
=INDEX('Price List'!$B$3:$B$57000, SMALL(IF('Price Sheet'!$B$4='Price List'!$A$3:$A$57000, ROW('Price List'!$A$3:$A$57000)-MIN(ROW('Price List'!$A$3:$A$57000))+1), ROW(A1)))

I'm also not sure why you say this would be easier with VBA?

 

[Trebor76]

The following three steps will also produce a list of unique entries (best from Excel 2010):

1. Highlight the column with the data (Col. A in your case)
2. From the DATA ribbon select Remove Duplicates from the Data Tools tab
3. Make the necessary selections from the Remove Duplicates dialog and then click OK

HTH

Robert

 

[Trebor76]

Here's a VBA method of my procedure in thread 3:

Option Explicit
Sub Macro1()

    Dim lngMyCol As Long
    
    lngMyCol = 1 'i.e. Col. A

    Columns(lngMyCol).RemoveDuplicates Columns:=lngMyCol, Header:=xlYes

End Sub