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

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?

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

Pete

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

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

Thanks in advance Pete

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

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

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))

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:

Please Login or Register  to view this content.

and in C2:

Formula:

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

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

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

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

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

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

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

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

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

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

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.

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

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