Search code examples
google-sheetslambdasplittransposeflatten

Google Sheets: Repeat "Product SKUs" for 5 rows per SKU in another Sheet

Basically I need to get SKUs in one sheet formatted like this:

Column A
SKU 1
SKU 2
SKU 3

To become formatted like this in another sheet:

Column A
SKU 1
SKU 1
SKU 1
SKU 1
SKU 1
SKU 2
SKU 2
SKU 2
SKU 2
SKU 2
SKU 3
SKU 3
SKU 3
SKU 3
SKU 3

Ad nauseum so that it will pick up all new SKUS to do the same

I've only gotten so far as to do this: =ArrayFormula(TRANSPOSE(SPLIT(REPT(CONCATENATE('US Price List'!$B5&"~"),5),"~")))

But when I try this: =ArrayFormula(TRANSPOSE(SPLIT(REPT(CONCATENATE('US Price List'!$B$5:B&"~"),5),"~")))

I only get a repeat of the full list 5 times instead of the result I want wherein the repeated SKUs stay together. I've also looked into FLATTEN and I can't figure out how to get this to continue down the sheet cleanly. Not sure how I can do this with LAMBDA either.

Solution

  • You may try:

    =tocol(map(tocol('US Price List'!B5:B,1),lambda(Σ,wraprows(Σ,5,Σ))))