Sort capital letters before lower case?

[Tinwelende]

Is there a way to make Exel rearrange the order of letters in a cell, so that no matter what it says, the capital letter will always come first? (The cells contain formulas, and they need to stay intact, but the cells, or what's showed in the cells needs to be rearranged).

An example of a row of cells is this (every letter is in one cell separately)

A a b b C c d D E E F F g g

The A's need to be sorted amongst themselves, the b's amongst themselves, and so on, so that I get this:

A a b b C c D d E E F F g g (either in one merged cell, or in separate cells)

THOUGH, in a couple of cells I will have "ToY" or "toY" and in this case, it needs to sort them only by the T's and let the rest follow as it is (the Y is always in capital and needs to be at the end).

Is it possible to to this by an "if"-formula or something and not by macros?

I could also merge the cells that need to be sorted (so that we from the beginning have "aA" in one cell etc.), if it's easier to sort it that way. Though, having "toYToY" in a cell like this I guess might complicate things, won't it?

[Ace_XL]

In a separate column use this formula

=CODE(LEFT(A1,1))/32+MOD(CODE(LEFT(A1,1)),32)

Copy down and sort by this column

[Tinwelende]

Hmmm... it gives me an error. I had my first data in A1 and the second one in B2. Does that have anything to do with it? I pasted the formula into C1.

Here's a workbook of what I want:

Workbook sorting.xlsx

On sheet 2 I've added an example of my actual data (though, I've got 13 000 rows of this).

[aydeegee]

[Posting deleted]

[aydeegee]

Hi Tinwelende

The attached sheet shows how to sort one row at a time. Does this give you something to work on? (It only works for the first character so you will have to find some other way of playing with your Toys. (English joke )

Regards
Alastair

[Tinwelende]

Thanks for your help! Though, I didn't really manage to get it working, plus my data is arranged in another way.

I've added a workbook with an example of my data, if you or anyone wants to take a look:

Sorting Workbook.xlsx

[aydeegee]

Hi Tinwelende

If you do not mind a bit of VBA the attached workbook will do as you ask.

I have paired up the cells, but after the sort. I now have had the thought that if you had:

aAAa.... this would give AA aa under my sort-then-pair system but Aa Aa under a pair-then-sort system. Do I need to have a re-write or could this scenario not happen?

Regards
Alastair

[xladept]

I've probably got the same solution as aydeegee but, for this switching algorithm, you need:

Please Login or Register  to view this content.

Directions for running the routine(s) just supplied

Copy the code to the clipboard

Press ALT + F11 to open the Visual Basic Editor.

Open a macro-enabled Workbook or save your Workbook As Macro-Enabled

Select “Module” from the Insert menu

Type "Option Explicit" then paste the code under it

With the cursor between Sub and End Sub press F5 (F8 to Single Step)

OR

Press ALT + Q to close the code window.

Press ALT + F8 then double click on the macro name

[aydeegee]

@xladept Your way is much more succinct - and deals with the pairing. I used comparing Excel cells, but found that "a" = "A" - your way gets round this.

I try to learn something everyday. I can now go back to bed.

Question - what does the

Option Compare Binary

do. It seems to be redundant as the macro runs quite well without it.

Regards
Alasatair

[Tinwelende]

Thank you so much for your time to both of you, xladept and aydeegee! I tried running the macro the way you told me to, xladept, but the only thing that happened was that I saw that the macro started up the first time I hit F5 (and then "Run"), but I didn't see the result ending up anywhere. Am I doing something wrong? Isn't itsupposed to create a new sheet with the results?

And - using macros - will others be able to open up the document as well and just type anything into the cells on the first sheet so that all the values in the other cells will change as they do now, and the macro will always be sorting no matter what, or will they have to always open and run the macro manually for the sorting to occur? I need a document where all you do is fill in a couple of cells on the first sheet and you immediately get the result, without having to do anything else.

[aydeegee]

Hi Tinwelende

xladept's macro works for me. You say

I saw that the macro started up the first time I hit F5 (and then "Run")

This is not what I would have expected (there should not have been a "Run" option).

Now that you have loaded the macro just press Ctrl + F8 then double click SORTIN. The macro looks at each pair of cells and, if necessary, swaps them round - no new sheet.

Others can indeed run the macro, but they will need to have the security level set correctly. To do this go to
File > Options > Trust Center > Trust Center Settings > Radio button: Disable all macros with notification.

(I wonder whether this is why you cannot see the results?)

This will give them the option of allowing macros to run on their computer.

Yes, they will be able to run the macro after entering a couple of cells (but xladept's macro stops at row 29)

I am not trying to muscle in on xladept's solution - just trying to be helpful.

Regards
Alastair

[Tinwelende]

When I hit Ctrl and F8 I get a sign saying: "The row is not an executable statement".

Though, am I supposed to mark the cells I want sorted before I go to the macro? No matter if I do or if I just have the sheet active, I get this message when I hit Ctrl and F8. If I hit F5 I get the "SORTIN"-option, but nothing happens when I double click it.

The option "Disable all macros with notification" was already ticked.

[jindon]

Try the attached.

Just change any cell(s) in the data range.

[aydeegee]

Hi Tinwelende

No, you are not supposed to mark anything. The macro goes from row 1 through to row 29 and sorts anything it finds.

I think it might be the "Option Compare Binary" line that is causing your problem. I say this because I do not understand it (see post #9). However, I got the macro to work without it, so you could try putting an apostrophe in front of the line ( as in ' Option ...). This will make the macro ignore that line. To get to that line:

Press Ctrl + F8, single left click on SORTIN then select Step Into. You should now see xladept's macro.

Put in the apostrophe and pressing the F8 key should step you through the macro, highlighting in yellow the line that is about to be actioned. Alt + q will stop the macro and take you back to the workbook.

Regards
Alastair

[Tinwelende]

That worked! Putting an apostrophe infront of the "Option Compare Binary"!

Thank you so much for your help!

Now on to the next problem... :P Haha!

For it to work - will I always have to run the macro manually every time I want it sorted!?

[Tinwelende]

Oooh, no... What happened more was that the formulas in the cells disappeared... Now after running the macro, it seems to have just printed out the values I got with the macro and it doesn't let the formulas remain with each cell...

Are you sure there is not an if-formula to put in a set of new columns to the right of all of my original columns? One if-formula per cell, comparing it to it's "partner cell" and sorting the two of them thereafter. I know all the possible data that can be put into each cell, in case that has to be listed in such a formula.

[xladept]

I didn't realize that you wanted a new sheet - I limited the indices horizontally to 14 and vertically to 29 because you had the desired results on the same sheet and the explanation below - Option Compare Binary is supposed to order the lower case letters after the higher case ones - I found that it worked intermittently - I was going to post another solution last night but either my system or the forum itself had virtually stopped. I see that Jindon has posted a solution for you - Jindon's solutions are usually "spot on" - Alastair, you can adapt my code anytime - I'm no dilettante and I'm not jealous (I may be envious sometimes)

[Tinwelende]

Thank you!

Jindon's solution DID work the way I wanted, I think, but how do I apply Jindon's solution to my table of 16 389 rows? And will it still work when my cells contain formulas and values that will change?

(Just to let you know - I'm soooo extremely thankful for all this help you're giving me! Without you all, I'd be totally lost and I'm now learning so much about Excel on the way!)

[xladept]

Here's what I would have posted last night;with the addition of a new sheet:

Please Login or Register  to view this content.

[jindon]

If you want the result to Col. Q to AD and A to N contains formula.

[Tinwelende]

xladept: I don't neccissarily need a new sheet. That's ok, too, but it's also ok for it to end up right beside the data we're sorting -on the same sheet.

I tried your macro and it does do exactly what I want! Though, it seems like I have to run the macro every single time I want the letters sorted after I've saved and exited the document?

[xladept]

@Tinwelende - you can tie it into an event procedure like the Worksheet_Change event:

Please Login or Register  to view this content.

in the worksheet module

[Tinwelende]

Where do I insert this information? After Return: End Sub? Or before?

Also, I noticed that some letters DON'T change. Starting at A65, there is no change in letter when I change my original data. Some cells (spread out over most columns) don't get the formula transferred to the new sheet, it seems.

[xladept]

Can you post a sample with just data; the way you'll really be using it? - If you do, I'll send it back to you with the code as Jindon has done.

[Marcol]

See if this formula based workbook does as you need.

No VBa required.

Setup the Names to suit your workbook, see the Names Manager

Then in R1, Drag Across to AE1, then down

Please Login or Register  to view this content.

[aydeegee]

Hi Tinwelende

3 options (that I know of - there are probably more)

1 - set the macro to run every time a cell is updated. This might be a little over the top?
2 - set the macro to run from a designated keystroke set. Ctrl + F8 / single click SORTIN / Options / choose a letter. Thereafter, CTRL + <chosen letter> will run the macro.
3 - set macro to run from a shape. Insert / shapes / Select a shape and put it on the spreadsheet (make it a pretty colour, if you want!). Right click the shape / Assign macro / <use intelligence>

Regards
Alastair

[Tinwelende]

OH, MY GOD, Marcol! THAT WORKS!!!

Now I only have one small request left until we've reached perfection! Some cells may sometimes not contain any information at all. If I leave some cells blank now, I get #### in all the cells in that row. Is it possible to change something in the formula, so that it will just transfer the blank cell, too, instead of giving me an error?

[Marcol]

... Some cells may sometimes not contain any information at all. If I leave some cells blank ...

You say that the values in your Data Table are formula derived, "But my original data to the left here will contain formulas"

1/. I assume then that these formula can return "", is this the case? If not what do they actually return?
A cell that contains a formula isn't a cell that does "not contain any information at all".

2/. So where in the final sort order should these cells appear?

This is not what I would describe as a "small request".

[Tinwelende]

Yes, the cells that "contain nothing" returns "".

In your sorting formula the cells get sorted alphabetically, which is perfect. Though, it doesn't really matter if the ""-cells appear to the right or to the left in the result cells, since I'm going to merge them all after this step into one cell per row (that is still sensitive to changes).

[newdoverman]

Maybe this will help out.

[Tinwelende]

It seems to be doing the same thing as Marcol posted. Altough, if I have cells that show nothing, the final cell needs to show "" and not #### as it is doing right now. But your formula seems simpler than the previous one I got, though.

[newdoverman]

This should eliminate the ###

[Tinwelende]

YES!!! That works EXACTLY like I want it! Thank you SOOOO much! You have no idea how happy this made me right now! You made my day!

[newdoverman]

I'm glad that you have the solution that you wanted to get.

[Marcol]

YES!!! That works EXACTLY like I want it! Thank you SOOOO much! You have no idea how happy this made me right now! You made my day!

We seem to be talking at cross purposes here, I thought the entire row had to be sorted Aa Bb, etc. , with the first upper case letter given precedence'

I can't see how the solution that you have accepted achieves this ...

[Tinwelende]

Oh, well, it doesn't have to sort the entire row, sorry if I was unclear about this. If it's easy to add that feature to the formula, though, it WOULD be fantastic! I'd gladly use a formula like that if you have the time and will to change it!

[xladept]

I guess you're not going to post a true sample. I've become confused about what you want. If the thread is solved, you should mark it so - good luck.

[Tinwelende]

It's not completely solved yet, but I think Marcol knows what I mean by my last post, so I'm just waiting for his reply. I'll mark it solved once it is, I promise.

[Marcol]

Oh, well, it doesn't have to sort the entire row, sorry if I was unclear about this. If it's easy to add that feature to the formula, though, it WOULD be fantastic! I'd gladly use a formula like that if you have the time and will to change it!

I'm still at a loss as to what you want ...
The workbook I gave you sorts the entire row as you asked, that is why it is more complicated than the solution newdoverman offered.

This workbook does the same, but it allows for your formulae in the data table returning "", these are pushed to the end of the result row.

Look in particular at rows 8 & 12 to see what is happening.

[Tinwelende]

Thank you, Marcol! Both your and newdoverman's codes will work for my purposes, but yours is taking it one step further, so I will definately use yours. Thank you!

I just can't seem to transfer it to my document without having some errors, though. I have changed the cells, so that the cells I want it to work for is the same as in your workbook (starting är Q1). I get #NAME? in the first cell that I put it in. What am I doing wrong?

[Marcol]

Have you transferred the "Names" to your workbook? See the Names Manager. (In the file I posted).

You might be easier using the workboook I posted as a template.

Put your Data in Columns A:N, the Names will then work.
It shouldn't matter where the results table is in the workbook.

[Tinwelende]

Where in the file that you posted do I find the Names Manager...? I don't think I can use your workbook as a template, since I've already got a very extensive document with seven different sheets (with formulas going across the sheets).

[Marcol]

I don't have 2010, but in 2007 it is in the "Formulas" tab.