sort alphabetically and numerically, then sort rows

**luke20allen** · 09-19-2008, 08:23 AM

I have been on here before trying to solve an issue that I have with a file that I am trying to make. I am trying to sort multiple columns of data by the letters contained within them and then by the numbers that are in them. After that is executed I would then like to take the sorted information and filter it even further by keeping all of the like number in the same row all the way across, if certain columns do not have the numbers other columns have I would like for a black fill to appear in that box. Can anyone help me out... I have attached a file to better explain what I am trying to accomplish the unsorted tab is the before and the sorted tab is the result that I am looking for. Thanks

**luke20allen** · 09-22-2008, 08:46 AM

Is this even possible.....? I was on the general excel forum and they told me to come here to see if this forum could be more help..... Let me know. Thanks.

**MatrixMan** · 09-22-2008, 01:57 PM

Hi Luke -

This probably isn't the most elegant solution, but it should get you out of trouble for now .. Select your unsorted sheet, then run this and it will add a sheet at the end and sort per your example. I've reposted your file with the code in it.

I did find two cases where there are 5 values that are equal, not just 4 as in your sorted sheet, but perhaps that's just this test data ... Also - you have to remove the blank rows at the top and I've assumed a header row, but I'm sure you can tidy that up if you need to.

Hope it helps anyway ...

MM.

Please Login or Register  to view this content.

**MatrixMan** · 09-23-2008, 01:45 PM

Hi Luke - here's the modified code to cater for however many columns of data you have. I'm afraid I can't guess why you got the dataoption1:= error .. anyone? In any case, it's not necessary, so I've removed it.

Please Login or Register  to view this content.

I've also attached the workbook I did the testing in with your data. As I mentioned in the original, it's not a particularly elegant solution and is actually a bit inefficient, but I hope it helps.

Cheers, MM.

**MatrixMan** · 09-27-2008, 08:48 AM

Hi Luke -

(for anyone else following this thread, I received a more detailed explanation of what needed to happen, which basically meant that the data needed to remain associated with it's original header; the other issue was that this is being run on a large data set and was hanging...)

I've completely reworked this so that the sorting is now all done in a series of arrays rather than in a sheet, which makes it run much faster and should mean you won't have the hanging issue any more. On the dataset you sent me, it runs on my machine for the largest dataset in less than a second.

In the sort process, I've kept the data in each column associated with its original header as you explained to me ... Anyway - see below & let me know if it's what you need.

Please Login or Register  to view this content.

**luke20allen** · 09-29-2008, 09:11 AM

Here is the version that you sent to me in the e-mail. Hope you can help me out, I am running on Excel 2000. Thanks

**MatrixMan** · 09-29-2008, 09:22 AM

Hi Luke - I just opened & ran it and it worked fine ... Since you're on XL2000, perhaps lookup your VBA help under Sort and see what the allowable values are for dataoption1 ... it may have changed in 8 years

**MatrixMan** · 10-01-2008, 07:00 AM

Hi Luke, I had a thought on this issue where I used the following in the initial column sort command:

Please Login or Register  to view this content.

... which doesn't exist in Excel 2000 that you're running. I think, though, that if you change the comparison after the sort to change the values using the Asc converter, then it should work OK. Something like this, with the conversion shown in red:

Please Login or Register  to view this content.

It does take a bit longer to run than my original code, but at least it will run on your version. Hope that helps ... MM.

sort alphabetically and numerically, then sort rows

LinkBack

Thread Tools

Rate This Thread

Display

sort alphabetically and numerically, then sort rows

it is possible ...

expanding columns solution

keeping data with original columns ..

Adjustment of code for XL2000 compatibility.

Thread Information

Users Browsing this Thread

Similar Threads

I need to sort alphabetically and then numerically

Bookmarks

Bookmarks

Posting Permissions