• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Finding numbers from within numbers

T

Thunder001

New Member
Let’s say that in column A you have a list of thousands of 12-digit numbers (each number separated by a dash). The numbers are sorted left to right. You know that “within” two of these 12-digit numbers on the list, there is the same 6-digit number and you want to identify it. The 2 challenges are;
A: You do not know the 6-digit number you are looking for, you only know that there is the same 6-digit number in two of your 12-digit numbers in the list and you want to find it.

B: The 6-digit number you’re seeking can be scattered anywhere within its 12-digit number.

Is there a formula to do this?
 
Hi ,

Please upload a workbook with at least half a dozen examples , or post the examples in this same thread.

Post the input data , and the expected output.

Narayan
 
Thank you for replying,

I had to upload my example in a Word doc. I am using Excel 97 and for some reason the "upload" on this website did not recognize my .xls files.

I gave you a list of 59 12-digit numbers and I seeded two of the listings with a 6-digit number: 01-13-35-36-44-50

In reality the list of 12-digit numbers is several thousand and I do not know the 6-digit number I am looking for. I only know that at least two of my 12-digit numbers have the same 6-digit numbers within. I need to identify the 6-digit number I am looking for by surfacing these matches. Its possible other 6-digit matches may result but that's not a problem.
Just to mention again, the 6-digit number may be scattered anywhere within its 12 digit number.

Please ask me anything else you like,

Thanks

Ron
 

Attachments

Hi ,

A very interesting problem , but one which is going to take some time to solve , at least for me.

Can you clarify one point ?

What will be the maximum number within these strings ?

Narayan
 
Hi Bosco ,

Can you re-read the following in the problem description ?
I do not know the 6-digit number I am looking for. I only know that at least two of my 12-digit numbers have the same 6-digit numbers within. I need to identify the 6-digit number I am looking for by surfacing these matches.
Click to expand...
Narayan
 
Hi ,

Yet another question :

What if more than 6 numbers match ?

For example , in the present data , rows 5 and 7 have the following :

13 - 35 - 36 - 44 - 45 - 46 - 50

Narayan
 
Narayan,

It does not matter if there are more than 6. In that case I would assume that the formula would be give me all the 6-digit versions of it and I can make a decision later how to handle it.

The max number would be 59
 
You must log in or register to reply here.
Back
Top