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) :rofl:

Artik

[amartakale]

Perfect work:yes

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

25082

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)


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





https://bytecomb.com/accurate-performance-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 :nono, 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.
:vv
Artik

[Paul_Hossler]

:oops:


:doh::doh::doh::doh::doh::doh::doh:

Yea :blush:blush:blush:blush

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%) :devil2:



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)



But that's just me