Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help extracting data
I need to extract some data from a cell please let me know any suggestion you
may have for a formula. Right now in a cell I have the number 50323113211. In a database I have...I know that the number that needs to be extracted from this particular number is 503. What formula can I use to extract this so that the 23113211 remains? I have a list of 50,000 entries and the number varies. So in another cell i may have 1342234 and in the next cell I am told I need to pull the 1 so that the 343324 remains. Now once the first part of this is done... I need from database range to pull out the number it finds. So for example, let's say for the example of 23113211, there is a range in cells that has for example 23, 24, 25, 26. I then need this range to be checked with the 23113211 and then pull out the match. So in this case it finds that 23 is the match and then pulls it out and leaves me with 113211. I know this is omplicated and confusing. Any thoughts? Thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help extracting data
I don't see what rules you are applying to get your derived numbers.
How do you know that 503 has to be removed (leaving 8 digits) or 1 in your second example (leaving 6 digits)? You say "... in the next cell I am told I need to pull the 1 ..." - who tells you? Please try to describe your problem more accurately - what do you have in terms of columns used and values in them and what do you want to achieve from these values? Pete A. S. wrote: I need to extract some data from a cell please let me know any suggestion you may have for a formula. Right now in a cell I have the number 50323113211. In a database I have...I know that the number that needs to be extracted from this particular number is 503. What formula can I use to extract this so that the 23113211 remains? I have a list of 50,000 entries and the number varies. So in another cell i may have 1342234 and in the next cell I am told I need to pull the 1 so that the 343324 remains. Now once the first part of this is done... I need from database range to pull out the number it finds. So for example, let's say for the example of 23113211, there is a range in cells that has for example 23, 24, 25, 26. I then need this range to be checked with the 23113211 and then pull out the match. So in this case it finds that 23 is the match and then pulls it out and leaves me with 113211. I know this is omplicated and confusing. Any thoughts? Thanks. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help extracting data
The common thead I see in the description seems to be that given some number
(503, 1, 23, 24, 25, 26) that if those are the first n-number of digits of a particular value, then they are to be removed from it. From the first paragraph: remove the leading 'n' number of digits given some input as 503 or 1 and then pull out the number that matches with that. For the second half, you take what was left over from the first step and compare the first 'n' characters in it to a list and if a match of the first n digits in the long number matches one of the numbers in the list of shorter ones, strip those off of the longer one. But I'm not sure why, in the first step you need to pull from the 'database' unless it is just to verify that whatever was left over after removing the 503 or 1 is actually in the database? "Pete_UK" wrote: I don't see what rules you are applying to get your derived numbers. How do you know that 503 has to be removed (leaving 8 digits) or 1 in your second example (leaving 6 digits)? You say "... in the next cell I am told I need to pull the 1 ..." - who tells you? Please try to describe your problem more accurately - what do you have in terms of columns used and values in them and what do you want to achieve from these values? Pete A. S. wrote: I need to extract some data from a cell please let me know any suggestion you may have for a formula. Right now in a cell I have the number 50323113211. In a database I have...I know that the number that needs to be extracted from this particular number is 503. What formula can I use to extract this so that the 23113211 remains? I have a list of 50,000 entries and the number varies. So in another cell i may have 1342234 and in the next cell I am told I need to pull the 1 so that the 343324 remains. Now once the first part of this is done... I need from database range to pull out the number it finds. So for example, let's say for the example of 23113211, there is a range in cells that has for example 23, 24, 25, 26. I then need this range to be checked with the 23113211 and then pull out the match. So in this case it finds that 23 is the match and then pulls it out and leaves me with 113211. I know this is omplicated and confusing. Any thoughts? Thanks. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help extracting data
I worked up some formulas to strictly deal with the problem as I interpreted
it earlier. You can see the approach I took in this workbook: http://www.jlathamsite.com/uploads/BreakItDown.xls although I don't think that's going to be a practical solution for the 50,000 entry problem? I'm betting that there are going to be 40000+ combinations to deal with <g and that makes things really tough. It's possible that a coding solution might work, but as Pete_UK has accurately stated: more definition of how things work is really needed. "Pete_UK" wrote: I don't see what rules you are applying to get your derived numbers. How do you know that 503 has to be removed (leaving 8 digits) or 1 in your second example (leaving 6 digits)? You say "... in the next cell I am told I need to pull the 1 ..." - who tells you? Please try to describe your problem more accurately - what do you have in terms of columns used and values in them and what do you want to achieve from these values? Pete A. S. wrote: I need to extract some data from a cell please let me know any suggestion you may have for a formula. Right now in a cell I have the number 50323113211. In a database I have...I know that the number that needs to be extracted from this particular number is 503. What formula can I use to extract this so that the 23113211 remains? I have a list of 50,000 entries and the number varies. So in another cell i may have 1342234 and in the next cell I am told I need to pull the 1 so that the 343324 remains. Now once the first part of this is done... I need from database range to pull out the number it finds. So for example, let's say for the example of 23113211, there is a range in cells that has for example 23, 24, 25, 26. I then need this range to be checked with the 23113211 and then pull out the match. So in this case it finds that 23 is the match and then pulls it out and leaves me with 113211. I know this is omplicated and confusing. Any thoughts? Thanks. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help extracting data
The end purpose is to know how many digits are left. I am breaking apart the
data so for the example, 50323113211, I know that 503 can be removed due to a series of calculations prior. I have set it up so that I know, initially, what exactly needs to be taken out. With that said, I am then left with 23113211. Now, from there, I need to lookup from a database, if any of the numbers exist. So in that database, there may be 23, 25, 56, 178. In this case, it searches and finds that 23 matches, so it then pulls it out and leaves me with 1134211 and then I count the number of digits, 7. Now, this is they key for me, since I am trying to verify this. I hope this clears some things up. JLatham, I will take a look at the excel file you had created and see if that helps me do what I need to. Thanks. "JLatham" wrote: I worked up some formulas to strictly deal with the problem as I interpreted it earlier. You can see the approach I took in this workbook: http://www.jlathamsite.com/uploads/BreakItDown.xls although I don't think that's going to be a practical solution for the 50,000 entry problem? I'm betting that there are going to be 40000+ combinations to deal with <g and that makes things really tough. It's possible that a coding solution might work, but as Pete_UK has accurately stated: more definition of how things work is really needed. "Pete_UK" wrote: I don't see what rules you are applying to get your derived numbers. How do you know that 503 has to be removed (leaving 8 digits) or 1 in your second example (leaving 6 digits)? You say "... in the next cell I am told I need to pull the 1 ..." - who tells you? Please try to describe your problem more accurately - what do you have in terms of columns used and values in them and what do you want to achieve from these values? Pete A. S. wrote: I need to extract some data from a cell please let me know any suggestion you may have for a formula. Right now in a cell I have the number 50323113211. In a database I have...I know that the number that needs to be extracted from this particular number is 503. What formula can I use to extract this so that the 23113211 remains? I have a list of 50,000 entries and the number varies. So in another cell i may have 1342234 and in the next cell I am told I need to pull the 1 so that the 343324 remains. Now once the first part of this is done... I need from database range to pull out the number it finds. So for example, let's say for the example of 23113211, there is a range in cells that has for example 23, 24, 25, 26. I then need this range to be checked with the 23113211 and then pull out the match. So in this case it finds that 23 is the match and then pulls it out and leaves me with 113211. I know this is omplicated and confusing. Any thoughts? Thanks. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help extracting data
JLatham,
After looking at the example you had given, I think we are on the right track. A question I wanted to ask was, the second check that it is doing, is that based on number length? If so, then this will not work, since number length varies. To explain, if we go back to the example of 50323113211, and we search the second part for 23, 24, etc., the number length may be different so it could be that the number is 5039113211 and it needs to find the 9 which is in the database. I.e. the number lengths in the database vary. "JLatham" wrote: I worked up some formulas to strictly deal with the problem as I interpreted it earlier. You can see the approach I took in this workbook: http://www.jlathamsite.com/uploads/BreakItDown.xls although I don't think that's going to be a practical solution for the 50,000 entry problem? I'm betting that there are going to be 40000+ combinations to deal with <g and that makes things really tough. It's possible that a coding solution might work, but as Pete_UK has accurately stated: more definition of how things work is really needed. "Pete_UK" wrote: I don't see what rules you are applying to get your derived numbers. How do you know that 503 has to be removed (leaving 8 digits) or 1 in your second example (leaving 6 digits)? You say "... in the next cell I am told I need to pull the 1 ..." - who tells you? Please try to describe your problem more accurately - what do you have in terms of columns used and values in them and what do you want to achieve from these values? Pete A. S. wrote: I need to extract some data from a cell please let me know any suggestion you may have for a formula. Right now in a cell I have the number 50323113211. In a database I have...I know that the number that needs to be extracted from this particular number is 503. What formula can I use to extract this so that the 23113211 remains? I have a list of 50,000 entries and the number varies. So in another cell i may have 1342234 and in the next cell I am told I need to pull the 1 so that the 343324 remains. Now once the first part of this is done... I need from database range to pull out the number it finds. So for example, let's say for the example of 23113211, there is a range in cells that has for example 23, 24, 25, 26. I then need this range to be checked with the 23113211 and then pull out the match. So in this case it finds that 23 is the match and then pulls it out and leaves me with 113211. I know this is omplicated and confusing. Any thoughts? Thanks. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help extracting data
Well, somehow we need to be able to tell how many leading characters (digits)
to examine to match in the different sections of your database. Yes, at the moment it is set on just matching 2 characters - best guess I could come up with given your example data. If there are columns where we can look to find the characters to remove or an indicator of how many to examine remove, then we can probably move on with this another step or two. I think after we have one or two more 'general rules' in place, then I can probably turn you loose and you can work out succeeding steps on your own. But if that turns out not to be the case, I'll hang in there with you. "A.S." wrote: JLatham, After looking at the example you had given, I think we are on the right track. A question I wanted to ask was, the second check that it is doing, is that based on number length? If so, then this will not work, since number length varies. To explain, if we go back to the example of 50323113211, and we search the second part for 23, 24, etc., the number length may be different so it could be that the number is 5039113211 and it needs to find the 9 which is in the database. I.e. the number lengths in the database vary. "JLatham" wrote: I worked up some formulas to strictly deal with the problem as I interpreted it earlier. You can see the approach I took in this workbook: http://www.jlathamsite.com/uploads/BreakItDown.xls although I don't think that's going to be a practical solution for the 50,000 entry problem? I'm betting that there are going to be 40000+ combinations to deal with <g and that makes things really tough. It's possible that a coding solution might work, but as Pete_UK has accurately stated: more definition of how things work is really needed. "Pete_UK" wrote: I don't see what rules you are applying to get your derived numbers. How do you know that 503 has to be removed (leaving 8 digits) or 1 in your second example (leaving 6 digits)? You say "... in the next cell I am told I need to pull the 1 ..." - who tells you? Please try to describe your problem more accurately - what do you have in terms of columns used and values in them and what do you want to achieve from these values? Pete A. S. wrote: I need to extract some data from a cell please let me know any suggestion you may have for a formula. Right now in a cell I have the number 50323113211. In a database I have...I know that the number that needs to be extracted from this particular number is 503. What formula can I use to extract this so that the 23113211 remains? I have a list of 50,000 entries and the number varies. So in another cell i may have 1342234 and in the next cell I am told I need to pull the 1 so that the 343324 remains. Now once the first part of this is done... I need from database range to pull out the number it finds. So for example, let's say for the example of 23113211, there is a range in cells that has for example 23, 24, 25, 26. I then need this range to be checked with the 23113211 and then pull out the match. So in this case it finds that 23 is the match and then pulls it out and leaves me with 113211. I know this is omplicated and confusing. Any thoughts? Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ranking query | Excel Discussion (Misc queries) | |||
From several workbooks onto one excel worksheet | Excel Discussion (Misc queries) | |||
Extracting data from two columns | Excel Discussion (Misc queries) | |||
Help PLEASE! Not sure what answer is: Match? Index? Other? | Excel Worksheet Functions | |||
Sort pages? | Excel Discussion (Misc queries) |