Array formula to return an ARRAY without duplicates, without VBA

excel unique function not available
excel data validation list unique values only
vlookup unique values
excel create list of unique values from multiple sheets
how to extract unique values based on criteria in excel
excel vba get unique values from column into array
find unique values in excel multiple columns
excel copy unique values to another worksheet vba

I would like to know whether it's possible to return an array from a single cell formula, which is filtered to remove duplicates, and which is built purely on Excel formulas.

I'm aware of approaches to return a list of values where the duplicates are removed (see this question), where the list is spread over multiple cells. However I specifically want to return an array intermediate.

E.g. For the list in A1:A5, I can get an array of values {0.1,0.2,0.2,0.7,0.3}, from which I want a second array {0.1,0.2,0.7,0.3}, as an intermediate in an array formula. Current approaches use single-end anchored ranges (like C$1:C1) to iterate through the items in the array geometrically (by dragging down column C). I would like to leave the array un-iterated, within the formula. I can then manipulate this as I would any other array.

All this should take place in a single cell if possible.

NB

Both MacroMarc's and Barry Houdini's answers are perfectly valid, and I ran a speed check on each - there was negligible difference (any difference was smaller than the variation between test runs). Both scored ~ 1.0±0.2 ms

I have used a defined name for the Range (A1:A5) and called it myList. You can do the same, or substitute in the Address $A$1:$A$5 if you wish:

{=INDEX(myList, N(IF({1}, MODE.MULT(IF(MATCH(myList, myList, 0) = ROW(myList), ROW(myList)*{1,1})))), 1)}

EDIT: Above wasn't robust to handle if the column list is further down the sheet, and a shorter minrow routine courtesy of OP:

{=INDEX(myList, N(IF({1}, MODE.MULT(IF(MATCH(myList, myList, 0)=ROW(myList)-MIN(ROW(myList))+1, (ROW(myList)-MIN(ROW(myList))+1)*{1,1})))), 1)}

This should be ok for you. Needless to say, these are array formulas..

Array Formula to Remove Duplicates from List, Excel Facts. Who is Mr Spreadsheet? Click here to reveal answer. In Column B I want to make a list without the duplicates so it shows the unique IDs from the list in Column A. I know I can achieve this by using either the "Advanced Filter" or by the "Remove Duplicate" function unter the tab data tools.

This formula will return a sorted array without duplicates, e.g. for your example

{0.1;0.2;0.3;0.7}

=SMALL(IF(MATCH(A1:A5,A1:A5,0)=ROW(A1:A5)-ROW(A1)+1,A1:A5),ROW(INDIRECT("1:"&SUM(0+(0<(FREQUENCY(A1:A5,A1:A5)))))))

confirmed with CTRL+SHIFT+ENTER

......or this version will keep the order

=INDEX(A1:A5,N(IF({1},SMALL(IF(MATCH(A1:A5,A1:A5,0)=ROW(A1:A5)-ROW(A1)+1,ROW(A1:A5)-ROW(A1)+1),ROW(INDIRECT("1:"&SUM(0+(0<(FREQUENCY(A1:A5,A1:A5))))))))))

5 easy ways to extract Unique Distinct Values, I am not sure if all array formulas can be converted to regular formulas, but some can. The formula in column B extracts unique distinct values from column A The INDEX function returns a value from a given cell range based on a Put your VBA code here. [/vb]. How to add a picture to your comment: Excel Dynamic Array Functions are a true game changer. These newly introduced DA functions can filter, sort, remove duplicates and do much more. The output of these functions can go to a range of cells. Hence the name – dynamic array functions.

Use this array formula to sum the unique, it requires the use of TEXTJOIN() which is only available with Office 365 Excel:

=SUM( IF(ISERROR(FIND(TRIM(MID(TEXTJOIN(REPT(" ",999),TRUE,A:A),(ROW(INDIRECT("1:" & COUNTA(A:A)))-1)*999+1,999)),MID(TEXTJOIN(REPT(" ",999),TRUE,A:A),1,(ROW(INDIRECT("1:" & COUNTA(A:A)))-1)*999))),--TRIM(MID(TEXTJOIN(REPT(" ",999),TRUE,A:A),(ROW(INDIRECT("1:" & COUNTA(A:A)))-1)*999+1,999))))

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

You can replace SUM with many different formula.

If one does not have Office 365 Excel then vba and or helper columns are the only method available.


Edit:

To remove the False from the array and return the 3rd non duplicate in the array we can wrap it in another TEXTJOIN:

