How to combine multiple sequence arrays and append a prefix from an array in Excel

Question

I am trying to create an array with the interfaces for networking devices. The array of interfaces is an undetermined size and there can be 0-3 slashes in the interface. Below is an example of the interfaces for one device.

A1: G1/0/1, G1/1/1 (first inteface) B1: G1/0/24, G1/1/4 (last interface)

I am seeking C1:

G1/0/1
G1/0/2
G1/0/3
G1/0/4
G1/0/5
G1/0/6
G1/0/7
G1/0/8
G1/0/9
G1/0/10
G1/0/11
G1/0/12
G1/0/13
G1/0/14
G1/0/15
G1/0/16
G1/0/17
G1/0/18
G1/0/19
G1/0/20
G1/0/21
G1/0/22
G1/0/23
G1/0/24
G1/1/1
G1/1/2
G1/1/3
G1/1/4

Excel Example Image

I do not want to use VBA. This is tricky because Excel does not have built-in support for using Sequences with an Array. Also, I need to append the prefix for the interfaces, which may change based upon the inteface pair. Futhermore, the amount of slashes in the interface may be from 0-3.

I found a similar problem at: https://www.reddit.com/r/excel/comments/tnl3q6/how_to_string_together_multiple_sequence_arrays/

I tried using

=LET(
a, TRANSPOSE(TEXTAFTER(D2#,"/",2) - TEXTAFTER(D1#,"/",2) + 1),
b, ROW(a),
c, TRANSPOSE(b),
d, --(b>=c),
e, MMULT(d, a),
f, SUM(a),
g, SEQUENCE(f),
h, XMATCH(g-1, e,-1),
I, IFERROR(INDEX(e,h),0),
j, g - I,
j)

which allows me to generate a sequence using an array. This works for finding the interface number, but I do not know how to add the prefix specific to that interface group using this formula. I heard that it may also be possible to use VSTACK to get a similar result, but I do not know how to use VSTACK.

One idea I had was to create an array that finds when the sequence array iterates to the next sequence using:

=LET(
SEQ_1,LET(
a, TRANSPOSE(TEXTAFTER(D2#,"/",2) - TEXTAFTER(D1#,"/",2) + 1),
b, ROW(a),
c, TRANSPOSE(b),
d, --(b>=c),
e, MMULT(d, a),
f, SUM(a),
g, SEQUENCE(f),
h, XMATCH(g-1, e,-1),
I, IFERROR(INDEX(e,h),0),
j, g - I,
j), IF(INDEX(SEQ_1,SEQUENCE(COUNTA(SEQ_1)))>INDEX(SEQ_1,SEQUENCE(COUNTA(SEQ_1))-1), FALSE, TRUE))

By using this, I can iterate through a prefix array.

D1:

=TRANSPOSE(INDEX(TEXTSPLIT(TEXTJOIN(",", TRUE, TEXTSPLIT(A2, ", ") & "," & TEXTSPLIT(B2, ", ")), ","), SEQUENCE(COUNTA(TEXTSPLIT(TEXTJOIN(",", TRUE, TEXTSPLIT(A2, ", ") & "," & TEXTSPLIT(B2, ", ")), ","))/2,1,1,2)))

D2:

=TRANSPOSE(INDEX(TEXTSPLIT(TEXTJOIN(",", TRUE, TEXTSPLIT(A2, ", ") & "," & TEXTSPLIT(B2, ", ")), ","), SEQUENCE(COUNTA(TEXTSPLIT(TEXTJOIN(",", TRUE, TEXTSPLIT(A2, ", ") & "," & TEXTSPLIT(B2, ", ")), ","))/2,1,2,2)))

Tested Formulas Image

Mayukh Bhattacharya · Accepted Answer · 2024-02-04 10:01:38Z

2

Try in the following way:

=LET(
     _Data, A1:B1,
     _First, TEXTBEFORE(_Data,","),
     _Last, TEXTAFTER(_Data,", "),
     _Append, VSTACK(_First,_Last),
     _FirstLast, --TEXTAFTER(_Append,"/",-1),
     _NOfRows, SEQUENCE(,MAX(_FirstLast)),
     TOCOL(IFS((_NOfRows>=TAKE(_FirstLast,,1))*(_NOfRows<=TAKE(_FirstLast,,-1)),UNIQUE(TEXTBEFORE(_Append,"/",2),1)&"/"&_NOfRows),2))

Update:

=LET(
     _First, TAKE(A1:B3,,1),
     _Last,  TAKE(A1:B3,,-1),
     _NOfDelimiters, SEQUENCE(,MAX(LEN(_First)-LEN(SUBSTITUTE(_First,",",))+1)),
     _Start, TOCOL(TEXTSPLIT(TEXTAFTER(", "&_First,", ",_NOfDelimiters),", "),2),
     _End, TOCOL(TEXTSPLIT(TEXTAFTER(", "&_Last,", ",_NOfDelimiters),", "),2),
     _Merge, HSTACK(_Start,_End),
     _FirstLast, --TEXTAFTER(_Merge,"/",-1),
     _NOfRows, SEQUENCE(,MAX(_FirstLast)+1,0),
     TOCOL(IFS( (_NOfRows>=TAKE(_FirstLast,,1))*(_NOfRows<=TAKE(_FirstLast,,-1)),UNIQUE(TEXTBEFORE(_Merge,"/",MAX(-2,-1)),1)&"/"&_NOfRows),2))

In the screenshot the output is not rotated vertically, so as the output can be shown clearly. Formula remains the same as the one in Update

edited Feb 4, 2024 at 10:01

answered Feb 4, 2024 at 8:05

Mayukh Bhattacharya

29.2k9 gold badges31 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Brandon Over a year ago

This does not work for Arrays greater than two in size (G0/0/0, G0/1/0, G0/2/0; G0/0/2, G0/1/3, G0/2/3). This also does not work for instances where there are no slashes in the interface. But, this is a great help.

Mayukh Bhattacharya Over a year ago

@Brandon also the formula you have posted doesnt resemblance with your screenshots. In the formula it A2:A4 where as you have A1 and B1 please show something where you have tried and the error you are getting in. Because as per your OP it says Futhermore, the amount of slashes in the interface may be from 0-3. but i dont see anything which relates could you explain further and show what you have tried yet?

Brandon Over a year ago

Sorry about that. I am not an expert at Excel, so the formulas I used were randomly thrown on my sheet for testing, and the cells didn't match up. However, your solution is very good, but it only works for arrays with two elements and doesn't account for slashes.

Mayukh Bhattacharya Over a year ago

@Brandon just posted an updated answer!

Brandon Over a year ago

Wow, that is perfect!

Collectives™ on Stack Overflow

How to combine multiple sequence arrays and append a prefix from an array in Excel

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related