PDA

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

faib
2009-08-22, 03:47 AM
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.:)

lala
2009-12-27, 06:58 PM
why I can not download

marceloastengo
2010-02-11, 02:44 AM
I want to calculate somes cell numbers

wnsworld
2010-02-13, 05: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, 05: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