+ Reply to Thread
Results 1 to 25 of 25

Advanced lookup matching part of a number

  1. #1
    Registered User
    Join Date
    08-15-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    13

    Advanced lookup matching part of a number

    Hi

    I have the following challenge...

    I need to match a list of numbers which are numbers from a phone bill that people have dialled. I need to check that number against a list of dialling codes to return results based upon the lookup. i.e. the destination and the cost. The dialling codes, destinations and costs are contained within a different spreadsheet.

    Main problem is the numbers vary in length, both the number dialled and the dialling code.

    For example if lookup the dial number, say 5423211234, that potentially has three matches for the destination 54, 54232 and 542321 therefore 3 results. The correct result I want to return is the 542321

    I hope that all makes sense? Thanks in advance

  2. #2
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Advanced lookup matching part of a number

    Something like this?

    The codes are in A1:A3, the destinations are in B1:B3 and the value we're looking up is in B7, with the matched destination in C7.

    Is that what you're after?
    Attached Files Attached Files

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,773

    Re: Advanced lookup matching part of a number

    Post an example workbook (or workbooks) which show what data you have and what you want to achieve.

    Pete

  4. #4
    Registered User
    Join Date
    08-15-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Advanced lookup matching part of a number

    Hi

    I have attached a simple example which I hope can extend out with the base function. There are 65536 potential variation of dialling codes in the world which may have differing description and or price! I have removed many entries to keep the spreadsheet under the 1mb max upload.

    Columns A and B and C would normally be on a separate spreadsheet as a "master" price and destination list.

    Column C is three random examples to match to a destination

    I would like to return to answers, the destination and it's cost.

    Thanks in advance
    Attached Files Attached Files

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,773

    Re: Advanced lookup matching part of a number

    It will take me quite a while to set this up, and I'm going out now, so I'll get back to you later on.

    Pete

  6. #6
    Registered User
    Join Date
    08-15-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Advanced lookup matching part of a number

    Thanks in advance Pete

  7. #7
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,773

    Re: Advanced lookup matching part of a number

    I'm back now and have had chance to look at this in more detail. I see that your costs are all set to the same value, but in your real sheet these are likely to be different. It will hamper my testing of the solution if they are all the same, as I won't be able to check that I am retrieving the correct value. Also, you mentioned that you have far more destinations in your real file, and I shall be manipulating the data quite a bit to get it in a format to optimise performance. It would make more sense, therefore, if you could paste the real workbook so I can set things up on that so you'll be able to paste in some call data to another sheet.

    So, could you post your real destination data sheet? If it is too large to post in one file, then split it into say 3 files each of 20,000 or so rows, so that I can re-combine them at this end.

    Hope this helps.

    Pete

  8. #8
    Registered User
    Join Date
    08-15-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Advanced lookup matching part of a number

    I have manage to reduce the file size somewhat and now my variations are just 1,526!

    I have attached the files to be used as a lookup, which will be in this format and maintained in a separate spreadsheet. Different pricing

    So the result I want to achieve is a spreadsheet containing the dialed number looks at the attached and displays the destination and the cost based upon the dialling code.

    Thanks
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    08-15-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Advanced lookup matching part of a number

    Below is the code I have added to N2

    For the moment I pasted the list to lookup in Sheet2

    The dialled number is in D2

    It correctly says Ireland-Mobile-Vodafone when looking up 353872571111 but bizarrely 3538725776 returns Burundi-Mobile-Eco ?

    I think it's because it search right to left of the number not left to right?

    Thanks

    =INDEX(Sheet2!B:B,MATCH(LARGE(INDEX(ISNUMBER(FIND(Sheet2!A:A,D2))*LEN(Sheet2!A:A),0),1),INDEX(ISNUMBER(FIND(Sheet2!A:A,D2))*LEN(Sheet2!A:A),0),0))

  10. #10
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,773

    Re: Advanced lookup matching part of a number

    I've attached a file that does this for you.

    I've included comments in blue on the Destinations sheet to indicate the major steps I've taken, essentially to copy the data into adjacent columns (so that the original data remains in the same order), sort the copied data by Dial_code, introduce formulae to link back to the original data (so that if you need to change prices in future and find it easier to do so if the destinations are alphabetical, then you can still do so), and then introduce named ranges for codes of equal length (L_1, L_2 etc).

    In the Calls_made sheet you have a list of dialled numbers in column A, together with formulae in B2, C2 and E2 which are to be copied down at least as far as your data, but it doesn't matter if you copy too far as the hyphens indicate.

    This is the main formula in E2:

    =IFERROR(MATCH(1*LEFT(A2,MAX(IF(ISERROR(MATCH(1*LEFT(A2,1),L_1,0)),0,1),IF(ISERROR(MATCH(1*LEFT(A2,2),L_2,0)),0,2),IF(ISERROR(MATCH(1*LEFT(A2,3),L_3,0)),0,3),IF(ISERROR(MATCH(1*LEFT(A2,4),L_4,0)),0,4),IF(ISERROR(MATCH(1*LEFT(A2,5),L_5,0)),0,5),IF(ISERROR(MATCH(1*LEFT(A2,6),L_6,0)),0,6),IF(ISERROR(MATCH(1*LEFT(A2,7),L_7,0)),0,7),IF(ISERROR(MATCH(1*LEFT(A2,8),L_8,0)),0,8),IF(ISERROR(MATCH(1*LEFT(A2,9),L_9,0)),0,9),IF(ISERROR(MATCH(1*LEFT(A2,10),L_10,0)),0,10),IF(ISERROR(MATCH(1*LEFT(A2,11),L_11,0)),0,11))),Dial_Code,0),"-")

    I've left it like this rather than shorten it as an array formula so that you can follow more easily what it does. Essentially, it takes the first digit of the dialled number and sees if there is a match with the range L_1, then the first 2 digits and compares it with L_2, then the first 3 digits with L_3, and so on. The MAX function ensures that the largest number is chosen, i.e. it matches with the largest number of digits possible, and returns the overall row where that match occurred. The formula in B2, i.e.:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    and in C2:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    then bring the data from the corresponding appropriate columns if there was a match, or returns a hyphen if not.

    In future, all you need to do is paste new dialled digits to column A, and ensure that the formulae are copied down.

    Hope this helps.

    Pete
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    08-15-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Advanced lookup matching part of a number

    Thanks Pete

    That works great with your version when I pasted my "real data" into the dialled number column.

    I re-created your layout and replicated the code into another spreadsheet. i.e. the actual report which has other info i.e. who dialled the number and how long for etc. And I get returned a -

    I think it's got something to do with the Len Column. How did you populate that?

    I have checked the code is the same, all data you have entered I have in to same columns and named worksheets.

    Thanks

  12. #12
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,773

    Re: Advanced lookup matching part of a number

    Mayfair Blue,

    it took me quite a while to set up those individual named ranges, so I would suggest that you take my file, rename it, and then on the Calls_made sheet (which you might want to rename as well) you can insert columns as necessary to suit the layout of your real data, eg for date/time columns, call duration, CLI etc. The formulae that I gave you will automatically adjust if you insert new columns, and this will be a far quicker way of you ending up with a working file than if you were to build those named ranges again for L_1, L_2 etc.

    Hope this helps.

    Pete

    EDIT: actually, another way would be to have my file open and your file, but without the Destinations sheet in it - and any named ranges removed. Then you can just CTRL-drag my destinations sheet tab into your file and the named ranges will come with it. You will then need to set up the formulae which are in B2, C2 and E2 of the Calls_made sheet in my file into the appropriate cells of your file, although you will need to change the cell references to suit your particular layout. In a case like this I often put an apostrophe in front of the = sign so that it is treated as text, and then you can copy it across. Then, with that cell and an adjacent cell selected, you can do Find & Replace (CTRL-H) and change A2, for example, to D2 if that is where your dialled number is, and then eventually you can remove the apostrophe.
    Last edited by Pete_UK; 08-15-2012 at 02:54 PM.

  13. #13
    Registered User
    Join Date
    08-15-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Advanced lookup matching part of a number

    Thanks Pete

    I had started on that principle just curious if there was something I was doing wrong and or a quick fix.

    Many Many Thanks for that it's going to be a massive help.

    I did just notice another problem the bill does not put in 44 if UK!

    Now need formula to say if L2 contains "UK" or "O2" then ="44"&d2 if not =d2 (Both UK and O2 is not the only thing in the cell, "Roaming Zone 1 to UK" for example.

    That then should add the 44 to the UK part of the calls to another cell that I will then use to paste into your version. Do you happen to know the correct way of writing that? Or a alternative method?

    Thanks once again

  14. #14
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,773

    Re: Advanced lookup matching part of a number

    I do a lot of telephone analysis for my clients. You have to ensure that the dialled number is always in the same format, in order for the exact matches to work. Your codes are actually numbers, so that is why I have 1*LEFT( ... several times in that long formula, to ensure that the extracted digits are also converted into numbers. If you have a formula that includes the expression "44"&D2, then that will convert the dialled number into a text format.

    However, having said that, the formula that I gave you originally in E2 should still work, as long as it is looking at the cell where those corrected dialled numbers occur (M2 ?). If that is the case, then M2 should contain this:

    =IF(OR(LEFT(L2,2)="UK",LEFT(L2,2)="O2"),"44"&D2,D2)

    This assumes that the description in L2 would begin "UK ...." or "O2 ....", but if the "UK" can be within the description (like "National Rate UK calls"), then we would probably have to use ISNUMBER(SEARCH ....

    Hope this helps.

    Pete

  15. #15
    Registered User
    Join Date
    08-15-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Advanced lookup matching part of a number

    Hi Pete

    Both UK and O2 could be just in the description and not just at the beginning. The ISNUMBER etc the correct string?

    Thanks

    ---------- Post added at 08:21 PM ---------- Previous post was at 08:17 PM ----------

    Hi Pete

    Both UK and O2 could be just in the description and not just at the beginning. The ISNUMBER etc the correct string?

    Thanks

  16. #16
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,773

    Re: Advanced lookup matching part of a number

    In that case the formula would become:

    =IF(OR(ISNUMBER(SEARCH("UK",L2)),ISNUMBER(SEARCH("O2",L2))),"44"&D2,D2)

    which now looks for either of those two characters being contained anywhere within the description in L2. However, you might get incorrect returns, for example if the description is for "Brukheim" - made up, but to show a possible problem.

    Hope this helps.

    Pete

  17. #17
    Registered User
    Join Date
    08-15-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Advanced lookup matching part of a number

    Perfect!

    But, there is always a but :-)

    The UK mobile doesn't resolve the abbreviated dialling codes for them is a problem. If I change to just 447 for UK Mobile and not carrier specific then it then becomes a 3 digit length and probably messes up your Len settings. It still does not display any UK mobile anyway?

    Thanks

  18. #18
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,773

    Re: Advanced lookup matching part of a number

    I think you need to post another example file showing the range of descriptions that you can have in the data you receive from O2. Often there will be a column for destination codes as well as a (narrative) description, and it might be better to match on the codes rather than if the description contains some characters. If you have calls to Ukraine, for example, these will have "44" appended onto the dialled digits and thus be treated as UK calls.

    Pete

  19. #19
    Registered User
    Join Date
    08-15-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Advanced lookup matching part of a number

    Attached is a couple of lines from the actual call data and the descriptive headers from O2.

    Columns A:E has been added to match your template and number description is normally in I

    This is proving to be a mare and I still have to do Voda, Orange and T-Mob yet.

    Thanks for you kind help on this
    Attached Files Attached Files

  20. #20
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,773

    Re: Advanced lookup matching part of a number

    Not enough data to work with. Can you post a full set of data for columns F, O, P and Q - you can clear the contents of all the other columns.

    Pete

  21. #21
    Registered User
    Join Date
    08-15-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Advanced lookup matching part of a number

    Column F is the number that made the call I presume you want the number that is dialled (Number/Description as O2 call it), column A on the temp spreadsheet.

  22. #22
    Registered User
    Join Date
    08-15-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Advanced lookup matching part of a number

    Hi

    I have attached the file we receive remove the column data not required and removed 3 digits off the end of each number being dialled and dialled from.

    Dialling number is the number that made the call and Number/Description is the number being called.

    Thanks
    Attached Files Attached Files

  23. #23
    Registered User
    Join Date
    08-15-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Advanced lookup matching part of a number

    Can the template be that so if I need to amend the dialling code fields either by changing the string length or adding or removing dialling codes? This I presume in it's current setup will change the Len fields and therefore "break" the script.

    The more I look into this I am going to have to use the full list of codes 65,000 + Still need the possibility of amending, adding or changing. Is this possible to build. Appreciate the time you have spent on this.

    Thanks
    Last edited by MayfairBlue; 08-16-2012 at 04:18 AM.

  24. #24
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,773

    Re: Advanced lookup matching part of a number

    If you come across new dialling codes then you can just insert a partial row in columns G to J and put it there. You should insert the cells within the appropriate named range, i.e. if the code has 6 digits then you should insert the cells somewhere between the first and last cells that define L_6 - the codes do not have to be in order. If you want to link the new code back to the data in cols A to D, then you need to add it to the bottom of the list and use the next sequence number in turn, and then use that sequence number in the inserted cells along with the formulae.

    If you want to remove a code then you can just delete those cells from cols G to J - no need to remove it from A to D.

    You do not need 65,000+ codes, as the formula I gave you matches on the largest number of digits. So, if you had 123456 as defining destination X, then any calls to 123456xxx (where x is any number) will match to that destination. In my look-up tables (for landline codes) I have 3500 codes, which serves for all mobile networks, international, overseas mobile, PRS and other services etc. It does need periodic adjustment, as codes allocated (particularly for overseas mobile) do change now and then.

    I'll look at the file you posted last night a bit later on (I have to do some work now and then !!).

    Pete

  25. #25
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,773

    Re: Advanced lookup matching part of a number

    I've looked at your file from post #22 in some detail, and I think you will find it quite difficult to come up with a formula to add "44" to the dialled number and which will work correctly in all circumstances. Such a formula would have to look at columns N and O and would be quite lengthy and difficult to maintain. However, you could change the "UK" to "to UK" in the formula I gave you in post #16 to improve the success rate of that formula.

    Another approach might be to derive destinations from the call type in column N - when I analyse mobile phone bills (from many different suppliers, not just O2) I tend to take this approach and translate the supplier's description into a set of codes which make sense to me, eg "UK Landline", "NGN National", "NGN Local", "Same-network", "Cross-network" and so on, as prices tend to be charged in these charge bands.

    It all depends on what you ultimately want to achieve from your analysis.

    Hope this helps.

    Pete
    Last edited by Pete_UK; 08-16-2012 at 08:34 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

X vBulletin 4.1.8 Debug Information

  • Page Generation 0.09583 seconds
  • Memory Usage 9,581KB
  • Queries Executed 15 (?)
More Information
Template Usage (32):
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_global_above_footer
  • (1)ad_global_below_navbar
  • (1)ad_global_header1
  • (1)ad_global_header2
  • (1)ad_navbar_below
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)ad_thread_first_post_content
  • (1)ad_thread_last_post_content
  • (1)footer
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (25)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (4)navbar_noticebit
  • (6)option
  • (6)postbit_attachment
  • (25)postbit_legacy
  • (25)postbit_onlinestatus
  • (25)postbit_wrapper
  • (4)showthread_bookmarksite
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper
  • (1)vbseo_linkbackmenu_entry 

Phrase Groups Available (6):
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files (39):
  • ./vbseo.php
  • ./env.php
  • ./vbseo/includes/functions_vbseo.php
  • ./vbseo/includes/functions_vbseo_pre.php
  • ./vbseo/includes/functions_vbseo_url.php
  • ./vbseo/includes/functions_vbseo_createurl.php
  • ./vbseo/includes/functions_vbseo_db.php
  • ./vbseo/includes/functions_vbseo_vb.php
  • ./vbseo/includes/functions_vbseo_seo.php
  • ./vbseo/includes/functions_vbseo_misc.php
  • ./vbseo/includes/functions_vbseo_crr.php
  • ./vbseo/includes/functions_vbseo_cache.php
  • ./vbseo/includes/functions_vbseo_hook.php
  • ./vbseo/includes/functions_vbseo_startup.php
  • ./includes/config.php
  • ./showthread.php
  • ./global.php
  • ./includes/class_bootstrap.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/functions.php
  • ./includes/class_friendly_url.php
  • ./includes/class_hook.php
  • ./includes/functions_cforum.php
  • ./includes/functions_facebook.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/class_bootstrap_framework.php
  • ./vb/vb.php
  • ./vb/phrase.php
  • ./packages/vbattach/attach.php
  • ./vb/types.php
  • ./vb/cache.php
  • ./vb/cache/db.php
  • ./vb/cache/observer/db.php
  • ./vb/cache/observer.php
  • ./includes/functions_notice.php 

Hooks Called (47):
  • init_startup
  • friendlyurl_resolve_class
  • database_pre_fetch_array
  • database_post_fetch_array
  • global_bootstrap_init_start
  • global_bootstrap_init_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • global_state_check
  • global_bootstrap_complete
  • global_start
  • style_fetch
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • friendlyurl_redirect_canonical
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • fetch_musername
  • cache_templates
  • template_register_var
  • parse_templates
  • notices_check_start
  • notices_noticebit
  • process_templates_complete
  • reputation_image
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • memberaction_dropdown
  • postbit_attachment
  • tag_fetchbit_complete
  • forumrules
  • showthread_bookmarkbit
  • navbits
  • navbits_complete
  • showthread_complete


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1