how can this formula be modified so that it doesn't require control shift enter

excelismagic
excelismagic used Ask the Experts™
on
=INDEX($A$2:$A$11, SMALL(IF(COUNTIF($B$2:$B$11, $A$2:$A$11), MATCH(ROW($A$2:$A$11),ROW($A$2:$A$11)), ""), ROWS($A$1:A1)))

Open in new window

EE.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You cannot. This is how excel executes excel formulas.
Managing Director/Excel VBA Developer
Distinguished Expert 2018
Commented:
Try below without Array Formula
=IFERROR(INDEX($A$2:$A$11, MATCH(0,INDEX(COUNTIF($D$1:D1, $A$2:$A$11)+(COUNTIF($B$2:$B$11, $A$2:$A$11)<>1),0,0), 0)), "")

Open in new window

In attached, I have above formula in Column D
EE_List-Duplicates.xlsx

Author

Commented:
Wow. Shams thank you.

for a moment, i believed what Saqib mentioned.  this is really cool

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial