Formulas

[drewrogers]

Hi all,

I have a list of names where some recur. I trying to find a formula that will create a list of all the names but without the duplicates. Does this exist?

Thanks so much

Drew

[#2]

Hello James. If you have Office 365, Excel has a UNIQUE function. The UNIQUE function will extract a list of distinct values or unique values. Unique are values that occur only once. It sounds like you are looking for distinct, which it handles also. The UNIQUE function is a dynamic array function. Here is a video I created on how to use the function. https://youtu.be/P5QQPvDeFoo If you didn't want to use a function, you could also Copy and Paste and use Remove Duplicates which is on the Data Tab under the group Data Tools. One last thing you could use is a PivotTable and use the Distinct function. Here is a video I created on that feature. https://youtu.be/aPYDMaeLncg

[#3]

@drewrogers 

There are several ways you can extract the unique items from a list.

1. Array Formula
2. Remove Duplicate feature: I this is one time requirement, you may copy your list of names to a blank column, go to Data Tab --> click on Remove Duplicates and this will remove all the duplicates leaving behind a list of all the unique names.
3. Pivot Table: You can insert a Pivot Table and drag the Name Field from the Field List into the Rows area and you will see a unique list of names in the Pivot Table.
4. Power Query: You can create a query using Power Query and remove the duplicates in the query editor and load it back to the worksheet, a list of unique names.
5. VBA: Use VBA to generate a list of unique names.
6. Dynamic Array Formula Unique: If you have access to the dynamic array formula, you can use UNIQUE function to get a unique list of names. Assuming your data is in A2:A25, place the formula =UNIQUE(A2:A25) in a blank cell and hit Enter.

In case of Array Formula, assuming your list is in A2:A25, place this Array Formula which requires confirmation with Ctrl+Shift+Enter instead of Enter alone.

In C2

=IFERROR(INDEX($A$2:$A$25,MATCH(0,INDEX(IF($A$2:$A$25<>"",COUNTIF($C$1:C1,$A$2:$A$25)),0,0),0)),"")

Confirm with Ctrl+Shift+Enter and copy it down until you get blank cells.

Adjust the range referred in the formula as per your requirement.

You may also create a Dynamic Named Range and refer it in the formula instead of referring ranges.

If you plan to place the above formula in another column, don't forget to change the bold column letters in the above formula as per your formula column.

Please find the attached with Array Formula, Dynamic Array Formula and Pivot Table solutions to get a unique list of names.

[#4]

@chrismenard7 

Thanks you very much!

[#5]

@drewrogers 

My non-array version of that which was suggested by @Subodh_Tiwari_sktneer is: 

=IFNA(LOOKUP(2,1/(
COUNTIF(A$2:A$25,">="&A$2:A$25)=AGGREGATE(14,6,
COUNTIF(A$2:A$25,">="&A$2:A$25)/(COUNTIF(C$1:C1,A$2:A$25)=0),ROW()-1)),
A$2:A$25),"")