VBA code 3 columns to 1, using a specific order

Question

I have a list of data, distributed with a non-homogeneous pattern, on 3 columns. The challenge is to write a vba code "smart enough" to copy and paste all these numbers on one column, putting them in order, one below the other one: 1, 2, 3, 4, 4.1, 4.2 etc.. without missing any of them.

Could someone help me in this task? Because I see a lot of complexity and I have no idea how to manage it. Thank you!

Answer 1

As I understand it, you're looking to order this in a specific way that isn't necessarily how Excel would sort by default. The values look like version numbers or nested task IDs.

Stack Overflow isn't really a "solve your problem for you" kind of place, but I can definitely get you started. Here's what you'll need to do:

1. Get all the values, preferably into a Collections object. Make sure to omit blank cells.
2. Convert each value into a new format which a) is sortable, and b) can be reverted to the original format. For example, let's say these are version numbers, and any given number can be as high as 999, and there can be up to 4 items (e.g. 123.10.15.9 is valid, as is 9.5). Convert these to the form 000000000000 (that's 12 0s). You can do this by using Split() to divide the values using the "." delimiter, then padding the value out. This might look something like this:

.

'Converts 1        => 001000000000
'         1.1      => 001001000000
'         2.4.7    => 002004007000
'         65.339.1 => 065339001000
Function ConvertToSortableVersionNumber(value As String) As String
    'Add all the parts of the value (. delimited) to a collection
    Dim vnPart As Variant
    Dim error As Boolean: error = False
    Dim Parts As Collection
    Set Parts = New Collection
    For Each vnPart In Split(value, ".")
        'Make sure this can actually be formatted as needed.
        If Len(vnPart) > 3 Then
            error = True
            Exit For
        End If

        'Add this part to the Parts collection
        Parts.Add vnPart
    Next vnPart

    'Now make sure there are 4 parts total
    If Parts.Count > 4 Then error = True

    'Check for errors
    If error Then
        'There is an error. Handle it somehow
    End If

    'If there are less than 4 items in the collection , add some
    While Parts.Count < 4
        Parts.Add ("000")
    Wend

    'Now, assemble the return value
    Dim retVal As String
    Dim item As Variant
    For Each item In Parts
        retVal = retVal & Right(String(3, "0") & item, 3)
    Next item

    'All set!
    ConvertToSortableVersionNumber = retVal
End Function

3. Sort the collection (this page has a good example of sorting a collection in memory).
4. Create an array with new values converting back to the original format (much easier since the inputs will be highly standardized).
5. Write the array to a new range--and you're done!

Take a stab at it. I think you'll find that Stack Overflow is much more helpful once you can show the work you've already done, what you've tried, and what specifically is giving you trouble.

Good luck!

Answer 2

If you have 2010 or later the following formula will do it:

=IFERROR(SUBSTITUTE(SUBSTITUTE(AGGREGATE(15,6,--SUBSTITUTE($A$1:$C$10,".","000")*--(1 & REPT("0",9-LEN(SUBSTITUTE($A$1:$C$10,".","000")))),ROW(1:1)),"0000",""),"000","."),"")

If you have 2007 or earlier than it will need to be an array formula:

=IFERROR(SUBSTITUTE(SUBSTITUTE(SMALL(IF($A$1:$C$10<>"",--SUBSTITUTE($A$1:$C$10,".","000")*--(1 & REPT("0",9-LEN(SUBSTITUTE($A$1:$C$10,".","000"))))),ROW(1:1)),"0000",""),"000","."),"")

Being an array formula it must be confirmed with Ctrl-Shift-Enter instead of Enter when leaving edit mode.

Column E is the first formula and Column F is the second.