Array question

[I2omani]

Howdy,
I am trying to make my life easier using an excel sheet to populate router se. this is the format i am looking for "Se7/0/0.12/2:1". so this is in group 7 and it is card no 12, port 2. so my the formula i am looking for will return "864-2"

I have 10 group , i am trying to find a way to write the card number and the port number from the Serial.
Group 7
1 853
2 854
.
.
12 864

can this be done

thank you in advance

 

[al_b_cnu]

hi, you can do it with a 'helper' lookup table:
<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;">Group</td><td style="text-align: center;;">Card</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Group</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">1</td><td style="text-align: center;;">100</td><td style="text-align: right;;"></td><td style=";">Se7/0/0.12/2:1</td><td style=";">864-2</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">2</td><td style="text-align: center;;">200</td><td style="text-align: right;;"></td><td style=";">xx1/99/99.4/3:1</td><td style=";">103-3</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">3</td><td style="text-align: center;;">300</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">4</td><td style="text-align: center;;">400</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">5</td><td style="text-align: center;;">500</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">6</td><td style="text-align: center;;">600</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">7</td><td style="text-align: center;;">853</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">8</td><td style="text-align: center;;">250</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">9</td><td style="text-align: center;;">350</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;">10</td><td style="text-align: center;;">450</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E2</th><td style="text-align:left">=VLOOKUP(<font color="Blue">MID(<font color="Red">D2,3,SEARCH(<font color="Green">"/",D2</font>)-3</font>)+0,$A$2:$B$11,2,FALSE</font>)+MID(<font color="Blue">D2,SEARCH(<font color="Red">".",D2</font>)+1,SEARCH(<font color="Red">"/",D2,SEARCH(<font color="Green">".",D2</font>)</font>)-SEARCH(<font color="Red">".",D2</font>)-1</font>)-1&"-"&MID(<font color="Blue">D2,SEARCH(<font color="Red">"/",D2,SEARCH(<font color="Green">".",D2</font>)</font>)+1,SEARCH(<font color="Red">":",D2</font>)-SEARCH(<font color="Red">"/",D2,SEARCH(<font color="Green">".",D2</font>)</font>)-1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E3</th><td style="text-align:left">=VLOOKUP(<font color="Blue">MID(<font color="Red">D3,3,SEARCH(<font color="Green">"/",D3</font>)-3</font>)+0,$A$2:$B$11,2,FALSE</font>)+MID(<font color="Blue">D3,SEARCH(<font color="Red">".",D3</font>)+1,SEARCH(<font color="Red">"/",D3,SEARCH(<font color="Green">".",D3</font>)</font>)-SEARCH(<font color="Red">".",D3</font>)-1</font>)-1&"-"&MID(<font color="Blue">D3,SEARCH(<font color="Red">"/",D3,SEARCH(<font color="Green">".",D3</font>)</font>)+1,SEARCH(<font color="Red">":",D3</font>)-SEARCH(<font color="Red">"/",D3,SEARCH(<font color="Green">".",D3</font>)</font>)-1</font>)</td></tr></tbody></table></td></tr></table><br />

The values i column B are start values for the group, I've guessed these for all but group 7 :¬/

 

[Richard Schollar]

Hi

I think you'll need to explain greater explanation of how 864-2 is generated from "Se7/0/0.12/2:1" if that's what you meant. If not, then I haven't understood and further explanation would be appreciated.

 

[GlennUK]

I have to agree with Richard about needing further explanation. But I've had an initial guess, showing the processes I've used to generate a result. If the result is right I can merge all processes into a single formula :

Excel Workbook
	A	B	C	D	E	F	G	H
1	*	Group 4	Group 5	Group 6	Group 7	*	*	Se7/0/0.12/2:1
2	1	512	645	785	853	*	group, from char 3 to first "/"	7
3	2	513	646	786	854	*	text of group	Group 7
4	3	514	647	787	855	*	pos of "/0."	6
5	4	515	648	788	856	*	card no from "/0." to following "/"	12
6	5	516	649	789	857	*	from table	864
7	6	517	650	790	858	*	pos of "/" after "/0."	5
8	7	518	651	791	859	*	get text after above	2:1
9	8	519	652	792	860	*	get text before ":"	2
10	9	520	653	793	861	*	final string	864-2
11	10	521	654	794	862	*	*	*
12	11	522	655	795	863	*	*	*
13	12	523	656	796	864	*	*	*
Sheet6

 

[I2omani]

thank you all for trying to help me out. I will try to explain to the best i can, here we go:

Each Group/Controller/Card represent an OC12. an OC12 has 12 DS3 and each DS3 has 28 T1's
*Se = Serial interface
e.g: Se7/0/0.12/2:1 <- this will read:
Controller 7, the twelve's DS3, Second T1.

there is a map for each card to how this is physically connected:
e.g:
Card 4
1 817
2 818
3 819
etc
12 828
then Card 5 will start at 829, and ends at 840.
Card 6 : starts at 841 - ends 852 and etc etc etc

and i wanted a was that when i write the Serial interface , the formula will use the card table to translate it to the physical connects .

