How to fill a range of Sheet A with data from Sheet B by repeating this action for each value

Hi, I have two sheets A & B. Sheet A has a table where A1:H1 is the header. The range C2:C9 of this table is blank but there is data in the ranges A2:B9 and D2:H9. Sheet B has data in the range A...

excel

office 365

SergeiBaklan

Feb 10, 2025

As variant

function main(workbook: ExcelScript.Workbook) {

    const data = workbook.getWorksheet("SheetB")
        .getUsedRange().getValues().slice(1)
    const range = workbook.getWorksheet("SheetA")
        .getRange("A2:H10")

    let i = 0
    for (let item of data) {
        let next = range.getOffsetRange(9 * i, 0)
        i > 0 ? next.copyFrom(range) : ''
        next.getColumn(2).setValues(item[0])
        i += 1
    }
}

m_tarler

Bronze Contributor

Feb 06, 2025

Still want more info. For example in the image you copy Data1 and Data2 9x each but how do you decide that? Is that a fix number (i.e. always 9)? Is that based on the values in column A repeating the cycle and if so will the same value always be first (e.g. "ORA_AR_ACCOUNTS_RECEIVABLE_MANAGER_JOB" is always 1st) or does it dynamically have to determine the repeat? Will the repeat count always be the same at least in a given data set?

A relatively simple way to create a list with N duplicates is

=TOCOL( CHOOSECOLS( [array], SEQUENCE( , N, 1, 0) ) )

you may want to further TAKE only a portion of [array] which may include DROP alreay used values and TAKE a length = ROUNDUP( [target array] / N ) or something like that

As for:

" and deleted the cell value of Sheet B that I have previously copied/pasted"

that is not possible using a formula (you would need to use a Macro or Script to do something like that) or Manually copy the "calculated" values and paste values and then delete the values on the other list. OR just keep an ongoing list and never delete, just let the formula keep going up.

Hopefully that gives you ideas on how to do it or at least on what and type of additional info that would be useful to help.

Dan1ExcelUser
Copper Contributor
Feb 12, 2025
Thanks for your formula but it doesn't work or I don't see how to use it.

Forum Discussion

How to fill a range of Sheet A with data from Sheet B by repeating this action for each value

Resources