Sort data in specific alphanumerical order

Do you mean?

Please Login or Register  to view this content.

Hi Jindon,

I apologize for late response, and thank you for your help, as usual.

I tried the code, it worked well to keep the sort within the "current area", as needed. However, since my intent is to also sort lists that start and end at different locations, became obvious the code needs some adjustments (can't reference column "A" when the list is located at a different cell/column). My guess is it's all about setting the "Range" properly?

The attached sample book contains 3 lists located randomly. Let's say I'd like to sort the one where the first record to be sorted is located at H11. How would the 'Range' be stated so Excel inserts the extra column, counts the rows to insert the formula, and then sorts on the records contained?

I tried to come up with something, but the below code is as far as I could go (assuming I click on the first record I need sorted so Excel 'knows' where my list starts). Pretty sure there might be a better way to start with? ...

Please Login or Register  to view this content.

Thank so much for your help.

Hi Bakerman2

I appreciate you took the time to help me.

I tried your code and really liked the 'InputBox' command to set the range to be sorted. However, I got an error message saying "Run-time error '1004'" The sort reference is not valid. Make sure that it's within the date you want to sort, and the first 'Sort By box isn't the same or blank."

I have no idea what that means to be honest, but the macro stopped when trying to run this line:

Please Login or Register  to view this content.

Not sure how to fix that?

Bakerman,

I tried the code with the 3 tables contained in the sample book but still get the same error message. I'm using Excel 2016. Not sure if that might be a reason behind the code not working? ...

My code is fully based on your file/code.
If range is different, neet to see your file.

I' out at the moment, so reply will be late.

Hi Jindon,

Thanks for your response. The first message of this thread contains a sample book with 3 tables, each located randomly. If you please download that file, you'll see what I'm talking about.

No problem for a late response. Your help is highly appreciated.

Bakerman,

YES! The code works as wanted. I'll do further testing tomorrow at work and will come back to report on that.

Thanks!

Do you mean like this?

Please Login or Register  to view this content.

Bakerman,

I'm finally back. Sorry for delayed response. I tested your code extensively at work. It works as intended. However, I'm not sure if due to being using Excel 2016, but it happened sometimes, that it didn't sort the data as expected. In fact, it didn't do anything at all. The weirdest part is that I got no error message of any kind. Once a sheet got, let me use the word, "altered", there was no way the code did anything. Now, I'm not saying your code has an issue. I actually have no idea why that happens, but that lead me to a question.

The main reason I'm inserting the formula "=LEFT(RC[-1],FIND(""-"",RC[-1])-1)&LEN(RC[-1])-3", is because I need the data sorted in a specific alphanumerical order, and not in the built-in sort in Excel. Since this post was intended to solve sorting within a certain area, I'll go ahead and post a new thread to address that.

In the meantime, I'm adding reputation to you and giving you a BIG thank you for your help

Jindon,

The code you posted actually finds the SKU and does sort the data, however, it sorts it in the Excel built-in sort, and not in the alphanumerical sort I actually need it. Since the goal of this post has been achieved, I'm posting a new thread to ask specifically about the best way to achieve the needed sort. As I mentioned to Bakerman, seems like the code sometimes get ignored and I have no idea why.

I'll add a link to the new post here.

In the meantime, thanks for your help!

Bakerman and Jindon,

I started a new thread that I tittle the same as this one (didn't realize it until I hit the 'post' button!). Sort data in specific alphanumerical order. If you're so kind to take a look at it, it will be highly appreciated.

Thanks!