HTH

 

[I2omani]

I have to agree with Richard about needing further explanation. But I've had an initial guess, showing the processes I've used to generate a result. If the result is right I can merge all processes into a single formula :

Excel Workbook
	A	B	C	D	E	F	G	H
1	*	Group 4	Group 5	Group 6	Group 7	*	*	Se7/0/0.12/2:1
2	1	512	645	785	853	*	group, from char 3 to first "/"	7
3	2	513	646	786	854	*	text of group	Group 7
4	3	514	647	787	855	*	pos of "/0."	6
5	4	515	648	788	856	*	card no from "/0." to following "/"	12
6	5	516	649	789	857	*	from table	864
7	6	517	650	790	858	*	pos of "/" after "/0."	5
8	7	518	651	791	859	*	get text after above	2:1
9	8	519	652	792	860	*	get text before ":"	2
10	9	520	653	793	861	*	final string	864-2
11	10	521	654	794	862	*	*	*
12	11	522	655	795	863	*	*	*
13	12	523	656	796	864	*	*	*
Sheet6

i think you nailed it my friend that is what i am looking for. how cay you put it all in one formula please

 

[al_b_cnu]

Hi,

Try:
[CODE<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">   </td><td style=";">Group</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Se7/0/0.12/2:1</td><td style=";">864-2</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Se2/0/0.10/1:1</td><td style=";">802-1</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Se2/0/0.3/1:1</td><td style=";">795-1</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B2</th><td style="text-align:left">=781+(<font color="Blue">MID(<font color="Red">A2,3,SEARCH(<font color="Green">"/",A2</font>)-3</font>)-1</font>)*12+MID(<font color="Blue">A2,SEARCH(<font color="Red">".",A2</font>)+1,SEARCH(<font color="Red">"/",A2,SEARCH(<font color="Green">".",A2</font>)</font>)-SEARCH(<font color="Red">".",A2</font>)-1</font>)-1&"-"&MID(<font color="Blue">A2,SEARCH(<font color="Red">"/",A2,SEARCH(<font color="Green">".",A2</font>)</font>)+1,SEARCH(<font color="Red">":",A2</font>)-SEARCH(<font color="Red">"/",A2,SEARCH(<font color="Green">".",A2</font>)</font>)-1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B3</th><td style="text-align:left">=781+(<font color="Blue">MID(<font color="Red">A3,3,SEARCH(<font color="Green">"/",A3</font>)-3</font>)-1</font>)*12+MID(<font color="Blue">A3,SEARCH(<font color="Red">".",A3</font>)+1,SEARCH(<font color="Red">"/",A3,SEARCH(<font color="Green">".",A3</font>)</font>)-SEARCH(<font color="Red">".",A3</font>)-1</font>)-1&"-"&MID(<font color="Blue">A3,SEARCH(<font color="Red">"/",A3,SEARCH(<font color="Green">".",A3</font>)</font>)+1,SEARCH(<font color="Red">":",A3</font>)-SEARCH(<font color="Red">"/",A3,SEARCH(<font color="Green">".",A3</font>)</font>)-1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B4</th><td style="text-align:left">=781+(<font color="Blue">MID(<font color="Red">A4,3,SEARCH(<font color="Green">"/",A4</font>)-3</font>)-1</font>)*12+MID(<font color="Blue">A4,SEARCH(<font color="Red">".",A4</font>)+1,SEARCH(<font color="Red">"/",A4,SEARCH(<font color="Green">".",A4</font>)</font>)-SEARCH(<font color="Red">".",A4</font>)-1</font>)-1&"-"&MID(<font color="Blue">A4,SEARCH(<font color="Red">"/",A4,SEARCH(<font color="Green">".",A4</font>)</font>)+1,SEARCH(<font color="Red">":",A4</font>)-SEARCH(<font color="Red">"/",A4,SEARCH(<font color="Green">".",A4</font>)</font>)-1</font>)</td></tr></tbody></table></td></tr></table><br />[/CODE]

 

[GlennUK]

In one formula:

Excel Workbook
	A	B	C	D	E	F	G	H
1		Group 4	Group 5	Group 6	Group 7			Se7/0/0.12/2:1
2	1	817	829	841	853		group, from char 3 to first "/"	7
3	2	818	830	842	854		text of group	Group 7
4	3	819	831	843	855		pos of "/0."	6
5	4	820	832	844	856		card no from "/0." to following "/"	12
6	5	821	833	845	857		from table	864
7	6	822	834	846	858		pos of "/" after "/0."	5
8	7	823	835	847	859		get text after above	2:1
9	8	824	836	848	860		get text before ":"	2
10	9	825	837	849	861		final string	864-2
11	10	826	838	850	862
12	11	827	839	851	863		in one formula=	864-2
13	12	828	840	852	864
Sheet6

 

[I2omani]

In one formula:

Excel Workbook
	A	B	C	D	E	F	G	H
1	*	Group 4	Group 5	Group 6	Group 7	*	*	Se7/0/0.12/2:1
2	1	817	829	841	853	*	group, from char 3 to first "/"	7
3	2	818	830	842	854	*	text of group	Group 7
4	3	819	831	843	855	*	pos of "/0."	6
5	4	820	832	844	856	*	card no from "/0." to following "/"	12
6	5	821	833	845	857	*	from table	864
7	6	822	834	846	858	*	pos of "/" after "/0."	5
8	7	823	835	847	859	*	get text after above	2:1
9	8	824	836	848	860	*	get text before ":"	2
10	9	825	837	849	861	*	final string	864-2
11	10	826	838	850	862	*	*	*
12	11	827	839	851	863	*	in one formula=	864-2
13	12	828	840	852	864	*	*	*
Sheet6

one more question , is this process reversible if possible , to put in 864-2 and it generate the Se ?

 

[al_b_cnu]

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Original</td><td style="font-weight: bold;;">Group</td><td style="font-weight: bold;;">And Back again</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Se7/0/0.12/2:1</td><td style=";">864-2</td><td style=";">Se7/0/0.12/2:1</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Se2/0/0.10/1:1</td><td style=";">802-1</td><td style=";">Se2/0/0.10/1:1</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Se2/0/0.3/1:1</td><td style=";">795-1</td><td style=";">Se2/0/0.3/1:1</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B2</th><td style="text-align:left">=781+(<font color="Blue">MID(<font color="Red">A2,3,SEARCH(<font color="Green">"/",A2</font>)-3</font>)-1</font>)*12+MID(<font color="Blue">A2,SEARCH(<font color="Red">".",A2</font>)+1,SEARCH(<font color="Red">"/",A2,SEARCH(<font color="Green">".",A2</font>)</font>)-SEARCH(<font color="Red">".",A2</font>)-1</font>)-1&"-"&MID(<font color="Blue">A2,SEARCH(<font color="Red">"/",A2,SEARCH(<font color="Green">".",A2</font>)</font>)+1,SEARCH(<font color="Red">":",A2</font>)-SEARCH(<font color="Red">"/",A2,SEARCH(<font color="Green">".",A2</font>)</font>)-1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C2</th><td style="text-align:left">="Se"&INT(<font color="Blue">(<font color="Red">LEFT(<font color="Green">B2,3</font>)-781</font>)/12</font>)+1&"/0/0."&MOD(<font color="Blue">(<font color="Red">LEFT(<font color="Green">B2,3</font>)-781</font>),12</font>)+1&"/"&RIGHT(<font color="Blue">B2,LEN(<font color="Red">B2</font>)-4</font>)&":1"</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B3</th><td style="text-align:left">=781+(<font color="Blue">MID(<font color="Red">A3,3,SEARCH(<font color="Green">"/",A3</font>)-3</font>)-1</font>)*12+MID(<font color="Blue">A3,SEARCH(<font color="Red">".",A3</font>)+1,SEARCH(<font color="Red">"/",A3,SEARCH(<font color="Green">".",A3</font>)</font>)-SEARCH(<font color="Red">".",A3</font>)-1</font>)-1&"-"&MID(<font color="Blue">A3,SEARCH(<font color="Red">"/",A3,SEARCH(<font color="Green">".",A3</font>)</font>)+1,SEARCH(<font color="Red">":",A3</font>)-SEARCH(<font color="Red">"/",A3,SEARCH(<font color="Green">".",A3</font>)</font>)-1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C3</th><td style="text-align:left">="Se"&INT(<font color="Blue">(<font color="Red">LEFT(<font color="Green">B3,3</font>)-781</font>)/12</font>)+1&"/0/0."&MOD(<font color="Blue">(<font color="Red">LEFT(<font color="Green">B3,3</font>)-781</font>),12</font>)+1&"/"&RIGHT(<font color="Blue">B3,LEN(<font color="Red">B3</font>)-4</font>)&":1"</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B4</th><td style="text-align:left">=781+(<font color="Blue">MID(<font color="Red">A4,3,SEARCH(<font color="Green">"/",A4</font>)-3</font>)-1</font>)*12+MID(<font color="Blue">A4,SEARCH(<font color="Red">".",A4</font>)+1,SEARCH(<font color="Red">"/",A4,SEARCH(<font color="Green">".",A4</font>)</font>)-SEARCH(<font color="Red">".",A4</font>)-1</font>)-1&"-"&MID(<font color="Blue">A4,SEARCH(<font color="Red">"/",A4,SEARCH(<font color="Green">".",A4</font>)</font>)+1,SEARCH(<font color="Red">":",A4</font>)-SEARCH(<font color="Red">"/",A4,SEARCH(<font color="Green">".",A4</font>)</font>)-1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C4</th><td style="text-align:left">="Se"&INT(<font color="Blue">(<font color="Red">LEFT(<font color="Green">B4,3</font>)-781</font>)/12</font>)+1&"/0/0."&MOD(<font color="Blue">(<font color="Red">LEFT(<font color="Green">B4,3</font>)-781</font>),12</font>)+1&"/"&RIGHT(<font color="Blue">B4,LEN(<font color="Red">B4</font>)-4</font>)&":1"</td></tr></tbody></table></td></tr></table><br />