Hey all!
I've got a few problems but they're all related to the same thing I'm trying to do, so I'm just going to group them all together in this post. I'm using Windows 7 and Excel 2010. Sorry in advance for the wall of text!
First problem: I'm having a hard time trying to create a custom sorting list for what I need. I have to sort a set of numbers (let's say 1 through 600. It's not always that high but that's the highest it can get to) that each has a corresponding number with the letters MC in front of it. (So there would be 1 and MC1, 2 and MC2, and so on). I need to be able to sort these as if the MC numbers were just regular numbers. An example of what I mean would be something like: 4, 16, MC49, 56, MC70, MC92, 112 etc. Currently I've been sorting them in a way that puts all the MC numbers on the bottom, and just copying and pasting the rows into the correct positions. This was fine for a while, but this is something I'm going to need to keep doing as part of my job and it can get pretty time consuming.
I attempted to solve this by creating a custom sorting list, but I'm having trouble doing it. I started off by having a sheet where I entered in the number 1 (stored as text) in one cell, followed by MC1 in the next cell, and using auto-fill to follow the pattern to 600 and MC600. I then went into the Excel Options window and opened the Edit Custom Lists window from the "Advanced" menu, and imported a custom list from the cells I got from the auto-fill. I thought this would be the end of my problem, but the list that I ended up creating stops at 127 and MC127, and if what I'm trying to sort goes any higher than that, it just wont sort it right at all. (It goes back to sorting all the numbers at the top, and all the MC numbers at the bottom)
Is there a way to get around this? Is it maybe possible to create a custom sorting list out of a formula that would follow the pattern? I spent a while searching google yesterday trying to find out, but I can't find anything anywhere. Or maybe there's some other way I'm completely not even thinking of that someone else might be able to point out to me, who knows? Not me.
Second problem: When I did figure out how to create a custom sorting list by importing cells, (before I found out it only works up to 127) it initially didn't work because each of the numbers always has a line after it saying Sheet 1 through Sheet 30. I'm having a hard time wording it so that doesn't sound confusing, so here's what it would look like:
1
Sheet 1
As well as not working when the numbers got higher than 127, it did not work when I left the Sheet # in there. I ended up removing the sheet number from each of the cells I was trying to sort, and it worked then. However, it was a huge pain going back and adding that line of text in afterwards when it would be much easier to sort it with that line already there. Is there a way to sort the cells by the first line of text only? Or would it be easiest to create a custom sorting list for each sheet number? (There's never an instance where numbers from sheet 1 and sheet 2 would be sorted together, for example).
Third problem: This one isn't a huge problem, and is less about custom sorting and more about cell formatting. As I've said before, each cell has a number as the first line and the sheet number as the second. The number though has to be bolded and a size 12 font, while the sheet number would be regular and a size 10 font. Currently I'm starting out by having the whole cell as regular and size 10, and going through and changing the numbers afterwards. This doesn't take up that much time, so I'm not as worried about fixing it, but I was wondering if there was a way to format cells so that the first line will automatically be bolded and size 12, and the second line will be size 10?
Anyway, that's everything. Hopefully I was concise enough that I don't just sound like a crazy person rambling about her problems. If there's anything I wrote that doesn't make sense or needs clarifying, just let me know and I'll try to explain myself better. I'll probably keep this thread open until my day's over or until I either get an answer from somebody or get told that what I'm trying to do is just impossible or something.
Thanks for reading
I've got a few problems but they're all related to the same thing I'm trying to do, so I'm just going to group them all together in this post. I'm using Windows 7 and Excel 2010. Sorry in advance for the wall of text!
First problem: I'm having a hard time trying to create a custom sorting list for what I need. I have to sort a set of numbers (let's say 1 through 600. It's not always that high but that's the highest it can get to) that each has a corresponding number with the letters MC in front of it. (So there would be 1 and MC1, 2 and MC2, and so on). I need to be able to sort these as if the MC numbers were just regular numbers. An example of what I mean would be something like: 4, 16, MC49, 56, MC70, MC92, 112 etc. Currently I've been sorting them in a way that puts all the MC numbers on the bottom, and just copying and pasting the rows into the correct positions. This was fine for a while, but this is something I'm going to need to keep doing as part of my job and it can get pretty time consuming.
I attempted to solve this by creating a custom sorting list, but I'm having trouble doing it. I started off by having a sheet where I entered in the number 1 (stored as text) in one cell, followed by MC1 in the next cell, and using auto-fill to follow the pattern to 600 and MC600. I then went into the Excel Options window and opened the Edit Custom Lists window from the "Advanced" menu, and imported a custom list from the cells I got from the auto-fill. I thought this would be the end of my problem, but the list that I ended up creating stops at 127 and MC127, and if what I'm trying to sort goes any higher than that, it just wont sort it right at all. (It goes back to sorting all the numbers at the top, and all the MC numbers at the bottom)
Is there a way to get around this? Is it maybe possible to create a custom sorting list out of a formula that would follow the pattern? I spent a while searching google yesterday trying to find out, but I can't find anything anywhere. Or maybe there's some other way I'm completely not even thinking of that someone else might be able to point out to me, who knows? Not me.
Second problem: When I did figure out how to create a custom sorting list by importing cells, (before I found out it only works up to 127) it initially didn't work because each of the numbers always has a line after it saying Sheet 1 through Sheet 30. I'm having a hard time wording it so that doesn't sound confusing, so here's what it would look like:
1
Sheet 1
As well as not working when the numbers got higher than 127, it did not work when I left the Sheet # in there. I ended up removing the sheet number from each of the cells I was trying to sort, and it worked then. However, it was a huge pain going back and adding that line of text in afterwards when it would be much easier to sort it with that line already there. Is there a way to sort the cells by the first line of text only? Or would it be easiest to create a custom sorting list for each sheet number? (There's never an instance where numbers from sheet 1 and sheet 2 would be sorted together, for example).
Third problem: This one isn't a huge problem, and is less about custom sorting and more about cell formatting. As I've said before, each cell has a number as the first line and the sheet number as the second. The number though has to be bolded and a size 12 font, while the sheet number would be regular and a size 10 font. Currently I'm starting out by having the whole cell as regular and size 10, and going through and changing the numbers afterwards. This doesn't take up that much time, so I'm not as worried about fixing it, but I was wondering if there was a way to format cells so that the first line will automatically be bolded and size 12, and the second line will be size 10?
Anyway, that's everything. Hopefully I was concise enough that I don't just sound like a crazy person rambling about her problems. If there's anything I wrote that doesn't make sense or needs clarifying, just let me know and I'll try to explain myself better. I'll probably keep this thread open until my day's over or until I either get an answer from somebody or get told that what I'm trying to do is just impossible or something.
Thanks for reading
Last edited:
