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

Nando1988

New Member
Joined
Aug 21, 2019
Messages
20
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.
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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?
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,273
Members
448,559
Latest member
MrPJ_Harper

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top