0

I have a table with three Columns: Column A: name of Item, Column B: Lowest value of series, Column C: the Highest value of series. enter image description here

What I want to achieve is:

  1. Generate series of item sequence from lowest number to highest number per row

So Apple 7 9 will yield: "Apple_7", "Apple_8", "Apple_9"

  1. Concatenate/Join such sequence per row into Column D So

    Item From Until Result
    Apple 7 9 "Apple_7, Apple_8, Apple_9"
    Berry 3 8 "Berry_3, Berry_4, Berry_5, Berry_6, Berry_7, Berry_8"

  2. Doing it all using one Arrayformula, so that new row added can be automatically calculated.

Here is example sheet: https://docs.google.com/spreadsheets/d/1R5raKmmt5-aOIorAZGHjv_-fdySKWjCMB_FRQwm1vag/edit#gid=0

I tried in Column D:

arrayformula(textjoin(", ",true,arrayformula(A3:A&"_"&sequence(1,C3:C-B3:B+1,B3:B,1))))

Apparently, the sequence function only take value from Column B and join it in first row.

Any help will be appreciated.

Ray Daapala
  • 3
  • 3

2 Answers2

0

Try below BYROW() formula (see your file, harun24hr sheet).

=BYROW(A3:INDEX(A3:A,COUNTA(A3:A)),LAMBDA(x,TEXTJOIN(";",1,INDEX(x&"_"& SEQUENCE(INDEX(C:C,ROW(x))-INDEX(B:B,ROW(x))+1,1,INDEX(B:B,ROW(x)))))))

  • Here A3:INDEX(A3:A,COUNTA(A3:A)) will return a array of values as well cell reference from A3 to last non empty cell in column A (Assume you do not have any blank rows inside data). If you have blank row, then you have to use different approach. See this post by @TheMaster

  • Then LAMBDA() will apply TEXTJOIN() and SEQUENCE() function for each cell of B as well as C column.

  • SEQUENCE() will make series from start to end number and by concatenating A column and will generate your desired strings.

  • Finally TEXTJOIN() will join all those strings with delimiter to a single cell.

enter image description here

Harun24hr
  • 30,391
  • 4
  • 21
  • 36
  • 1
    Wow, Amazing.. Just the one I need! Thank you so much. :-D – Ray Daapala Nov 21 '22 at 10:41
  • @RayDaapala If the answer helps then please consider to accept it. See this article [What should I do when someone answers my question?](https://stackoverflow.com/help/someone-answers) – Harun24hr Nov 21 '22 at 10:47
0

try REDUCE:

=INDEX(QUERY(REDUCE(, A3:INDEX(A:A, MAX(ROW(A:A)*(A:A<>""))), 
 LAMBDA(x, a, {x; JOIN(, LAMBDA(i, f, u, i&"_"&SEQUENCE(1, u-f+1, f)&";")
 (a, OFFSET(a,,1), OFFSET(a,,2)))})), "offset 1", ))

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124