I want Extract unique text from cell - excel formula
Printable View
I want Extract unique text from cell - excel formula
By excel formula do you mean you can't using macros at all?
It is quite easy by UDF like:
then from user point of view is formula only:Code: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
Code:=OnlyUnq(D11)
Answer AAA / AAA / AAA / BBB / CCC / CCC / AMAR / AMAR / DDD AAA / BBB / CCC / AMAR / DDD AAA / BBB / CCC / AMAR / DDD /
Answer AAA / AAA / AAA / BBB / CCC / CCC / AMAR / AMAR / DDD AAA / BBB / CCC / AMAR / DDD =OnlyUnq(D11)
Sheet: Sheet1
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:ArtikCode:For i = 0 To UBound(parts)
Quote:
Originally Posted by 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, 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
Perfect work:yes
Thanks Very much Sir
Artik - absolutely right, however I agree with Paul, and I always using LBound even with Split.
amartakale - happy to help.
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/UBoundQuote:
Originally Posted by Artik
Attachment 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)
[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/
Code: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 :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.Quote:
(1,000,000 x 100 x 2)
The calculations should look like this: 1,000,000 x 2.
:vv
Artik
: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')
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 codecan be written like this: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
It will be fast and "wordy". :)Code:'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
Artik
1. YesQuote:
Originally Posted by Artik
2. Yes
3. Yes (99%) :devil2:
[OPINION]
To my way of thinking ...
is very^2 slightly less readable thanCode:For n = lLower To lUpper
a(n) = n ^ 2
Next n
since it is a tiny bit closer to the actual data structure (i.e. the array A)Code:For n = LBound (a) To UBound(a)
a(n) = n ^ 2
Next n
[/OPINION]
But that's just me