1

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

Community
  • 1
  • 1
Greedo
  • 4,967
  • 2
  • 30
  • 78
  • so you want `0.1,0.2,0.7,0.3` as your output in one cell? – Scott Craner Nov 14 '17 at 19:51
  • write a udf with a static dictionary object. –  Nov 14 '17 at 20:02
  • @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()` – Greedo Nov 14 '17 at 20:03
  • @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 – Greedo Nov 14 '17 at 20:05
  • That is a big formula, vba is better. – Scott Craner Nov 14 '17 at 20:05
  • 2
    And a UDF can return an array to a formula. – Scott Craner Nov 14 '17 at 20:07
  • Sounds like an [XY problem](http://mywiki.wooledge.org/XyProblem) to me. –  Nov 14 '17 at 20:10
  • I know the question is for an array formula, but a PivotTable is portable, and will return a unique list. – jeffreyweir Nov 14 '17 at 20:49
  • @jeffreyweir Yeah, there's a few ways of filtering with lists and tables and things. But the advantage I see in array formulae is the live updating, in fact it's really the only reason I use them at all. And I don't *believe* you can re-filter when data changes without breaking into a `Worksheet_Change` event (or otherwise using VBA) – Greedo Nov 14 '17 at 21:07
  • Ah. Yeah I use VBA and a Worksheet_Change event to trap any updates to the source data and then refresh the PivotTable. Or I put a big callout saying "Please right click on this thing and choose refresh". – jeffreyweir Nov 14 '17 at 21:27

3 Answers3

4

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

MacroMarc
  • 3,214
  • 2
  • 11
  • 20
3

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

barry houdini
  • 45,615
  • 8
  • 63
  • 81
1

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.

enter image description here

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.

![enter image description here

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)

enter image description here

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.

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • So close, but the internal array contains `False` for duplicates: i.e. returns `{0.1,0.2,False,0.7,0.3}` and not simply `{0.1,0.2,0.7,0.3}`. That's fine for `SUM()`, but something like `MATCH()`, the additional element bumps everything along the queue. I agree there's certainly a case by case shorter version, but it's a problem I've come across before so I wanted a complete general case. Probably not do-able, as I've never seen Excel shrink an array (correct me if I'm wrong) – Greedo Nov 14 '17 at 20:28
  • This is great, the last one you added was what I was *secretly* after, and I'm definitely bookmarking these functions 'cos you have most covered. But we still don't really have the general case, the plain, filtered array. *I'm fascinated by all the little tricks here by the way - `--` for text (I only knew that for boolean conversions)* `TEXTJOIN`, `AGGREGATE` – Greedo Nov 14 '17 at 20:58
  • Or so it seemed... (just flagging you in case you didn't see the new solns.) – Greedo Nov 14 '17 at 21:51