Suppress Duplicates in Search List

[pmich]

In Column A from A2 to A20, I have names.
In B1, I type "John".
In B2, I have an array formula entered using Ctrl+Shift+Enter.
I have copied it down from B3 to B20.
It filters names having the word "John".
But, there are duplicates in the displayed List.
I would like to suppress the duplicates.
I can do it in VBA.
But I will appreciate suggestions from any expert to do it without using VBA, as I need to use it directly in an Excel Sheet and not through a programme.
So, kindly suggest me to edit this formula so that it filters the search text and also displays only unique values.
(If there is way to display it in alphabetical order, that will be good. But it is not a must.)
Thanks in advance.

This is the formula in B2:
=IF($B$1="","",IFERROR(INDEX($A$2:$A$20,SMALL(IF(ISNUMBER(SEARCH($B$1,$A$2:$A$20)),ROW($A$2:$A$20)-ROW($A$2)+1),ROWS($A$2:$A2))),""))

This is the source data :

Name
David John
Jonah
Robert
Samuel Johnson
David John
Kingsley
Williams
James Williams
Nelson
John Bright
Anderson
Joseph
Moses
Samson
John
David John
Christopher
John Bright
Paul

This is the search Text : "John"

The displayed result:

David John
Samuel Johnson
David John
John Bright
John
David John
John Bright

This is the expected result:

David John
Samuel Johnson
John Bright
John

 

[mikerickson]

You could enter this array formula into C1 (with Ctrl-Shift-Enter) and then drag down.

=INDEX(B:B, SMALL(IF(MATCH($B$1:$B$20&"",$B$1:$B$20&"",0)=ROW($B$1:$B$20), ROW($B$1:$B$20), 999), ROW(A1)), 1)&""

 

[pmich]

@mikerickson Your formula works perfectly. Thanks a lot. You have saved lot of my time. Very recently I have started using WorkSheet Formulae, and they are great indeed.
As you know, I am also giving replies in this forum to some of the queries, which I can handle. But I am not an expert. Actually, this I do to learn more.
By the way, if it is permissible in this forum, can you explain the formula that you have suggested. I haven't seen 999 in other suggestions of experts that I have read. I am curious to know. If not, can you suggest me where I can learn it. Anyway, if it is objectionable in this forum, please ignore my request.
I have read some of your replies that you have given to others and I have learnt new things in Excel.
Once again thank you so much for the formula.

 

[mikerickson]

This returns an array of numbers
IF(MATCH($B$1:$B$20&"",$B$1:$B$20&"",0)=ROW($B$1:$B$20), ROW($B$1:$B$20), 999)

If the entry in column B is the first instance of that name, it returns the row number, if not, then it returns 999, because presumably B999 is empty.

So when that array is "sorted" with
SMALL(IF(MATCH($B$1:$B$20&"",$B$1:$B$20&"",0)=ROW($B$1:$B$20), ROW($B$1:$B$20), 999), ROW(A1))

The SMALL will put the row numbers that have values before the many 999's
And then the INDEX(B:B, SMALL(.....), 1) will return the column B entries from those row numbers.

Since B999 is empty, INDEX(B:B, 999, 1) would return 0 unless it is coerced into a string with INDEX(B:B, ....) & ""

 

[pmich]

@mikerickson Thank you for the explanation. I will do some samples to become familiar with this. So, if I have say, 8000 Rows of Data in Column A, and say, if 1000 Rows are displayed in Column B as the result of data matching with the search text, then I think I should use 9999 instead of 999. Am I correct? If wrong, kindly advise me.

 

[mikerickson]

That is correct

 

[pmich]

That is correct

@@mikerickson
Thank you very much. I am learning new things.