remove duplicates and also left out blank rows formula

[pepablock]

Hello,

Is there a way to make this formula a bit more efficient? I'm trying to remove duplicates and remove blanks, but not sure how to do it. I have this basic formula which removes duplicates, but it leaves blank spaces, I think there's a way using IFFERROR, INDEX and ROW.

Here is a simple formula for removing duplicates which as shown on screen leaves blank spaces. Data are in the column A, formula below is in the column B.

=IF(COUNTIF(A$1:A1,A1)=1,A1,"")

I would like to achieve something like this:

 

[Joe4]

Welcome to the Board!

How about just using Excel's built-in "Remove Duplicates" functionality?
It will comletely remove them, and will not leave any blank spaces behind where the duplicates were.

 

[pepablock]

Welcome to the Board!

How about just using Excel's built-in "Remove Duplicates" functionality?
It will comletely remove them, and will not leave any blank spaces behind where the duplicates were.

Thanks .

Yea, I know there is a built-in remove duplicates function, Im trying to basically replicate this with a formula, so I can somewhat automate my excel sheet. That is why Im looking for a formula solution.

 

[pepablock]

I think I found what I need in this >>thread<<. But I dont need them to be sorted and also I need to start from row A1. Anyone know how to simplify the formula? Im not that skilled with these advanced formulas. Thanks