Extract unique text from cell - excel formula

[amartakale]

I want Extract unique text from cell - excel formula

[KOKOSEK]

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

[Artik]

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

[Paul_Hossler]

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

[Artik]

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

[amartakale]

Perfect work

Thanks Very much Sir

[KOKOSEK]

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

[Paul_Hossler]

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

[Artik]

(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

[Paul_Hossler]

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')

[Artik]

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

[Paul_Hossler]

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