View Full Version : Calculate sites number from sectors numbers

justdream

2009-08-21, 06:20 AM

Friends,

I would like to know the most accurate and fastest way to calculate the number of geographical sites, if you have a big excel sheet contain cells ‘sectors of GSM, DCS’

zaebolll

2009-08-21, 07:19 AM

If i understood you correctly than,

You can seperate site ID's from cell ID's with "LEFT" function of excel,

After that you can choose advanced filter and select "unique records only"

Count and Done!

roots

2009-08-21, 09:04 AM

If u r working wid Siemens vendor u can have it by adding up the number of BTSM per BSC

justdream

2009-08-21, 07:35 PM

If i understood you correctly than,

You can seperate site ID's from cell ID's with "LEFT" function of excel,

After that you can choose advanced filter and select "unique records only"

Count and Done!

Dear Zaebolll,

I have tried to use Advanced filter 'unique records only' but with coordinates 'long_lat' as you know for the same site all cells has the same coordinates

but I don't know why, all times the first two row after applying the filter are always duplicated :confused:

which let me be in doubt regarding the accuracy of this method

justdream

2009-08-21, 07:38 PM

If u r working wid Siemens vendor u can have it by adding up the number of BTSM per BSC

Dear roots,

thanks a lot for all your support, yeah I work on siemens, but unfortunately here we have different BTSM for the same site 'example: for site ABC: GSM BTSM is not like DCS BTSM'

eyaOpti_guy

2009-08-21, 08:10 PM

What about cell ID, do you use a sequencial IDs, like for site ABC the cell IDs are 86521, 86522, 86523, 86524, 86525, 86526.. If this is valid you can still use the left an len functions to get the number of sites.....

roots

2009-08-21, 10:25 PM

another idea, try to get only BTS number equal to zero of each site ( the first cell of each site has normally BTS number=0) of ur file, then apply ur filter of coordinates.

hassen

2009-08-22, 12:47 AM

Hi my friend

unfortunately I forgot the method, but I know you can do it with mapinfo for example to calculate the numbre of Trxs in specific area in order to make Bsc dimentioning and ovoid overload ect...

ask mybe some one remembers about the method.

Br hassen

justdream

2009-08-22, 02:27 AM

What about cell ID, do you use a sequencial IDs, like for site ABC the cell IDs are 86521, 86522, 86523, 86524, 86525, 86526.. If this is valid you can still use the left an len functions to get the number of sites.....

yes friend, I use sequential cell ID like what you have mentioned.....but I didn't understand your idea

can you please share more explanation?

Thanks a lot all people

guess.who

2009-08-22, 03:02 AM

tell the type of CIs u are using in ur company and i can help u...

justdream

2009-08-22, 03:39 AM

I don'y understand what do you mean by the type of cell ID?

but it's sequential like:

9320

9321

9322

9323

9324

9325

Hi,

use below mentioned formula in Excel to get Site Id from Cell id

Cell Id(ColumnA) Site Id(Column B)

93201 =left(A2,len(A2)-1)

93202 =left(A3,len(A3)-1)

93203 =left(A4,len(A4)-1)

93204 =left(A5,len(A5)-1)

93205 =left(A6,len(A6)-1)

93206 =left(A7,len(A7)-1)

eyaOpti_guy

2009-08-22, 03:50 AM

Hi there...

I have added a video example on how to do this on excel, it is all in there including the sample file I used..

Please let me know if you found it useful...

PS: the video was recorded using SnagIt with MS-CRAM compression, so if you couldn't play it look for the codec on the web...

yes friend, I use sequential cell ID like what you have mentioned.....but I didn't understand your idea

can you please share more explanation?

Thanks a lot all people

eyaOpti_guy

2009-08-22, 03:52 AM

The example used Excel 2007

justdream

2009-08-22, 05:34 AM

The example used Excel 2007

just one point, after I click on Remove duplicates and get the message of the number of duplicates removed and the remaining items and press ok

then the excel returns to me the same original data with dplicates :confused:, I think it should returns the data after removing the duplicates, may be I have problem with my excel

Thanks mate

justdream

2009-08-23, 01:48 AM

Hi there...

