Needed: An Array Formula that returns distinct items from a list

[pdx2dca]

I need an array function that works much like the column grouping function of a pivot table.


For example: Referencing the list of items below...
[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]Mary[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[/TR]
[TR]
[TD]John[/TD]
[/TR]
[TR]
[TD]John[/TD]
[/TR]
[TR]
[TD]John[/TD]
[/TR]
</tbody>[/TABLE]



...is there an array formula that would return the three distinct items ("Bob", "Mary", "John") from the list? The resulting array would be three cells long, one cell for each distinct item (example below)
[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]Mary[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[/TR]
[TR]
[TD]John[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Let A2:A6 house the data.

In D1 control+shift+ente, not just enter:

=SUM(IF(FREQUENCY(IF(1-($A$2:$A$6=""),MATCH($A$2:$A$6,$A$2:$A$6,0)),ROW($A$2:$A$6)-ROW($A$2)+1),1))

In D2 control+shift+enter, not just enter, and copy down:

=IF(ROWS($D$2:D2)>$D$1,"",INDEX($A$2:$A$6,SMALL(IF(FREQUENCY(IF(1-($A$2:$A$6=""),MATCH($A$2:$A$6,$A$2:$A$6,0)),ROW($A$2:$A$6)-ROW($A$2)+1),ROW($A$2:$A$6)-ROW($A$2)+1),ROWS($D$2:D2))))

 

[pdx2dca]

That does it. Thanks much.

I looked into using FREQUENCY, but couldn't figure out how to make it work with text. Plus, I misunderstood what the 'bins_array' entry was meant to do.

Thanks again.

 

[Aladin Akyurek]

That does it. Thanks much.

I looked into using FREQUENCY, but couldn't figure out how to make it work with text. Plus, I misunderstood what the 'bins_array' entry was meant to do.

Thanks again.

You are welcome. Thanks for the update.