create text string: "NOT "[b2value]", NOT "[b3value]", ..., NOT "[b600value]"

I want to do an advanced search of a database using keywords that produce many results. To make the results more manageable, I want to filter out the results that I have already recorded in my existing worksheet, "Data".

Data!D1 is a header, "FirstLast", with a reference list in D (list is of first and last names). Formula in D2 =CONCATENATE(B2, " ", C2) which I copied down to D969. Entries in D are not all unique (though some are); some names appear several times.

See attachment for illustration. Described further:
Goal1: I want to generate a list of all unique D values (i.e. ignores duplicates) in Column B of a new sheet, "Summary", in the same workbook.
My attempt:
B2 =INDEX($A$2:$A$969, MATCH(0, COUNTIF($B$1:B1,$A$2:$A$969), 0))
But that returns #N/A in B2 after pressing ctrl+shift+enter.

Goal2: I want to generate a single string of text showing all Summary!B values in quotes, each with 'NOT ' preceding it and with a comma directly following the endquote. (I want to copy this text into a database search so that I can filter out results already in my spreadsheet.) Basically, I want a formula that will produce text like this, all in one cell:
NOT "[B2Text]", NOT "[B3Text]", NOT "[EBText]", ... , NOT "[LastBEntryText]"


My guess is it is something like: =CONCATENATE(“NOT “”, B2, “”, ”, “NOT “”, B3, “”, ”, “NOT “”, B4, “”, ”, ...B?[End of my List #])

BUT how do I do this for the whole range so I don’t have to manually type B2-B6235484 in the formula? Also, I think getting quotes to appear is an issue for me

See attachment for illustration

Originally Posted by savekels

Goal1: I want to generate a list of all unique D values (i.e. ignores duplicates) in Column B of a new sheet, "Summary", in the same workbook.
My attempt:
B2 =INDEX($A$2:$A$969, MATCH(0, COUNTIF($B$1:B1,$A$2:$A$969), 0))

Hi. Not sure I understand. Both your above formula and the expected results in your attached appear to be asking for values from either the First or Last column, and not from the FirstLast column (though this is the column you seem to be indicating in your explanation above).

Originally Posted by savekels

[B]Goal2:I want a formula that will produce text like this, all in one cell:
NOT "[B2Text]", NOT "[B3Text]", NOT "[EBText]", ... , NOT "[LastBEntryText]"

Unfortunately there is no array version of CONCATENATE which would allow this process to be shortened. In a nutshell, unless you incorporate some VBA then you will have no alternative but to write an extremely long and unwieldy formula such as you give, and obviously if the number of strings to be concatenated could be quite large then this is simply not feasible.

Regards