Consulting

Results 1 to 12 of 12

Thread: Extract unique text from cell - excel formula

  1. #1

    Extract unique text from cell - excel formula

    I want Extract unique text from cell - excel formula
    Attached Files Attached Files

  2. #2
    By excel formula do you mean you can't using macros at all?
    It is quite easy by UDF like:

    Function OnlyUnq(InString As String)
    Dim parts As Variant
    Dim i As Long
    Dim temp As String
    Dim d As Object
    Set d = CreateObject("Scripting.Dictionary")
    
    
    parts = Split(InString, " / ")
    For i = LBound(parts) To UBound(parts)
            d(parts(i)) = 1
    Next i
    Dim v As Variant
    For Each v In d.Keys()
        temp = temp & v & " / "
    Next v
    OnlyUnq = Left(temp, Len(temp) - 3)
    End Function
    then from user point of view is formula only:

    =OnlyUnq(D11)
    D
    E
    10
    Answer
    11
    AAA / AAA / AAA / BBB / CCC / CCC / AMAR / AMAR / DDD AAA / BBB / CCC / AMAR / DDD
    12
    AAA / BBB / CCC / AMAR / DDD /





    D
    E
    10
    Answer
    11
    AAA / AAA / AAA / BBB / CCC / CCC / AMAR / AMAR / DDD AAA / BBB / CCC / AMAR / DDD
    12
    =OnlyUnq(D11)
    Sheet: Sheet1

  3. #3
    VBAX Tutor
    Joined
    Dec 2008
    Posts
    283
    Location
    KOKOSEK, Regardless of the directive at the beginning of the module (Option Base 0 or Option Base 1 or lack thereof), the Split function always returns an array with a lower index = 0. Therefore, you can safely write:
     For i = 0 To UBound(parts)
    Artik

  4. #4
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,723
    Location
    Quote Originally Posted by Artik View Post
    KOKOSEK, Regardless of the directive at the beginning of the module (Option Base 0 or Option Base 1 or lack thereof), the Split function always returns an array with a lower index = 0. Therefore, you can safely write:
     For i = 0 To UBound(parts)
    Artik

    True, but I've found that it's better (for me at least) to be consistent and always use LBound().

    One less special thing for me to remember
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  5. #5
    VBAX Tutor
    Joined
    Dec 2008
    Posts
    283
    Location
    Paul, I've known your opinion for some time. I know you do not like to walk shortcuts.
    However, where we fight for every millisecond, it is worth knowing.

    And we have to train the mind until the end of our days, so as not to get Alzheimer's too quickly (and we will probably get it anyway)

    Artik

  6. #6
    Perfect work

    Thanks Very much Sir

  7. #7
    Artik - absolutely right, however I agree with Paul, and I always using LBound even with Split.
    amartakale - happy to help.

  8. #8
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,723
    Location
    Quote Originally Posted by Artik View Post
    Paul, I've known your opinion for some time. I know you do not like to walk shortcuts.
    However, where we fight for every millisecond, it is worth knowing.


    Artik
    As a test, I did 1 million outer loops with 100 inner loops, with an exponentiation operation inside and compared the total times accessing the explicit bounds (0-99) and the LBound/UBound

    Capture.JPG

    The net difference of accessing the array bounds 200 million times (1,000,000 x 100 x 2) was a difference of 276.1 milliseconds (if I did this right)

    [OPINION]
    You're right, but I think you're only saving a couple of CPU cycles and I think that it's imperceptible to the user

    You're also right that I prefer to be wordy

    [/OPINION]



    https://bytecomb.com/accurate-perfor...timers-in-vba/

    Option Explicit
    
    
    Private Declare PtrSafe Function QueryPerformanceFrequency Lib "kernel32.dll" (lpFrequency As UINT64) As Long
    Private Declare PtrSafe Function QueryPerformanceCounter Lib "kernel32.dll" (lpPerformanceCount As UINT64) As Long
    
    
    Type UINT64
        LowPart As Long
        HighPart As Long
    End Type
     
    Const BSHIFT_32 = 4294967296# ' 2 ^ 32
    Const NumLoops As Long = 10 ^ 6
     
    Dim PFrequency As Double
    Dim pStartTS As UINT64
     
    
    
     
    Sub Timer_Start()
        Dim PerfFrequency As UINT64
        
        QueryPerformanceFrequency PerfFrequency
        PFrequency = U64Dbl(PerfFrequency)
        QueryPerformanceCounter pStartTS
    End Sub
     
     
    Function Timer_End() As Double
        Dim pNow As UINT64
        QueryPerformanceCounter pNow
        Timer_End = (U64Dbl(pNow) - U64Dbl(pStartTS)) / PFrequency
    End Function
     
    
    
    Sub test()
        Dim i As Long, n As Long
        Dim a(0 To 99) As Long
        Dim T1 As Double, T2 As Double
        
        '0 - 99
        Timer_Start
        For i = 1 To NumLoops
            For n = 0 To 99
                a(n) = n ^ 2
            Next n
        Next i
        T1 = Timer_End
        
        'Lbound - UBound
        Timer_Start
        For i = 1 To NumLoops
            For n = LBound(a) To UBound(a)
                a(n) = n ^ 2
            Next n
        Next i
    
    
        T2 = Timer_End
        
        MsgBox _
             "0-99  : " & Format(T1, "#,##0.0000") & " seconds" & vbCrLf & _
             "LB-UB  : " & Format(T2, "#,##0.0000") & " seconds" & vbCrLf & _
             "Diff  : " & Format(T1 - T2, "#,##0.0000") & " seconds"
    End Sub
    
    
    Private Function U64Dbl(U64 As UINT64) As Double
        Dim lDbl As Double, hDbl As Double
        lDbl = U64.LowPart
        hDbl = U64.HighPart
        If lDbl < 0 Then lDbl = lDbl + BSHIFT_32
        If hDbl < 0 Then hDbl = hDbl + BSHIFT_32
        U64Dbl = lDbl + BSHIFT_32 * hDbl
    End Function
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  9. #9
    VBAX Tutor
    Joined
    Dec 2008
    Posts
    283
    Location
    (1,000,000 x 100 x 2)
    Paul , loop limits n = LBound(a) To UBound(a) are calculated only once at the initiation of the loop, not at each turn of the loop.
    The calculations should look like this: 1,000,000 x 2.

    Artik

  10. #10
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,723
    Location





    Yea

    But still, even though it's 20 million times instead of the 200 million, a difference of 276.1 milliseconds is still pretty much not going to make any difference unless you're in the gazillion calculations range

    I just prefer my wordy style, and don't like to use an explicit lower bound (or any other 'magic number')

    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  11. #11
    VBAX Tutor
    Joined
    Dec 2008
    Posts
    283
    Location
    Fortunately, this is only an error in your calculations, not in the code.
    Yes, with the constantly increasing computing power of computers some coding elements lose their validity.

    So that the wolf is full and the sheep stays whole, this piece of code
        'Lbound - UBound
        Timer_Start
        For i = 1 To NumLoops
            For n = LBound(a) To UBound(a)
                a(n) = n ^ 2
            Next n
        Next i
    can be written like this:
       'Lbound - UBound
        Dim lLower As Long
        Dim lUpper As Long
        
        Timer_Start
        
        lLower = LBound(a)
        lUpper = UBound(a)
        
        For i = 1 To NumLoops
            For n = lLower To lUpper
                a(n) = n ^ 2
            Next n
        Next i
    It will be fast and "wordy".


    Artik
    Last edited by Artik; 09-17-2019 at 01:14 AM.

  12. #12
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,723
    Location
    Quote Originally Posted by Artik View Post
    Fortunately, this is only an error in your calculations, not in the code.
    Yes, with the constantly increasing computing power of computers some coding elements lose their validity.
    So that the wolf is full and the sheep stays whole, this piece of code
    1. Yes

    2. Yes

    3. Yes (99%)

    [OPINION]

    To my way of thinking ...

            For n = lLower To lUpper
                a(n) = n ^ 2
            Next n
    is very^2 slightly less readable than

            For n = LBound (a) To UBound(a)
                a(n) = n ^ 2
            Next n
    since it is a tiny bit closer to the actual data structure (i.e. the array A)

    [/OPINION]

    But that's just me
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

Posting Permissions

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