4

Say I have two tables. Table A (material requirements):

product_id | material | required
1          | A        | 0.5
1          | B        | 0.7
2          | A        | 0.2
3          | A        | 0.12

And Table B (orders):

order_id | product_id | quantity
1        | 1          | 100
2        | 2          | 10

I want to do a LEFT JOIN* on these two tables (table B being outer), such that the result would be:

order_id | product_id | quantity | material | required
1        | 1          | 100      | A        | 0.5
1        | 1          | 100      | B        | 0.7
2        | 2          | 10       | A        | 0.2

How do I do this? Of course, the end goal is to multiply the required with the quantity

Ideally, there should be an additional table with just basic product information, e.g. something like:

product_id | name | description

*It should be LEFT JOIN, NOT INNER JOIN simply because all orders should be shown, even if the info in product material description is not yet input. An order cannot be 'forgotten' because inner join is used.

1

Let's say your Table A were in A1:C, and your Table B were in E1:G. The following array formula would generate the table you indicate (placed anywhere other than Columns A:G):

=ArrayFormula(QUERY({FILTER(A2:C,ISNUMBER(FIND("\"&A2:A&"\","\"&TEXTJOIN("\",TRUE,E$2:E)&"\"))),VLOOKUP(FILTER(A2:A,ISNUMBER(FIND("\"&A2:A&"\","\"&TEXTJOIN("\",TRUE,E$2:$E)&"\"))),QUERY({E$2:G},"Select Col2, Col1, Col3 Where Col1 is Not Null"),{2,3},FALSE)}, "Select Col4, Col1, Col5, Col2, Col3, Col5*Col3 Where Col1 Is Not Null Label Col4 'order_id', Col1 'product_id', Col5 'quantity', Col2 'material', Col3 'required', Col5*Col3 'tot_material'"))

If it works as you expect, and you want to understand it, check back in here. But I do want to make sure it is what you want before diving into explaining something this complex. (If it works and you're happy to just use it without explanation, also fine.)

| improve this answer | |
1

Recently I create a script to do LeftJoin on tables, my script resolve your case. The script join up to 3 right tables, but can be easyli increased to more. So enjoy.

Supose you have all in A1:C5 and E1:G5 data in one sheet. All you need is only put a line like that:

=LEFTJOIN("A1:C5";"E1:G5"; "A=F";0)

We can see a working example here

I used code from Mogsdad@stackoverflow, so thanks him to.


/**
 * Convert a cell reference from A1Notation to 0-based indices (for arrays) 
 * or 1-based indices (for Spreadsheet Service methods).
 *
 * @param {String}    cellA1   Cell reference to be converted.
 * @param {Number}    index    (optional, default 0) Indicate 0 or 1 indexing
 *
 * @return {object}            {row,col}, both 0-based array indices.
 *
 * @throws                     Error if invalid parameter
 *
 * @author                     Mogsdad at stackoverflow
 */
function cellA1ToIndex( cellA1, index ) {
  // Ensure index is (default) 0 or 1, no other values accepted.
  index = index || 0;
  index = (index == 0) ? 0 : 1;

// Use regex match to find column & row references. // Must start with letters, end with numbers. // This regex still allows induhviduals to provide illegal strings like "AB.#%123" var match = cellA1.match(/(^[A-Z]+)|([0-9]+$)/gm);

if (match.length != 2) throw new Error( "Invalid cell reference" );

var colA1 = match[0]; var rowA1 = match1;

return { row: rowA1ToIndex( rowA1, index ), col: colA1ToIndex( colA1, index ) }; }

/** * Return a 0-based array index corresponding to a spreadsheet column * label, as in A1 notation. * * @param {String} colA1 Column label to be converted. * * @return {Number} 0-based array index. * @param {Number} index (optional, default 0) Indicate 0 or 1 indexing * * @throws Error if invalid parameter * * @author Mogsdad at stackoverflow */ function colA1ToIndex( colA1, index ) { if (typeof colA1 !== 'string' || colA1.length > 2) throw new Error( "Expected column label." );

// Ensure index is (default) 0 or 1, no other values accepted. index = index || 0; index = (index == 0) ? 0 : 1;

var A = "A".charCodeAt(0);

var number = colA1.charCodeAt(colA1.length-1) - A; if (colA1.length == 2) { number += 26 * (colA1.charCodeAt(0) - A + 1); } return number + index; }

/** * Return a 0-based array index corresponding to a spreadsheet row * number, as in A1 notation. Almost pointless, really, but maintains * symmetry with colA1ToIndex(). * * @param {Number} rowA1 Row number to be converted. * @param {Number} index (optional, default 0) Indicate 0 or 1 indexing * * @return {Number} 0-based array index. * * @author Mogsdad at stackoverflow */ function rowA1ToIndex( rowA1, index ) { // Ensure index is (default) 0 or 1, no other values accepted. index = index || 0; index = (index == 0) ? 0 : 1;

return rowA1 - 1 + index; }

/** * Split a String with Sheet Range A1Notation into Object with Sheet and Range * * @param {String} range Sheet string range in format A1 (ex: Sheet1!A1:C3) * * @return {object} {sheet,range}, both 0-based array indices. * * @author SauloAlessandre at stackoverflow */ function SSA1_split_sheet_range(range) { var sheet_range = range.split('!'); var sheet_name; var range_name; if (sheet_range.length == 2) { sheet_name = sheet_range[0]; range_name = sheet_range1; } else { sheet_name = SpreadsheetApp.getActiveSpreadsheet().getSheetName(); range_name = sheet_range[0]; } return {sheet: sheet_name, range: range_name}; }

/** * @param {String} range Sheet string range in format A1 (ex: Sheet1!A1:C3) * * @return {Sheet object} for the range passed in param * * @author SauloAlessandre at stackoverflow */ function SSA1_range(range) { var sheet_range = SSA1_split_sheet_range(range); var sheet = SpreadsheetApp.getActive().getSheetByName(sheet_range.sheet); return sheet.getRange(sheet_range.range);
}

function RowsLeftJoin_(left_range, right_ranges, joins, tables, right_ordered, skip_header) { right_ordered = right_ordered || 0; skip_header = skip_header || 0; debug = function(row) { // Logger.log(row); } var add_right_columns = function(columns, column, row) { for (var i = 0; i < columns.length; i++) { if (i != column) row.push(columns[i]); } } var discover_column = function(column, column_range) { var sheet_range = SSA1_split_sheet_range(column_range); var range = sheet_range.range.split(":"); if (range.length != 2) throw new Error("Error RANGE should be ex: A1:B, not " + column_range); var c1 = cellA1ToIndex(range[0]).col; var c2; var col = colA1ToIndex(column); var match = range1.match(/(^[A-Z]+)|([0-9]+$)/gm); if (match.length != 2) c2 = colA1ToIndex(range1); else c2 = cellA1ToIndex(range1).col; if (col < c1 || col > c2) throw new Error("Error JOIN column [" + column + "] is out of range [" + column_range + "]"); return col-c1; } var join_right = function(left_range, right_range, right_max, join, search, row) { var right_values = SSA1_range(right_range).getValues(); var compare = join.split("="); if (compare.length != 2) throw new Error("Error JOIN should be ex: A=B, not " + join); var column_left = discover_column(compare[0], left_range); var column_right = discover_column(compare1, right_range); var start = 0; var end = right_max - 1; debug("column_left " + column_left + " column_right " + column_right); if (right_ordered) { while (start <= end){ var mid = Math.floor((start + end)/2); debug("join_right [" + right_values[mid][column_right] + "] === [" + search[column_left] + "]"); if (right_values[mid][column_right] === search[column_left]) { debug("join_right found " + search[column_left]); add_right_columns(right_values[mid], column_right, row); return; } else if (right_values[mid][column_right] < search[column_left]) { start = mid + 1; } else { end = mid - 1; } } } else { for (; start <= end; start++) { if (right_values[start][column_right] === search[column_left]) { debug("join_right found " + search[column_left]); add_right_columns(right_values[start], column_right, row); return; } } } debug("join_right not found " + search[column_left]); } var count_if_not_empty = function(rows) { var total = rows.length; for (var i = rows.length-1; i >= 0; i--) { if (!rows[i][0]) total--; } return total; } var range = []; var left_values = SSA1_range(left_range).getValues(); var rows_range = left_values.length; for (var i = 0; i < rows_range; i++) { var cols_range = left_values[i].length; var row = []; for (var j = 0; j < cols_range; j++) { if (left_values[i][j]) row.push(left_values[i][j]); } for (t = 0; t < tables; t++ ) { var right_range = right_ranges[t]; var right_values = SSA1_range(right_range).getValues(); var right_max = count_if_not_empty(right_values); var join = joins[t]; if (row.length > 0) { join_right(left_range, right_range, right_max, join, left_values[i], row); debug(row); } } if (row.length > 0) range.push(row); } return range; }

/** * Return a LEFT JOINed range between 2 tables, including all data for the left table * * @param {Range_String} left_range left range to join ex: "A1:B15" (all fields go on) * @param {Range_String} right_range right range to join "C1:D15" (only equal values are visible) * @param {String} join equal comparator between left and right (ex: A=C) * @param {bool} sorted indicates when the right range is sorted (good for big tables) * * @return {Range}
* * @author SauloAlessandre at stackoverflow */ function LeftJoin(left_range, right_range, join, right_ordered, skip_header) { return LeftJoinR1(left_range, right_range, join, right_ordered, skip_header); }

/** * Return a LEFT JOINed range between 2 tables (same LeftJoni) * * @param {Range_String} left_range left range to join ex: "A1:B15" (all fields go on) * @param {Range_String} right_range right range to join "C1:D15" (only equal values are visible) * @param {String} join equal comparator between left and right (ex: A=C) * @param {bool} sorted indicates when the right range is sorted (good for big tables) * * @return {Range}
* * @author SauloAlessandre at stackoverflow */ function LeftJoinR1(left_range, right_range1, join1, right_ordered, skip_header) { var joins = []; var right_ranges = []; joins.push(join1); right_ranges.push(right_range1); rows = RowsLeftJoin_(left_range, right_ranges, joins, 1, right_ordered, skip_header); return rows; }

/** * Return a LEFT JOINed range between 3 tables, including all data for the left table * * @param {Range_String} left_range left range to join ex: "A1:B15" (all fields go on) * @param {Range_String} right_range1 right range to join "C1:D15" (only equal values are visible) * @param {String} join1 equal comparator between left and right 1 (ex: A=C) * @param {Range_String} right_range2 right range to join "C1:D15" (only equal values are visible) * @param {String} join1 equal comparator between left and right 2 (ex: A=C) * @param {bool} sorted indicates when the right range is sorted (good for big tables) * @param {bool} skip_header ignore header * * @return {Range}
* * @author SauloAlessandre at stackoverflow */ function LeftJoinR2(left_range, right_range1, join1, right_range2, join2, right_ordered, skip_header) { var joins = []; var right_ranges = []; joins.push(join1); joins.push(join2); right_ranges.push(right_range1); right_ranges.push(right_range2);
rows = RowsLeftJoin_(left_range, right_ranges, joins, 2, right_ordered, skip_header); return rows; }

/** * Return a LEFT JOINed range between 4 tables * * @param {Range_String} left_range left range to join ex: "A1:B15" (all fields go on) * @param {Range_String} right_range1 right range to join "C1:D15" (only equal values are visible) * @param {String} join1 equal comparator between left and right 1 (ex: A=C) * @param {Range_String} right_range2 right range to join "C1:D15" (only equal values are visible) * @param {String} join2 equal comparator between left and right 2 (ex: A=C) * @param {Range_String} right_range3 right range to join "C1:D15" (only equal values are visible) * @param {String} join3 equal comparator between left and right 3 (ex: A=C) * @param {bool} sorted indicates when the right range is sorted (good for big tables) * @param {bool} skip_header ignore header * * @return {Range}
* * @author SauloAlessandre at stackoverflow */ function LeftJoinR3(left_range, right_range1, join1, right_range2, join2, right_range3, join3, right_ordered, skip_header) { var joins = []; var right_ranges = []; joins.push(join1); joins.push(join2); joins.push(join3); right_ranges.push(right_range1); right_ranges.push(right_range2); right_ranges.push(right_range3); rows = RowsLeftJoin_(left_range, right_ranges, joins, 3, right_ordered, skip_header); return rows; }

| improve this answer | |
0
  • one-cell solution (paste in D1):

={"material", "required", "total";
 ARRAYFORMULA(IFERROR(VLOOKUP(B2:B, Sheet2!A2:C, {2,3}, 0), )),
 ARRAYFORMULA(IF(LEN(A2:A), C2:C*VLOOKUP(B2:B, Sheet2!A2:C, 3, 0), ))}

  • if you want it to export into Sheet3 use:

={{QUERY(Sheet1!A1:C, "select *", 1)},
  {"material", "required", "total";
  ARRAYFORMULA(IFERROR(VLOOKUP(Sheet1!B2:B, Sheet2!A2:C, {2,3}, 0), )),
  ARRAYFORMULA(IF(LEN(Sheet1!A2:A), Sheet1!C2:C*VLOOKUP(Sheet1!B2:B, Sheet2!A2:C, 3, 0), ))}}

enter image description here

| improve this answer | |
-1

  1. if your second table looks like this:

    1

  2. and you want something like this:

    5

  3. then paste this into D2 cell:

    =ARRAYFORMULA(IFERROR(VLOOKUP(B2:B, Sheet2!A2:C, {2,3}, 0), ))

    5

  4. and paste this into F2 cell:

    =ARRAYFORMULA(IF(LEN(A2:A), C2:C*E2:E, ))

    6



  • if you are not american use:
    =ARRAYFORMULA(IFERROR(VLOOKUP(B2:B; Sheet2!A2:C; {2\3}; 0); ))
    =ARRAYFORMULA(IF(LEN(A2:A); C2:C*E2:E; ))

| improve this answer | |

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.