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