Excel formula to fill column B with unique values from column A

You know there is a built in function in excel that can do that w/o a formula.
just copy column A, paste it into col B, use remove duplicates under the data tab at the top, when it asks you if you want to expand the selection click continue with the current selection, then hit remove duplicates.

This formula will work for you but you need to have the data begin on row 2, not row 1. You'd paste it into cell B2 and drag down until you get blanks...
=IFERROR(INDEX($A$2:$A$1000,MATCH(0,INDEX(COUNTIF($B$1:B1,$A$2:$A$1000&""),),0))&"","")

If you had MS365, this would suffice:

=UNIQUE(A:A)

Cell B1 non array formula , Drag Down

HTML Code: 

array formula , Drag Down

HTML Code: 

Thanks Sam.

Your formula works. But, let's say if there's a value "led" in Col A, then this "led" should not appear in Col B.

Is it possible to fill Col B by extracting unique values from Col A based on multiple conditions?

For instance...

first condition, "led" in Col A to be ignored
second condition, no X in Col C
third condition, Y in Col D

Pl refer to the updated question excel.

Ali, Would Unique formula supoort multiple conditions as mentioned above?

Thanks

Sam

Sam - this is a MASSIVE shift of the goalposts!!!

Before anyone answers you, have you got any more requirements? If so, state them all NOW - do not drip feed them into the thread, as that is not fair on your helpers.

Ali, Would Unique formula supoort multiple conditions as mentioned above?

You don't have MS365, so you can't use it, but if you did, you'd combine it with FILTER.

Thanks - you only need to state the new requirements once. I have removed your duplicate post.

Maybe this:

=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$2:$A$15)/(($A$2:$A$15<>"led")*($C$2:$C$15<>"x")*($D$2:$D$15="Y")),ROWS(F$2:F2))),"")

in F2, copied down:

For column H try:

Formula:

Please Login or Register  to view this content.

For column L try:

Formula:

Please Login or Register  to view this content.

Note that both of the above are array formulas which are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
Note that the first formula will return a "d", however the second instance of "d" doesn't correspond to an "x" in column C.
Let us know if you have any questions.

You're Welcome and thank you for the feedback. I hope that the family medical issues had a good outcome.
Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post.
I hope that you have a blessed day.