=--TRIM(MID(TEXTJOIN(REPT(" ",999),TRUE,IF(ISERROR(FIND(TRIM(MID(TEXTJOIN(REPT(" ",999),TRUE,A:A),(ROW(INDIRECT("1:" & COUNTA(A:A)))-1)*999+1,999)),MID(TEXTJOIN(REPT(" ",999),TRUE,A:A),1,(ROW(INDIRECT("1:" & COUNTA(A:A)))-1)*999))),--TRIM(MID(TEXTJOIN(REPT(" ",999),TRUE,A:A),(ROW(INDIRECT("1:" & COUNTA(A:A)))-1)*999+1,999)),"")),(3-1)*999,999))

The 3 in the (3-1)*999 can be replaced by anything that returns the Index number desired.

Still an array formula that needs Ctrl-Shift-Enter.

If you want to return the relative position in then use this array:

=AGGREGATE(15,6,ROW(INDIRECT("1:" & COUNTA(A:A)))/(--TRIM(MID(TEXTJOIN(REPT(" ",999),TRUE,IF(ISERROR(FIND(TRIM(MID(TEXTJOIN(REPT(" ",999),TRUE,A:A),(ROW(INDIRECT("1:" & COUNTA(A:A)))-1)*999+1,999)),MID(TEXTJOIN(REPT(" ",999),TRUE,A:A),1,(ROW(INDIRECT("1:" & COUNTA(A:A)))-1)*999))),--TRIM(MID(TEXTJOIN(REPT(" ",999),TRUE,A:A),(ROW(INDIRECT("1:" & COUNTA(A:A)))-1)*999+1,999)),"")),(ROW(INDIRECT("1:" & COUNTA(A:A)))-1)*999,999))=B1),1)


I gave you the long formula for creating the array but for the sum:

=SUMPRODUCT(A1:A5/COUNTIF(A1:A5,A1:A5)) 

would be sufficient.

And for the Average:

=SUMPRODUCT(A1:A5/COUNTIF(A1:A5,A1:A5))/SUMPRODUCT(1/COUNTIF(A1:A5,A1:A5))

There are many work arounds that are shorter and better if you know what you want.

Convert array formula to a regular formula, Excel Array Formula to Extract a List of Unique Values from a Column PivotTable, or you can write some VBA code to automatically refresh it. The reason I am asking this is because array formula are just causing problems with how I wanted to use them for my sheet. I wanted to be able to export my data (some 20,000 lines of data, varying length) into a tab, and have the formula do everything. Array formula work, but they take some tweaking to make it work.

Excel Extract a Unique List • My Online Training Hub, How to extract unique values through Excel formula? To count the unique values from a list, we use the IF function along with the COUNTIF function in. A3 shall be choosen from column A4, which is array of 5, 10, 15, 20, and so on. Excel Errors · Excel Formulas and Functions List · Excel General · Excel Macros and VBA  The Excel MATCH function search a value in an array and returns the position of that item.The syntax of the MATCH function is as below:= MATCH (lookup_value, lookup_array, [match_type])… Excel IF function The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE.

Remove Duplicates in List through Formula, 4 Methods to Extract Unique Values. Advanced Filter; Index- Match Array Formula; Excel Macro (VBA); Remove Duplicates. The above methods are explained in  At the moment I am getting what the array formula is supposed to do; provide a list of all unique category types. But say I wanted to see what categories appeared between 2 dates? I tried adding a simple IF statement into the array formula just to select one date to see if I could get any matches to return.

3 Ways to extract unique values from a range in Excel, Here is a solution that uses neither INDEX() nor SMALL() nor array formulas. With some data in A1 through B20: enter image description here. In C1 enter: An array formula is a formula that works with an array, or series, of data values rather than a single data value. There are two flavors of array formulas: first, there are those formulas that work with an array or series of data and aggregate it, typically using SUM , AVERAGE , or COUNT , to return a single value to a single cell.

Comments
  • so you want 0.1,0.2,0.7,0.3 as your output in one cell?
  • write a udf with a static dictionary object.
  • @ScottCraner the array {0.1,0.2,0.7,0.3}, yes. But only as an intermediate step (how could a single cell display an array!), I can then manipulate the array with other formulae in the same cell. SUM() for example, or MATCH()
  • @Jeeped For this particular use (with portability/accessibility in mind) I don't want to use VBA. I was also not aware that UDFs can return an entire array that Excel recognises (like with =SUM({myUDF()})) but that's another question altogether
  • And a UDF can return an array to a formula.
  • Nice one! - shorter than mine but a little less robust - as it stands mylist must start at row 1 whereas mine will accommodate any vertical range
  • true will edit - sloppy - it should check the first row. Done!
  • Excellent! I like the use of MODE.MULT - I've never used that function in this context
  • Thanks. I quite like it as a quick way of reducing/filtering an array in Excel formulas in conjunction with the IF formula...
  • Maybe ROW(INDEX(myList, 1, 1)) could become MIN(ROW(myList)) for simplicity?