+ Reply to Thread
Results 1 to 3 of 3

Sortuniq not sorting, Trim, not trimming, and not leading zero

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    10-21-2012
    Location
    miami, florida usa
    MS-Off Ver
    Excel 2007
    Posts
    260

    Sortuniq not sorting, Trim, not trimming, and not leading zero

    Hi, please need advice, i don't know what i'm doing wrong


    I'm extracting strings from single cells.
    These Cells contain digits Numbers including front zero.

    (Cell C15:C21, T15:T21, etc,etc)

    I need to Extract from original Cell and copy in to a Single Cell AO3 (Down,)
    -with numbers Sorted (least to most)
    -with "-" (dash) between each numbers and
    -Leading zero, But i excel doe's the job partially (well is not excel, it's me,

    I need to get this result :

    Cell
    Row
    AO3 02-08-11-16-25-28
    AO4 02-08-16-18-30
    AO5 02-05-08-09-12-16
    AO6 02-08-16-18-30-33
    AO7 02-06-08-16-23-32


    But i'm getting with extra "dash" in some of the string, also not sorted and not leading zero.
    I set the Cell properties to accept leading zero... (do i'm missing something?)

    I'm Getting this Undesired result, instead :
    (scramble)

    Cell
    Row
    AO3 11-16-2-25-28-8
    AO4 16-18-2-30-8
    AO5 12-16-2-5-8-9
    AO6 -16-18-2-30-33-8
    AO7 -16-2-23-32-6-8


    I have a VB code and using Sortuniq

    Function SortUniq(delim As String, ParamArray a()) As String
        Dim r As Range, e, x
        With CreateObject("System.Collections.ArrayList")
            For Each x In a
                For Each r In x
                    For Each e In Split(r.Value, delim)
                        If Not .Contains(Trim$(e)) Then .Add Trim$(e)
                    Next
                Next
            Next
            .Sort
            SortUniq = Join$(.ToArray, delim)
        End With
    End Function



    =(TRIM(sortuniq("-",C15:C21,S15:S21)))
    =(TRIM(sortuniq("-",C15:C21,T15:T21)))
    =(TRIM(sortuniq("-",C15:C21,U15:U21)))
    =(TRIM(sortuniq("-",C15:C21,V15:V21)))
    =(TRIM(sortuniq("-",C15:C21,W15:W21)))
    Thank you
    Last edited by david gonzalez; 01-21-2014 at 12:29 AM. Reason: forgot to sort the desire output

  2. #2
    Forum Contributor
    Join Date
    10-21-2012
    Location
    miami, florida usa
    MS-Off Ver
    Excel 2007
    Posts
    260

    Re: Sortuniq not sorting, Trim, not trimming, and not leading zero

    Hi, this is david again,

    Last night had a crash with my Win XP,

    This is the Sample File, where i'm getting the problem..

    Question: Conditional format it might causing this?,
    Because i tried in brand new sheet the SortUnique formula and macro,
    and it worked fine..

    Thanks

    Best Regards

    David
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    10-21-2012
    Location
    miami, florida usa
    MS-Off Ver
    Excel 2007
    Posts
    260

    Re: Sortuniq not sorting, Trim, not trimming, and not leading zero

    Thanks guys,

    Problem Fixed,

    Might some one is going to need some help,
    So let me share what i did:

    Next to a Column AO3 (on Column AP3)
    I Insert the Formula :
     =If(LEFT(AO3,1)="-",MID(AO3,2,LEN(AO3)), If(RIGHT(AO3,1)="-",LEFT(AO3,(LEN(AO3)-1)),AO3))
    Copy the formula down the column

    Such Formula Trim de First and Last "-" (dash), at sametime for unknown reason, this formula help
    to recognized the Leading zero on each single digit, So,

    the String looks Sorted, Trimmed the front and Last "-", Keep dash between digits, and recognize the Leading Zero as I need it.

    Thank you so much


    Best Regards from Cold miami.

    David

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. TRIM function and leading spaces
    By nsv in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 10-01-2014, 10:32 PM
  2. [SOLVED] Trim not Trimming!
    By davidx in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-31-2013, 03:43 AM
  3. CSV file in excel is trimming leading 0's
    By Delta223 in forum Excel General
    Replies: 12
    Last Post: 01-27-2010, 05:24 PM
  4. trim leading spaces
    By captain bob in forum Excel General
    Replies: 2
    Last Post: 08-03-2006, 10:00 AM
  5. [SOLVED] trim leading spaces
    By hawat.thufir@gmail.com in forum Excel General
    Replies: 10
    Last Post: 05-11-2006, 04:25 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1