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

Nando1988

New Member
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.
Code:
[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

Well-known Member
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

Well-known Member
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
 
Last edited:

Trebor76

Well-known Member
Here's a VBA method of my procedure in thread 3:

Code:
Option Explicit
Sub Macro1()

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

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

End Sub
 

Some videos you may like

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Macro to copy values across rows and transposing them and add the user id
    [FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Hi,[/COLOR][/SIZE][/FONT] [FONT=Times New...
Top