Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Challenge for all! Sort this!
ladies and gentlemen i have a challenge for you all. i wish to sort cattle details using excel. i download the details from the department of agriculture as follows; UK 9 12345 678 6 UK 9 12345 679 7 UK 9 12345 680 1 UK 9 12345 681 2 where the "UK 9" is constant, the "12345" is the herd number of animals-which i do not wish to sort, the "678" is the individual animal's number, which i wish the animals to be sorted in order of, and the "1"-"7" is a "check number", every animal has the random number 1-7 at the end of their number. As you can see, there are spaces between each different bit of information. you may think that this is easy, but what makes it more complicated is; the herd number, (e.g. 12345) is not necessarily 5 digits long, it can range from about 4 to 8 digits, also in my herd of cattle there is a wide variety of herd numbers, therefore not all 5 digits herd numbers are the same. to make matters more complicated, there are also animals in my herd which are in a slightly different format; 314159-180-N 314159-181-O 314159-182-P 314159-183-Q where there is no "UK 9" the "314159" is the herd number of animals-which i do not wish to sort, the "180" is the individual animals number, which i wish the animals to be sorted in order of, and the "A-Z" is a "check letter", every animals has the random letter "A-Z" at the end of their number. As you can see, there are "-" between each different bit of information. if you would be able to tell me how to sort the animals numbers i would greatly appriciate it. if i cannot arrange both formats of data in order of animals number at the same time, but can arrange each format of data in order of animals number individually, i would still greatly appriciate it! Thanks a million Gerard -- Gerard ------------------------------------------------------------------------ Gerard's Profile: http://www.excelforum.com/member.php...o&userid=28087 View this thread: http://www.excelforum.com/showthread...hreadid=475926 |
#2
|
|||
|
|||
by the way, i use microsoft excel 2000, though if you could tell me how to do it in any version i think i could work it out! :) -- Gerard ------------------------------------------------------------------------ Gerard's Profile: http://www.excelforum.com/member.php...o&userid=28087 View this thread: http://www.excelforum.com/showthread...hreadid=475926 |
#3
|
|||
|
|||
sorry for posting three times in a row, but if you want to e-mail me, e-mail; or thanks, Gerard -- Gerard ------------------------------------------------------------------------ Gerard's Profile: http://www.excelforum.com/member.php...o&userid=28087 View this thread: http://www.excelforum.com/showthread...hreadid=475926 |
#4
|
|||
|
|||
If all of the cows have a three digit number and your check number is always
1 digit try a helper column with =right(A1,5) copy down and sort all on this column if the cows can have other than 3 digits, it gets more complicated but not much, "Gerard" wrote: ladies and gentlemen i have a challenge for you all. i wish to sort cattle details using excel. i download the details from the department of agriculture as follows; UK 9 12345 678 6 UK 9 12345 679 7 UK 9 12345 680 1 UK 9 12345 681 2 where the "UK 9" is constant, the "12345" is the herd number of animals-which i do not wish to sort, the "678" is the individual animal's number, which i wish the animals to be sorted in order of, and the "1"-"7" is a "check number", every animal has the random number 1-7 at the end of their number. As you can see, there are spaces between each different bit of information. you may think that this is easy, but what makes it more complicated is; the herd number, (e.g. 12345) is not necessarily 5 digits long, it can range from about 4 to 8 digits, also in my herd of cattle there is a wide variety of herd numbers, therefore not all 5 digits herd numbers are the same. to make matters more complicated, there are also animals in my herd which are in a slightly different format; 314159-180-N 314159-181-O 314159-182-P 314159-183-Q where there is no "UK 9" the "314159" is the herd number of animals-which i do not wish to sort, the "180" is the individual animals number, which i wish the animals to be sorted in order of, and the "A-Z" is a "check letter", every animals has the random letter "A-Z" at the end of their number. As you can see, there are "-" between each different bit of information. if you would be able to tell me how to sort the animals numbers i would greatly appriciate it. if i cannot arrange both formats of data in order of animals number at the same time, but can arrange each format of data in order of animals number individually, i would still greatly appriciate it! Thanks a million Gerard -- Gerard ------------------------------------------------------------------------ Gerard's Profile: http://www.excelforum.com/member.php...o&userid=28087 View this thread: http://www.excelforum.com/showthread...hreadid=475926 |
#5
|
|||
|
|||
Several questions need answers to affect a solution: - Are the numbers you import (e.g. UK 9 12345 678 6) placed into a single cell? - You don't want to sort herd numbers, so you want each individual animal's number sorted within a grouping of a specific herd number (as in your example-all of herd A, 001-999 then all of herd B, 001-999) --or-- sort by animal number (001-999) regardless of herd number? - Can two or more animals have the same animal number, but different 'random' numbers (e.g. 678 5; 678 6; 678 Z)? I envision using Text to Columns to separate your numbers into columns. You would have to do this in phases, as some numbers have spaces as delimiters and other have dashes. Then line up your columns, as some have the UK 9, some don't. Then applying auto-filter and then sorting by animal number. More info, please -- swatsp0p ------------------------------------------------------------------------ swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101 View this thread: http://www.excelforum.com/showthread...hreadid=475926 |
#6
|
|||
|
|||
Gerard,
If I understood correctly, you only want to sort by the animal number only and disregard all other information, incl. the check number/letter. If this is so, the following formula, tested against your data, has isolated the animal number for both types of codes: =IF(ISNUMBER(FIND("-",A1)), MID(A1,FIND("-",A1)+1,FIND("-",A1,FIND("-",A1)+1)-FIND("-",A1)-1),MID(A1,FIND(" ",A1,6)+1, FIND(" ",A1,FIND(" ",A1,6)+1)-FIND(" ",A1,6)-1)) With this helper column you should now be able to sort. Write back if you need something else. HTH Kostis Vezerides |
#7
|
|||
|
|||
On Thu, 13 Oct 2005 12:07:36 -0500, Gerard
wrote: ladies and gentlemen i have a challenge for you all. i wish to sort cattle details using excel. i download the details from the department of agriculture as follows; UK 9 12345 678 6 UK 9 12345 679 7 UK 9 12345 680 1 UK 9 12345 681 2 where the "UK 9" is constant, the "12345" is the herd number of animals-which i do not wish to sort, the "678" is the individual animal's number, which i wish the animals to be sorted in order of, and the "1"-"7" is a "check number", every animal has the random number 1-7 at the end of their number. As you can see, there are spaces between each different bit of information. you may think that this is easy, but what makes it more complicated is; the herd number, (e.g. 12345) is not necessarily 5 digits long, it can range from about 4 to 8 digits, also in my herd of cattle there is a wide variety of herd numbers, therefore not all 5 digits herd numbers are the same. to make matters more complicated, there are also animals in my herd which are in a slightly different format; 314159-180-N 314159-181-O 314159-182-P 314159-183-Q where there is no "UK 9" the "314159" is the herd number of animals-which i do not wish to sort, the "180" is the individual animals number, which i wish the animals to be sorted in order of, and the "A-Z" is a "check letter", every animals has the random letter "A-Z" at the end of their number. As you can see, there are "-" between each different bit of information. if you would be able to tell me how to sort the animals numbers i would greatly appriciate it. if i cannot arrange both formats of data in order of animals number at the same time, but can arrange each format of data in order of animals number individually, i would still greatly appriciate it! Thanks a million Gerard I'm just learning about "regular expressions" and they seem ideal for this. Of course, you have to install Longre's free morefunc.xll add-in (available at http://xcell05.free.fr/). The following formula will extract the second to last "word" of your data, whether separated by <space or "-" 's. =REGEX.MID(A1,"[^[:punct:]\s]+",-2) Given your examples, it returns: 314159-180-N 314159-181-O 314159-182-P 314159-183-Q UK 9 12345 678 6 UK 9 12345 679 7 UK 9 12345 680 1 UK 9 12345 681 2 To extract the second to last word using built-in Excel functions is certainly possible, but it's a bit less flexible. If your animal numbers are always three digits, and the check number or letter follows and is only one character, then the simple: =MID(A1,LEN(A1)-4,3) will work. But more complex situations may be more difficult, depending on your ranges. In either of the above, you then sort on your "helper column". --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Select rows and sort based on type | Excel Discussion (Misc queries) | |||
Conform a total to a list of results? | Excel Discussion (Misc queries) | |||
Conform a total to a list of results? | Excel Worksheet Functions | |||
sorting more than 3 keys | Excel Discussion (Misc queries) | |||
"-" ignored in sort | Excel Discussion (Misc queries) |