• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Custom sort accross several columns

D

dourpil

Member
Hi !

See attached workbook: I wrote a macro to sort a table, but my code isn't robust enough to work in every situation.

I want my table filtered by batch number (A to Z) and within those batch numbers, I want the product order "EM", "AA", "G" to be respected. However: sometimes we have several "EM", "AA" and "G" products per batch. In those cases, I want to respect the product order using the comment column. This works when all 3 comments are identical (see batch 17A25N13 with the comments that start with "A 02"), but doesn't work when they're not (see the other lines for 17A25N13, or batch 17A25N14)

I should specify that, everytime there is more than 3 lines of the same batch, there is always a comment that will help sort the thing.

Any idea how to have the code work every time ? Thanks !
 

Attachments

Last edited:
Did You 'sort manually' or how those comments?
Left Side Your 'DESIRED OUTPUT' and right side 'sorted comments by Excel'.
Screen Shot 2017-03-14 at 09.36.38.png
 
vletm said:
Did You 'sort manually' or how those comments?
Left Side Your 'DESIRED OUTPUT' and right side 'sorted comments by Excel'.
Click to expand...

Hello vletm! I'm not sure I understand your question; but I did manually sort the table on the right side (by inserting & deleting rows and using copy paste)

The table on the left side shows how my current macro does the sorting.

-

I see; there is indeed a small data entry mistake:
"A 01H" vs "A 01 1 H"

Usually this shouldn't happen.


One way I tried to achieve my goal, was to insert a helper column with the formula:
"=LEFT([@[comment]],4)"

And instead of sorting the comment column at some point in my macro, I'd sort the helper column. But that didn't work for cases like 17A25N14.
 
Maybe You would focus,
it's challenge to get same 'wrong order' ...
but if 'my order' is okay then ...
this sorting is possible to do 'my way'.
> What is Your the most correct 'desired output'?
Could You do it manually and upload here?
 
The correct ouptut is what I've put in the "desired output" table: I need to respect the sort order "EM", "AA" and then "G" for each batch number.

On the printscreen from your 1st reply, the corresponding values in the column 'product' would be "G" then "AA" then "EM"
 
You must log in or register to reply here.
Back
Top