How to remove duplicate rows based on a column (leaving first one out) in Google Spreadsheets

Question

I am trying to remove duplicates from my Google spreadsheet file.

Here is an initial table example

     A       B   C   D
HelloWorld1  2   6  41
HelloWorld1  4   3   5
HelloWorld2  6   46  6
HelloWorld2  2   4   4
HelloWorld2  5   6   2
HelloWorld3  9   3   5

It should remove all duplicates but leave the fist entry as follows:

     A       B   C   D
HelloWorld1  2   6  41
HelloWorld2  6   46  6
HelloWorld3  9   3   5

I have tried different formulas and techniques, however I haven't got them working. Sorting is not suitable, because I need to leave the same order of rows.

I have tried the following formula for removing, based on the first column. However, I got An array value could not be found

 =ArrayFormula(vlookup(UNIQUE(ARRAY_CONSTRAIN(A:A,COUNT(A:A),1)),A:D,column(A:D),1))

which would be he correct formula? It would be great if this could be done in a single formula or query.

Answer 1

Try this instead:

=ArrayFormula(IFERROR(VLOOKUP(UNIQUE(A:A),A:D,{1,2,3,4},FALSE)))

Answer 2

Today Google announced a built-in feature to remove duplicates.

From Three new features to format and work with data in Sheets

What’s changing

We’re introducing three new features that will help you clean up and work with your data in Sheets. These features are:

• Remove duplicates
• Trim whitespace
• Compatible spreadsheet shortcuts

The move duplicates function is on the Data menu.

NOTE: I could take few days to get this feature.