google sheets - FLATTEN output from ARRAYFORMULA with TRANSPOSESPLITVLOOKUP - Stack Overflow

admin管理员组

文章数量:1123890

I'm attempting to create a spreadsheet to provide a visual representation of a server rack. I have a server named range with rack# and Rack Unit (RU) location of the server within that rack. The challenge is representing multi-RU servers into RU slots with a VLOOKUP.

Example Server Rack Sheet

In the example sheet (Rack Tab) I depicted an example desired output.

Column B (B2) was my original VLOOKUP, Column D (D2), E was my failed attempt and Column G is what I would like my desired output to look like.

I attempted to use SPLIT/TRANSPOSE combo to split the server description (using "/" delimiter) and TRANSPOSEd the output to 'flatten' the split (Formula below):

=ARRAYFORMULA(TRANSPOSE(ARRAYFORMULA(IF(LEN(B$1)=0,,IFNA(TRANSPOSE(SPLIT(VLOOKUP(RIGHT(B$1,3)&"-"&$A$2:$A,ServerDB,6,0),"/")),"Open")))))

The issue is SPLIT flattens the Description but TRANSPOSE flattens the VLOOKUP and TRANSPOSing again recreates the SPLIT. I'm not stuck on doing it my way so if any has a solution or a creative idea I'm open to suggestions. Thank you for taking the time to look at my problem.

I'm attempting to create a spreadsheet to provide a visual representation of a server rack. I have a server named range with rack# and Rack Unit (RU) location of the server within that rack. The challenge is representing multi-RU servers into RU slots with a VLOOKUP.

Example Server Rack Sheet

In the example sheet (Rack Tab) I depicted an example desired output.

Column B (B2) was my original VLOOKUP, Column D (D2), E was my failed attempt and Column G is what I would like my desired output to look like.

I attempted to use SPLIT/TRANSPOSE combo to split the server description (using "/" delimiter) and TRANSPOSEd the output to 'flatten' the split (Formula below):

=ARRAYFORMULA(TRANSPOSE(ARRAYFORMULA(IF(LEN(B$1)=0,,IFNA(TRANSPOSE(SPLIT(VLOOKUP(RIGHT(B$1,3)&"-"&$A$2:$A,ServerDB,6,0),"/")),"Open")))))

The issue is SPLIT flattens the Description but TRANSPOSE flattens the VLOOKUP and TRANSPOSing again recreates the SPLIT. I'm not stuck on doing it my way so if any has a solution or a creative idea I'm open to suggestions. Thank you for taking the time to look at my problem.

• google-sheets
• google-apps-script
• google-sheets-formula

Share Improve this question Follow asked yesterday Jay BivensJay Bivens 1 New contributor Jay Bivens is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct. 3

• 2 @zer00ne function names in Excel / Google Sheets are conventionally written in uppercase. There's no shouting here. – z.. Commented yesterday
• 1 Make sure to provide input and expected output as plain text table in the question. Check my answer or other options to create a table easily, which are easy to copy/paste. Avoid sharing links like spreadsheets, which make the question useless for others or images, which are hard to copy. Also, note that your email address can also be accessed by the public, if you share Google files. – TheMaster Commented yesterday
• 1 Welcome to Stack Overflow! An answer has been posted in response to your question. Please review the answer if it correctly solves your issue. If it does, please click the accept button on the left (check icon) and/or upvote the answer. By doing so, other people in the community, who may have the same concern as you, will know that their issue can be resolved. For further details, you may read the article on how to accept the answer. – Jats PPG Commented 16 hours ago

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 0

Here's a solution based on reduce():

=let( 
  table, filter( 
    hstack( 
      regexextract(choosecols(ServerDB, 1), "\d+$"), 
      split(choosecols(ServerDB, 6), "/") 
    ), 
    left(choosecols(ServerDB, 1), 3) = right(B1, 3) 
  ), 
  slots, choosecols(table, 1), 
  descs, choosecols(table, sequence(columns(table) - 1, 1, 2)), 
  list, tocol(reduce(tocol(æ, 2), sequence(rows(descs)), lambda(a, i, 
    vstack( 
      a, 
      reduce(torow(æ, 2), sequence(columns(descs)), lambda(b, j, 
        hstack(b, index(slots, i) - j + 1, index(descs, i, j)) 
      )) 
    ) 
  ))), 
  result, arrayformula(ifna(vlookup( 
    tocol(A2:A, 1), wraprows(list, 2), 2, false 
  ))), 
  arrayformula(if(len(result), result, "Open"))  
)

See reduce() and your sample spreadsheet.

Here's a possible solution.

=ARRAYFORMULA(LET(
  x,IFNA(VLOOKUP(RIGHT(B1,3)&"-"&TOCOL(A2:A,1),ServerDB,6,)),
  s,SEQUENCE(ROWS(x)),
  sp,SPLIT(x,"/"),
  nop,SPLIT(TOCOL(FILTER(s+SEQUENCE(1,COLUMNS(sp),0)&"❅"&sp,x<>""),1),"❅"),
  IFNA(VLOOKUP(s,QUERY(nop,"where Col2<>''"),2,),"Open")))

This formula assumes the number of adjacent Open slots is always enough to fill the descriptions.

本文标签： google sheetsFLATTEN output from ARRAYFORMULA with TRANSPOSESPLITVLOOKUPStack Overflow