How do I combine two rows with a the same identity?

Question

I'm trying to combine 2 rows with the same identity. I've been looking for a solution, but somehow can't find a working solution. I'm trying to make a tracker for my stocks, but I want it to combine the information if I add the same asset. I made a form with which trades can be added to the portfolio. So if there is a duplicate asset, the new duplicate will always be on the last row.

I'm neither an expert in programming or google sheets, but here's the pseudo code:

check if there is a duplicate in column 2 (id of asset), if so:
copy the value in column 1 of the last row to existing row (it is a column for purchase date);
the id of the asset is in column 2, so that can stay the same;
the amount is in column 3, it should add the amount from the last row to the existing row, column 3.

Here is an example sheet: https://docs.google.com/spreadsheets/d/1AEdljHtXUOnRJ1kxbziqKAjYo5EqGZjjnWOx1mbeTI0/edit#gid=0

I tried several things but I got stuck. I have made a code to go through the data, find the duplicate and add it to a list. But after that I just don't know how to go about it.

You're probably going to laugh at my code, but from a certain point it was just like going round in circles.

 function readData() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var formSS = ss.getSheetByName("Portfolio"); var rangeArray = formSS.getRange("B2:B" + formSS.getLastRow()).getValues(); //makes an array of values from column B, but each item is in array in itself. rangeArray = [].concat.apply([],rangeArray);//takes the elements outside their own array; var sortedRangeArray = rangeArray.sort();//sort the items to a new array duplicates=[];//make a list of duplicates to identify for (var i =0; i < 1;sortedRangeArray.length, i++)//iterate through sortedArray if(sortedRangeArray[i+1] === sortedRangeArray[i]){ duplicates.push(sortedRangeArray[i]);//if a duplicate is found, push it to the duplicates list } var str = duplicates[0];//identify the duplicate, there is only one anyway. for (var k = 0; k < sortedRangeArray.length; k++) { var row = sortedRangeArray[k]; if(row[SEARCH_COL_IDX] == str) { var index = rangeArray.findIndex(str);//I thought it might help defining the position }}}```

Can you provide a example Sheet for us to see the actual information? And also can you explain what worked in your code as you hoped for? And where in the process did you encounter an error or give up? — Neven Subotic
– Neven Subotic, Commented Sep 25, 2021 at 15:58
Thanks. I added a link to my little project. Well I hoped to find the duplicates, which I did. But I just gave up because I didn't know what to do with it. I couldn't fine a working solution. I'm open to all ideas :) — Osman P.
– Osman P., Commented Sep 25, 2021 at 18:41

Yuri Khristich · Accepted Answer · 2021-09-26 12:03:24Z

It could be something like this:

function main() { var table = SpreadsheetApp.getActiveSheet().getDataRange().getValues(); var ids = table.map(x => x[1]); // get array of IDs (column B) var row = ids.indexOf(ids.pop()); // find ID from last row in the array if (row == -1) return; // if not found do nothing var value = table[row][2]; // get value of 3rd cell table[row] = table.pop(); // move the last row in current row table[row][2] += value; // add the value to 3rd cell table.push(Array(table[0].length)); // add empty row to the table SpreadsheetApp.getActiveSheet().getDataRange().setValues(table); // put the table back on the sheet }

Update

Since your table contains formulas it needs a slightly another implementation:

function main() { var sheet = SpreadsheetApp.getActiveSheet(); var table = sheet.getDataRange().getValues(); var ids = table.map(x => x[1]); // get array of IDs (column B) var row = ids.indexOf(ids.pop()); // find ID from last row in the array if (row == -1) return; // if not found do nothing var last_row = table.pop(); var date = last_row[0]; var id = last_row[1]; var value = last_row[2] + table[row][2]; sheet.getRange(row+1,1,1,3).setValues([[date,id,value]]); sheet.getRange(sheet.getLastRow(),1,1,8).clearContent(); }

Thanks, that seems to work (also the comments are really appreciated). But when I put formulas in the following columns, like total value etc. it does not work. Why is that?
I just sent you a request to access to your sheet. I need to see what do you mean by 'following columns'.
sorry, accidentally changed the file location, I updated the link in the question. In my original file I made a form in which I can put my trades and it gets automatically copied to the portfolio. Along with the data I put in (purchase date, current price, amount, sl, target) it also copies in formulas for the following columns. Like the current value and risk.
Still 'Access Denied' with the new link. I sent you another request.
shit man, I am so sorry.. forgot to turn on the share options.. it should be working now.. again, I apologise. I use RC to copy the correct formulas btw.

Neven Subotic · Accepted Answer · 2021-09-25 16:10:45Z

Assumming these are your headers [ amount, id, newAmount]

 function run(){ const ACTIVE = SpreadsheetApp.getActive() const allRows = ACTIVE.getDataRange().getValues() const lastRow = allRows.pop() // Id is in column B const recordId = lastRow[1] // Amount is in column A const amount = lastRow[0] // Remove row 1, I assume there are only headers allRows.shift() // Look through every row in Column B for a match allRows.forEach( (row, index) => { if( row[1] == recordId ){ // Create a new amount const newAmount = amount + parseInt(row[0]) ACTIVE.getRange(index+2, 3).setValue(newAmount) } }) }

Collectives™ on Stack Overflow

How do I combine two rows with a the same identity?

2 Answers 2

6 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Related