I have added a video example on how to do this on excel, it is all in there including the sample file I used..

Please let me know if you found it useful...

PS: the video was recorded using SnagIt with MS-CRAM compression, so if you couldn't play it look for the codec on the web...

eyas, could you please share with us the converter you used to get this BTS excel sheet from Siemens.ASC files as it's more organized and friendly

eyaOpti_guy

2009-08-23, 02:56 AM

Here is a macro similar to mine..

[Only registered and activated users can see links]

My macro is BR8.0 but I'll update it to work on BR9.0 ASAP and I'll share it here for all of you guys..

eyas, could you please share with us the converter you used to get this BTS excel sheet from Siemens.ASC files as it's more organized and friendly

timedomain

2009-08-24, 07:43 AM

use formula =left(A2,len(A2)-1)

this will be like site id(cell id = 51234 after formula it will be 5123)

then copy that column to another sheet and remove dublicates, and u can use the vlookup to put coordinates.

whattavira

2009-08-24, 05:27 PM

use formula =left(A2,len(A2)-1)

this will be like site id(cell id = 51234 after formula it will be 5123)

then copy that column to another sheet and remove dublicates, and u can use the vlookup to put coordinates.

Hey, i'm not agree using this formula

Because it is possible two sites have a same "len(A2)-1" prefix number, the difference only the last digit. Such as sample below:

Cell A1 = JT09961

Cell A2 = JT09962

Cell A3 = JT09963

Cell B1 = JT09964

Cell B2 = JT09965

Cell B3 = JT09966

why not calculate based on counting for specific long/lat?

justdream

2009-08-24, 09:40 PM

Hey, i'm not agree using this formula

Because it is possible two sites have a same "len(A2)-1" prefix number, the difference only the last digit. Such as sample below:

Cell A1 = JT09961

Cell A2 = JT09962

Cell A3 = JT09963

Cell B1 = JT09964

Cell B2 = JT09965

Cell B3 = JT09966

why not calculate based on counting for specific long/lat?

in your example, are cell A and B GSM and DCS for the same site?

or they are geographically separated

in my network, your example apply for dual band sites (GSM and DCS), hereby no problem

whattavira

2009-08-25, 01:10 PM

in your example, are cell A and B GSM and DCS for the same site?

or they are geographically separated

in my network, your example apply for dual band sites (GSM and DCS), hereby no problem

In my network case, cell A and B have different Long/Lat :p

justdream

2009-08-25, 08:41 PM

friends, another task please

I want to calculate the numbe of Single band GSM, single band DCS and Dual band sites

do you know how could I made a flag to differentiate between them

justdream

2009-08-26, 02:42 AM

it's not necessary to do it by Excel

if you know a way to do it through some tools like Optimi 'it will be also very good'

Thanks a lot, my friends :hug:

justdream

2009-09-04, 09:19 PM

Hi my friend

unfortunately I forgot the method, but I know you can do it with mapinfo for example to calculate the numbre of Trxs in specific area in order to make Bsc dimentioning and ovoid overload ect...

ask mybe some one remembers about the method.

Br hassen

can you please help me to do this exercise?

I need it urgently :hug:

justdream

2009-09-10, 09:32 PM

no body, can help regarding calculating no. of TRX????

alkelani

2009-09-11, 04:29 AM

as i understand you should use in excel this operation

=left(the cell id,len(cell id)-1)

example: if cell is called RAMA1A,or Rama1B,or RAMA1C

then the function will take letter from the left of anumber equal to the length of the cellID minus one.......=left(RAMA1B,len(RAMA1B)-1)=RAMA1 which is the site ID.:)

marceloastengo

2010-02-11, 01:44 AM

I want to calculate somes cell numbers

wnsworld

2010-02-13, 04:36 AM

Hay, here 47_excel_formulas, learn it, use it and get ur solution..

You can use MapInfo also for calculate no of sites..

for password, find it in my Signature

nabil el haddani

2010-02-13, 04:57 AM

Hi justdream :)

Plz send me your excel and i will do the exercise for u, tell me just what do u want exactly!

Br,

Nab

vBulletin® v4.1.12, Copyright ©2000-2014, Jelsoft Enterprises Ltd.