Sorting Numbers

[Don Ivie]

Well I used to be pretty good with Excel Macros but have been out of the biz
for several years and hey, I'm old and I've slept since then

So here's what I'm trying to automate - I have a list of numbers like
this:

472624
485060
485110
485445
486065
486712
487466
487598
487698
487843
487874
487898
487905
487942
487953
487954
488057
488075
488086
488095
488100
488112
488123
488126
488139
488151

I need to sort these numbers into columns based on the last digit of each
number so that the end result looks like this:

Please Login or Register  to view this content.

Of course the actual list of numbers is much longer, typically a couple
hundred long. Also, the number of unique values in the list varies each time
I run this report.

Anyway, manually its pretty easy to do but programatically I can't decide on
where to start. My first thought was to loop thru and evaluate each number
then cut and paste it to the column corresponding to the last digit of the
number.

Then I thought well what if I just duplicated the entire original column of
numbers into 10 columns then just go thru each column and remove the unwanted
values from each until I ended up with the same result.

The problem with either of those approaches is tracking the cell position for
the cut and paste - just seems like a looping nightmare!

I'm guessing there's some funtion or combination of functions like Transpose
etc that would make this a very short macro lol

Any suggestions?

[TMS]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

[MarvinP]

Hi Don and welcome to the forum. (I'm old too)

It seems like you need a String Reverse function. See http://excelhelp.com/vba-programming...verse-function

Does it help? I don't know if your version of excel has it included.

[Don Ivie]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

Ok done. Example attached. 1st sheet is raw data, second is the desired result.

[shg]

Are you intent on doing this with VBA?

In D1, confirmed with Ctrl+Shift+Enter and copied right and down,

=IFERROR(SMALL(IF(--RIGHT($A$1:$A$278, 1)=COLUMNS($D1:D1)-1, $A$1:$A$278), ROWS(D$1:D1)), "")

[Don Ivie]

Thanks Marv, I didn't really see how that function would help me but let me explain how I accomplish the task manually and maybe that will shed some light on ways to automate the process.

The list of numbers is imported into a single row in a new sheet. It will always be a 6 digit number (but in text format) and there will be duplicates. So the first thing I do is select the entire column and use the Remove Duplicates function from the Data menu.

Now I have a column of unique 6 digit numbers and they are already in their natural numeric order. Problem is I need these numbers in order by the last digit of the six digit number. I accomplish this using the right() function since even though its a number the cell property is still text. So I enter this function in the column just to the right of the first value and it looks something like this:

486442 =right(A1,1) (and of course the result is 2)

I then copy this formula all the way down column B until every each number has it's right most value in the column next to it:

486442 2
488833 3
488833 3
488951 1

The I simply use the sort function to sort column A based on column B:

488951 1
486442 2
488833 3
488833 3

Now column A is in the order I need so I delete column B then I just cut and paste all the numbers that end with 1 into the 1's column, 2 into the 2's column etc:

489300 488951 486442 488833
489300 488951 489452 488833
489640 489461 489513
490090 489561 489733
490150 489881
490200 489961
490210 489961

Fin

[Don Ivie]

And yes, would like to automate this in VBA!

[Don Ivie]

BTW, keep getting Java errors on this site:

Webpage error details

User Agent: Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 5.1; Trident/4.0; .NET CLR 1.1.4322; .NET CLR 2.0.50727; .NET CLR 3.0.04506.30; .NET CLR 3.0.4506.2152; .NET CLR 3.5.30729; MDDR; .NET CLR 1.0.3705)
Timestamp: Wed, 14 Dec 2011 18:32:24 UTC


Message: Invalid argument.
Line: 11
Char: 4252
Code: 0
URI: http://www.excelforum.com/clientscri...reply.js?v=418

Happens everytime I try to click on Reply or Reply with Quote. ????

[Don Ivie]

"Are you intent on doing this with VBA?

In D1, confirmed with Ctrl+Shift+Enter and copied right and down,

=IFERROR(SMALL(IF(--RIGHT($A$1:$A$278, 1)=COLUMNS($D1:D1)-1, $A$1:$A$278), ROWS(D$1:D1)), "") "

Couldn't get this to do much of anything although I'm not sure I'm applying it correctly?

[shg]

Paste the formula in the formula bar, but DON'T press Enter.

Instead, press and hold the Ctrl and Shift keys, THEN press Enter.

[Don Ivie]

Paste the formula in the formula bar, but DON'T press Enter.

Instead, press and hold the Ctrl and Shift keys, THEN press Enter.

Ok, did that but doesnt really do anything, did I miss something? Clicked on D1, clicked in formula bar, pressed ctrl-v (iferror statement appears), then pressed ctrl-shft-enter.

[shg]

One more step, sorry.

Your numbers are stored as text. Select the column, do Data > Text to columns, Finish.

When you're done, they will be right-aligned (mean Excel sez they are numbers)

[nilem]

Try this macro (see attachment)

[Don Ivie]

It turned them into date format and the are sorted as such but if I switch the format back to general its still unsorted and in one column.

[shg]

Select the column, change the format back to General.

EDIT: See attached.

[Don Ivie]

That worked perfectly! Let me do a little testing with some random samples of data and see how it goes! Would love it if you could explain what you did for my edification lol

[Don Ivie]

Sorry, cannot get this forum to thread properly due to the javascript errors. nilem's macro did the trick.

[Don Ivie]

Success!!!! nilem, you're a genius! Went and retrieved a entirely different dataset and plugged it into the macro and it worked flawlessly. Exactly what I was looking for to - a nice little loop in VBA to get the hard work done. Now I can dress it up and make it to where any monkey here in the office can utilize it.

Thanks again!

P.S. Would be awesome if you could upload a heavily remarked version. Would love to know how you did it!

[kvsrinivasamurthy]

See the attached file with macro ,which carries out required work.

[kvsrinivasamurthy]

See the file attached,with macro.
macro code

Please Login or Register  to view this content.

[kvsrinivasamurthy]

Run this macro
code;

Sub sort_differently()
Dim L_ro, T, TA As Integer

Application.ScreenUpdating = False

L_ro = Sheets("Before").Cells(1, 1).End(xlDown).Row
Sheets("After").Range("K1").EntireColumn.Insert
Sheets("Before").Range("A1:A" & L_ro).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheets("After").Range( _
"K1"), Unique:=True
L_ro = Sheets("After").Cells(1, "K").End(xlDown).Row


With Sheets("After")

For T = 1 To L_ro
For TA = 0 To 9
If Val(Right(.Cells(T, "K"), 1)) = TA Then
If .Cells(1, TA + 1) = "" Then
.Cells(1, TA + 1) = .Cells(T, "K")
ElseIf .Cells(2, TA + 1) = "" Then
.Cells(2, TA + 1) = .Cells(T, "K")
Else
.Range(Cells(1, TA + 1).Address).End(xlDown).Offset(1, 0).Value = .Cells(T, "K")
End If
Exit For
End If
Next TA

Next T


End With

Sheets("After").Range("K1").EntireColumn.Delete
Application.ScreenUpdating = True
End Sub