Google Sheets Pivot Table Merge Empty by ID

Question

So I know that empty cells are the worst for generating pivot tables however I have a huge csv that is generated like this:

ID	QTY	ITEM	DATE
800170	1	Donut	5/21/2022
800170	1	Bun
800170	1	Cake
800169	1	Sandwich	5/20/2022
800169	1	Cake
800169	2	Donut
800168	1	Donut	5/21/2022
800168	1	Cookie
800168	1	Tea
800167	1	Donut	5/22/2022
800167	1	Tea

and this is the pivot table that gets generated from it.

I am wondering if there is a way to have the dates "merged" by ID as an ID will always have the same Date?

Desired Output:

Here is a link to my test google sheet: https://docs.google.com/spreadsheets/d/1Loe3dCe4jqj14ZD7alYkb0IhkysOpArk5ZORtIahjdk/edit?usp=sharing

Answer 1

Unfortunately, the Pivot table has no function that will merge the data based on the ID. What you can do is to populate the date column of your raw data.

Here I created a script that will populate the data based on the previous value.

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom Menu')
      .addItem('Fill Dates', 'fillDates').addToUi()
}

function fillDates(){
  var sh = SpreadsheetApp.getActiveSpreadsheet();
  var ss = sh.getSheetByName("Sheet1");//Change this to your sheet name
  var dLastRow = ss.getRange("D"+(ss.getLastRow()+1)).getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
  var startRow = 2;
  var dateCol = 4
  var range = ss.getRange(startRow, dateCol, dLastRow, 1);
  var data = range.getValues();
  var temp = '';
  data.forEach(date =>{
    if(date[0] != ''){
      temp = date[0];
    }else{
      date[0] = temp
    }
  })
  range.setValues(data)
}

To go to Apps Script, select Extensions > Apps Script. Copy paste the code above, save the script and refresh your spreadsheet. The script will create a custom menu in your spreadsheet and you can click that to run the script.

Demo:

Output:

Let me know if you have any issues or questions.

References:

• Extending Google Sheets
• Apps Script Spreadsheet Service

Answer 2

try:

=ARRAYFORMULA(QUERY({A2:C, VLOOKUP(ROW(D2:D), IF(D2:D<>"", {ROW(D2:D), D2:D}), 2)}, 
 "select Col1,sum(Col2) where Col2>0 group by Col1 pivot Col4"))

---

or if you want totals:

=ARRAYFORMULA({QUERY({A:C, VLOOKUP(ROW(D:D), IF(D:D<>"", {ROW(D:D), D:D}), 2)}, 
 "select Col1,sum(Col2) where Col2>0 group by Col1 pivot Col4"), 
 QUERY({A:B}, "select sum(Col2) where Col2>0 group by Col1 label sum(Col2)'Grand Total'"); 
 {"Grand Total", TRANSPOSE(MMULT(TRANSPOSE(QUERY(QUERY({A:C, 
 VLOOKUP(ROW(D:D), IF(D:D<>"", {ROW(D:D), D:D}), 2)}, 
 "select sum(Col2) where Col2>0 group by Col1 pivot Col4"), "offset 1", )*1),
 SEQUENCE(COUNTUNIQUE(A2:A), 1, 1,  ))), SUM(B:B)}})

---

or if you really love pivot table design:

demo sheet