0

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.

Improve this question
3
  • 2
    @zer00ne function names in Excel / Google Sheets are conventionally written in uppercase. There's no shouting here.
    – z..
    Commented Jan 9 at 23:01
  • 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 Jan 9 at 23:03
  • 2
    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 Jan 10 at 21:42

2 Answers 2

Reset to default
2

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.

Improve this answer
2

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.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.