Divide data and copy other cells in google sheets or excel

0 votes

Hello, I need your assistance. In a Google Sheet, I have data that is dispersed across several cells. Cell 1 contains a name, Cell 2 contains many lines of data, and Cell 3 contains a name. I need to divide the data in Cell 2 into different vertical cells by copying the data from Cells 1 and 3 as shown in the example.

required situationenter image description here](https://i.stack.imgur.com/DHu8U.png)

I tried transpose but it failed

Mar 20, 2023 in Others by Kithuzzz
• 38,010 points
241 views

1 answer to this question.

0 votes

Try this :

const Sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(`YourSheetName`)

function dataHandler() {

  const dataRange = Sheet.getDataRange()
                         .offset(1, 0)
  
  const data = dataRange.getValues()

  const newValues = data.flatMap(([ name, location, addresses, phone]) => {
    
    const addressList = addresses.split(`\n`)

    return location.split(`\n`)
                   .map((item, index) => {
      return [
        name,
        (addressList[index]) ? addressList[index] : ``,
        item,
        phone
      ]
    })

  })

  dataRange.clearContent()

  Sheet.getRange(2, 1, newValues.length, newValues[0].length)
       .setValues(newValues)

}
answered Mar 20, 2023 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
1 answer
0 votes
1 answer

What is the better API to Reading Excel sheets in java - JXL or Apache POI

Here are the things where both APIs ...READ MORE

answered Oct 9, 2022 in Others by narikkadan
• 63,420 points
2,675 views
0 votes
1 answer

Convert text to number in Google Sheets & Excel

Try: =INDEX(IF(REGEXMATCH(A1:A4&""; "M"); REGEXEXTRACT(A1:A4; "\d+.\d+|\d+")*1000000; IF(REGEXMATCH(A1:A4&""; "k"); ...READ MORE

answered Oct 15, 2022 in Others by narikkadan
• 63,420 points
967 views
0 votes
1 answer

Thousand and million formatting for negative numbers (excel/ googlesheets)

Its not possible. What you could do is ...READ MORE

answered Sep 25, 2022 in Others by narikkadan
• 63,420 points
905 views
0 votes
1 answer

How to split text values by a delimiter?

The Split function is what you are looking for: =Split(A1, ...READ MORE

answered Oct 11, 2022 in Others by narikkadan
• 63,420 points
419 views
0 votes
1 answer

Currency conversion:number to words excel

Try looking for javascript solutions to use ...READ MORE

answered Oct 28, 2022 in Others by narikkadan
• 63,420 points
323 views
0 votes
1 answer

How to format numbers as lakhs and crores in excel/ google spreadsheet when the number could be negative too?

Excel formatting, in my opinion, can only ...READ MORE

answered Oct 31, 2022 in Others by narikkadan
• 63,420 points
12,352 views
0 votes
1 answer

How do you populate a google sheets/excel column with cells from a column in another sheet in the same document?

You have two options on chronology: sheet-by-sheet =QUERY({Sheet1!X:Z; Sheet2!X:Z; ...READ MORE

answered Dec 19, 2022 in Others by narikkadan
• 63,420 points
1,170 views
0 votes
1 answer

How do I get text in a pivot table in excel or google sheets?

Try: =QUERY(QUERY({Data!A:A, Data!Q:S}, "select Col4,max(Col1) ...READ MORE

answered Feb 6, 2023 in Others by narikkadan
• 63,420 points
891 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP