4

In a spreadsheet I have two lists (key-value pairs basically) that look like this:

[A]  [B]     [D] [E]
 a   23       b  hello
 b   42       d  world
 c   11       f  good
 f   17       g  bye
 h   88

And I would like to get something that looks like this:

a  23    -
b  42    hello
c  11    -
d  -     world
f  17    good
g  -     bye
h  88    -

Is there a way to achieve this inside Google Sheets? I could download the csv file and write a program for it, but looking around on this site it seems that Google Sheets is powerful and a lot of these things can be done without leaving it.

4

A quick way to do it is indeed to use vlookup. For example: in F1 I'd put

=sort(unique({A1:A; D1:D}))

which collects the data from two columns, eliminates repetition, and sorts.

Then in G1 put

=arrayformula(iferror(vlookup(F1:F, A$1:B, 2, False)))

which takes the matching values from B column. Similarly, for H1 use

=arrayformula(iferror(vlookup(F1:F, D$1:E, 2, False)))

If your spreadsheet has a lot of empty rows at the bottom, these array formulas are slightly inefficient in that they look up a lot of empty values. In this case, replacing F1:F with filter(F1:F, len(F1:F)) helps: this filters down to nonempty values in F.


Technical remarks: iferror(...) suppresses error messages that arise when nothing is found by vlookup. arrayformula is used to avoid having to drag the formula down the column: it processes the entire column at once, and returns an array of results.

| improve this answer | |
  • Beautiful! This works. (One catch in my spreadsheet, which I just realized, is that the "key" columns A and D actually have a text with a hyperlink, as I pasted them from somewhere. So the F column gets "duplicates", where the text is the same but the hyperlinks are different. Somehow the G and H columns still show the right results each of the two times (i.e. vlookup takes only the text and ignores the hyperlink I guess). So I need to either strip the hyperlink and keep just the text for each of the rows of A and D, or change the formula for F to somehow consider only the text. Any ideas?) – ShreevatsaR Feb 5 '16 at 1:29
  • To remove hyperlinks manually is easy: select a range, right-click and choose "Unlink". I'll look into the formula approach. – user79865 Feb 5 '16 at 1:38
  • 1
    Joining with an empty string works: =sort(unique({""&A1:A; ""&D1:D})) – user79865 Feb 5 '16 at 1:44
  • Thank you! Somehow even after selecting "unlink" for columns A and D (which indeed seemed to work), and clearing out column F and re-entering the expression, still F had links in it (and therefore duplicates). But the joining with an empty string worked perfectly. Thanks again. – ShreevatsaR Feb 5 '16 at 1:48

Your Answer

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

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