I want Extract unique text from cell - excel formula
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: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
=OnlyUnq(D11)
D E 10Answer 11AAA / AAA / AAA / BBB / CCC / CCC / AMAR / AMAR / DDD AAA / BBB / CCC / AMAR / DDD 12AAA / BBB / CCC / AMAR / DDD /
D E 10Answer 11AAA / AAA / AAA / BBB / CCC / CCC / AMAR / AMAR / DDD AAA / BBB / CCC / AMAR / DDD 12=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:ArtikFor i = 0 To UBound(parts)
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
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) / 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
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
Perfect work
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/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
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
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) / 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
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.(1,000,000 x 100 x 2)
The calculations should look like this: 1,000,000 x 2.
Artik
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
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
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) / 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
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:'Lbound - UBound Timer_Start For i = 1 To NumLoops For n = LBound(a) To UBound(a) a(n) = n ^ 2 Next n Next iIt will be fast and "wordy".'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
Last edited by Artik; 09-17-2019 at 01:14 AM.
1. Yes
2. Yes
3. Yes (99%)
[OPINION]
To my way of thinking ...
is very^2 slightly less readable thanFor 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)For n = LBound (a) To UBound(a) a(n) = n ^ 2 Next n
[/OPINION]
But that's just me
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
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) / 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