1

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.

1
  • Please give a read to the How to Ask page. It is also helpful when you include a link to a sample sheet. Nov 15, 2018 at 12:36

2 Answers 2

0

Try this instead:

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

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.