Array formula to return an ARRAY without duplicate

问题:

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

回答1:

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..

回答2:

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))))))))))

回答3:

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.