proficient
Well-known Member
- Joined
- Apr 10, 2012
- Messages
- 745
- Office Version
- 2016
- Platform
- Windows
- Mobile
Hi all,
I am using this below formula to get unique (numbers/numbers with alphabets) from a list but every time getting a same issue that the last (number/number with alphabet) either reflect twice or once.
=IF(ROWS($W$6:W6)>=$B$4,"",INDEX($B$6:$B$3951,SMALL(IF($B$6:$B$3951<>"",IF(MATCH($B$6:$B$3951,$B$6:$B$3951,0)=ROW($B$6:$B$3951)-ROW($B$6)+1,ROW($B$6:$B$3951)-ROW($B$6)+1)),ROWS($W$6:W6))))
For count unique distinct I am using this formula
=SUMPRODUCT(($D$21:$D$40<>"")/COUNTIF($D$21:$D$40,$D$21:$D$40&""))
I am using this below formula to get unique (numbers/numbers with alphabets) from a list but every time getting a same issue that the last (number/number with alphabet) either reflect twice or once.
=IF(ROWS($W$6:W6)>=$B$4,"",INDEX($B$6:$B$3951,SMALL(IF($B$6:$B$3951<>"",IF(MATCH($B$6:$B$3951,$B$6:$B$3951,0)=ROW($B$6:$B$3951)-ROW($B$6)+1,ROW($B$6:$B$3951)-ROW($B$6)+1)),ROWS($W$6:W6))))
For count unique distinct I am using this formula
=SUMPRODUCT(($D$21:$D$40<>"")/COUNTIF($D$21:$D$40,$D$21:$D$40&""))
