1

Suppose I have the following Google Spreadsheet:

id1 mkdmc   cdjnc
id2 cn39    cncn
id3 cndc    cncn
id1 3mc     cm

And I want to transform it into:

id1 mkdmc   cdjnc
id2 cn39    cncn
id3 cndc    cncn

So to remove the row that has a duplicated id1 in column A. Note that the row isn't fully duplicated, just the value in column A.

How would I go about doing this with a formula?

migrated from superuser.com Apr 28 '17 at 11:21

This question came from our site for computer enthusiasts and power users.

1
={unique(A:A), arrayformula(vlookup(unique(A:A), A:B, 2, false))}

Explanation:

  • A table of 2 columns is constructed in a single formula (expanding to 3 columns is left as an exercise to the reader)
  • The first column is the list of unique IDs
  • The second column looks up the 1st value by that id in the second column of source
1

This could be simplified by using curly brackets {} within the vlookup to indicate which columns we would like to return. For example - 2 columns:

=arrayformula(vlookup(unique(A:A), A:B, {1,2}, false)

3 columns:

=arrayformula(vlookup(unique(A:A), A:C, {1,2,3}, false)

This can then be easily expanded for any number of columns and the user can choose which columns they need eg: {1,2,5,7,9,10}.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy