How to get Unique Values in an Index

[Lecurota]

Hi All,

I've been trying to find out how to get unique values out of an index that I have. What I need is to generate a list of unique identifiers from a criteria , in this case, "AMZ". It's referencing a table, and I'd like to be able to see a list of anything that has that code "AMZ" but only 1 of each identifier if that makes sense. I'd also like it to pull in a descending fashion, so if I arrange my list as date in descending order, it'd pull the earliest identifier first. The Formula works for now, but I need to know what I need to put in the formula, in order to eliminate duplicate values.


Here is my formula

=IFERROR(INDEX($A:$C,SMALL(IF("A"=$C:$C,ROW($C:$C)-ROW(INDEX($C:$C,1,1))+1),ROWS(A1)),2),"")


Attached is an example.

[Lecurota]

I attached a reference example book.

[63falcondude]

The following is based off of your sample workbook and expected results (in the workbook) from post #1.

In E3, try this:

=IFERROR(INDEX(B$2:B$14,MATCH(0,IF(C$2:C$14="A",COUNTIF(E$2:E2,B$2:B$14),""),0)),"") Ctrl Shift Enter

[Lecurota]

The following is based off of your sample workbook and expected results (in the workbook) from post #1.

In E3, try this:

=IFERROR(INDEX(B$2:B$14,MATCH(0,IF(C$2:C$14="A",COUNTIF(E$2:E2,B$2:B$14),""),0)),"") Ctrl Shift Enter

That works, but for each subsequent cell, there's an intermittent lag proceeding the CTRL SHIFT ENTER

[63falcondude]

I'm not sure what that means. Are you saying that the formula is slow?

[MarvinP]

Hi Lecurota,

It sounds like you want to do an Advanced Filter of your data using a "*AMZ*" for the criteria. If you sort by Date from old to new and check the box in the Advanced Filter Dialog that wants "Unique Records Only" it might give what you want. It is still hard to determine the question without a sample workbook.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.