Hi all,
I have an array function that returns text separated by commas (e.g. "Dave, Tom, Dave, Mike...") and it contains duplicates when there is more than 1 match (i.e. Dave). What functions should I wrap around the array to remove the duplicates?
Thank you!
[TABLE="class: grid, width: 192"]
<colgroup><col width="64" style="width:48pt" span="3"> </colgroup><tbody>[TR]
[TD="width: 64, bgcolor: transparent"] [/TD]
[TD="width: 64, bgcolor: transparent"] [/TD]
[TD="width: 64, bgcolor: transparent, align: right"]
3[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]
Dave[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]
Dave[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]
Tom[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]
Tom[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]
Dave[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]
Mike[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]
Mike[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]
In C1 control+shift+enter, not just enter:
=SUM(IF(FREQUENCY(IF(1-($A$2:$A$5=""),MATCH($A$2:$A$5,$A$2:$A$5,0)),ROW($A$2:$A$5)-ROW($A$2)+1),1))
In C2 control+shift+enter, not just enter, and copy down:
=IF(ROWS($C$2:C2)>$C$1,"",INDEX($A$2:$A$5,SMALL(IF(FREQUENCY(IF(1-($A$2:$A$5=""),MATCH($A$2:$A$5,$A$2:$A$5,0)),ROW($A$2:$A$5)-ROW($A$2)+1),ROW($A$2:$A$5)-ROW($A$2)+1),ROWS($C$2:C2))))
