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?

2 Answers 2

2

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}.

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

Your Answer

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