There isn't an explicit "copy except blanks" command, but the following approach seems simple enough. For this illustration I assume the original data is in the rows 1-6, while the "scattered" data to be pasted there is in rows 7-12.
Put the following, for example, in A14:
=ARRAYFORMULA(IF(LEN(7:12),7:12,1:6))
This will fill the rows 14-19 with the data combined in the way you described. Then copy these rows and paste them to the top of the spreadsheet with CtrlShiftV (equivalently, right click -> paste special -> values only).
Explanation: the IF command takes values from rows 7:12 when they have positive length (i.e., are nonempty). Otherwise it takes them from rows 1:6.
When you have many sumif
formulas in your spreadsheet, this is usually a sign you need to use query
instead. The script you are currently using to fetch the sheet names can be modified so that it creates a query formula instead:
function queryFormula() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var formula = "=query({";
var sheets = ss.getSheets();
for (var i=1; i<sheets.length; i++) {
formula = formula + "'" + sheets[i].getName() + "'!A5:E;";
}
formula = formula.replace(/;$/, '}, "select Col1, Col2, sum(Col5) group by Col1, Col2")');
ss.getSheetByName("Totales").getRange("H2").setFormula(formula);
}
The result of running this script is that cell H2 gets the formula
=query({'02/26/2016 20:33:08 gcrosta@gmail.com'!A5:E;'02/26/2016 19:23:17 leyla.s.rodriguez@gmail.com'!A5:E;'02/26/2016 17:26:37 gcrosta@gmail.com'!A5:E;'02/26/2016 17:26:26 gcrosta@gmail.com'!A5:E;'02/26/2016 17:26:17 iris2908@fibertel.com.ar'!A5:E;'02/26/2016 17:05:49 iris2908@fibertel.com.ar'!A5:E}, "select Col1, Col2, sum(Col5) group by Col1, Col2")
which does everything else. The first parameter of the query is the array, which consists of the ranges A5:E put together, on top of one another.
The second parameter is a query string, which says to sum column 5 (which in the range A:E is E), grouping by columns 1, 2 (which are A,B).
The output looks like this:
+-------------------------------------------------+------+------+
| ACEITE DE oLIVA Escencias de la Tierra x 500 ml | 2215 | 582 |
| Aceite de Coco God Bless 325 g | 2488 | 900 |
| Aceite de Coco Refinado Napus 660 ml | 2626 | 840 |
| Aceite de Coco Virgen God Bless 225 g | 2479 | 900 |
| Aceite de Coco Virgen God Bless 500 g | 2387 | 1500 |
+-------------------------------------------------+------+------+
Updating the formula
If you rename the script function "queryFormula" into onOpen
, it will run every time you open the spreadsheet, thus ensuring you get the current set of sheets in the formula.
Best Answer
See sheet 'INVOICE' in File.
Since cells B:D are merged, there are 2 formulas in B15 and E15 that return Product name and quantity with price for customer specified in cell B10.
Sheet 'Order Data adjusted' arranged in table form. In case of new products or customers it would be easier to add new data and form invoices, since formula in 'INVOICE adjusted' covers whole range of 'Order Data adjusted'.