Google Sheets – How to Union Two Lists of Key-Value Pairs

google sheets

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.

Best Answer

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.

Related Solutions

Google Sheets – Parse Out Column with Commas into Separate Rows

I think the easiest way to do this right in Google Spreadsheet is to write a program: a custom function. Once a custom function is entered in Script editor (under Tools) it becomes available within the spreadsheet: thus, calling =columnSplit(A1:B3, 2, ",") on your array will return the desired output.

The custom function is briefly documented using jsDoc, this will be shown if you start typing its name in a cell. The first argument is the range to split, the second is the number of column (1,2,...) which is to be split, the last one is the delimiter. The function trims the output cells to eliminate stray spaces around commas.

/** 
 * Splits the array by commas in the column with given index, by given delimiter
 * @param {A2:B20}  reference Range reference
 * @param {2}  index Column index
 * @param {","}  delimiter Character by which to split
 * @customfunction
 */
function columnSplit(reference, index, delimiter) {
  var input = reference;
  var output = [];
  if (input.constructor !== Array) {
    input = [[input]];                      // support for single-cell range
  }
  for (var i = 0; i < input.length; i++) {
    var parts = input[i][index - 1].toString().split(delimiter);
    for (var j = 0; j < parts.length; j++) {
      var copy = input[i].slice(0);         // slice(0) to get a fresh copy of the row
      copy[index - 1] = parts[j].trim();    // trimming is optional
      output.push(copy);
    }
  }
  return output
}

Google Sheets – Sort Column While Keeping Associated Values

If the Team and Rank columns are B and C, then you need the formula

=sort(B2:C, 2, False)

which sorts columns B and C according to the second one (C), in descending order (False). Note that the output of sort cannot overwrite the data in place. It has to be placed elsewhere, e.g., on a another sheet named "sorted".

Best Answer

Related Solutions

Google Sheets – Parse Out Column with Commas into Separate Rows

Google Sheets – Sort Column While Keeping Associated Values

Related Topic