hello
I have a huge list of names; some of which are duplicates. However, its difficult to use the duplicate conditional formatting because there are spelling errors.

For eg. if the list consists of fruits and vegetables:

Apples
Apple
Appel

While they need to be grouped under Apple- duplicate doesnt work on this. Any idea on how to fix this problem?

Thanks

Hi,

Am Mon, 6 May 2013 07:36:08 -0700 (PDT) schrieb RB:

Apples
Apple
Appel

look for the longest corresponding part. In your case with the Apples:
"App".
Conditional Formatting = Formula:
=ISNUMBER(SEARCH("App",A1))


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

On Monday, May 6, 2013 9:36:08 AM UTC-5, RB wrote:
hello

I have a huge list of names; some of which are duplicates. However, its difficult to use the duplicate conditional formatting because there are spelling errors.



For eg. if the list consists of fruits and vegetables:



Apples

Apple

Appel



While they need to be grouped under Apple- duplicate doesnt work on this. Any idea on how to fix this problem?



Thanks

Thanks Claus!!!

Hi again,

Am Mon, 6 May 2013 07:36:08 -0700 (PDT) schrieb RB:

Apples
Apple
Appel

you can also try:
Find & Select = Replace = Find what="App*" = Replace with "Apples"
Then the spellings for apples are equal.


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

On Monday, May 6, 2013 10:10:43 AM UTC-5, Claus Busch wrote:
Hi again,



Am Mon, 6 May 2013 07:36:08 -0700 (PDT) schrieb RB:



Apples

Apple

Appel



you can also try:

Find & Select = Replace = Find what="App*" = Replace with "Apples"

Then the spellings for apples are equal.





Regards

Claus Busch

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

Hi Claus,

thanks for your post- however, turns out that this really doesnt help me; in this case, I know what I have to look for.
But what if the list has different names and the spelling is off for some and not for the others.

Eg.
Banana, BANANAS, Apples, APPLELS, Apple, Plums, Plums, Orange, Oranges etc,
In this case: the find and replace wont work especially because there are different names and they are all different.
I need my list to read as : Banana, Apples, Plums, Orange

thanks,

Hi,

Am Mon, 6 May 2013 08:49:46 -0700 (PDT) schrieb RB:

But what if the list has different names and the spelling is off for some and not for the others.

Eg.
Banana, BANANAS, Apples, APPLELS, Apple, Plums, Plums, Orange, Oranges etc,
In this case: the find and replace wont work especially because there are different names and they are all different.
I need my list to read as : Banana, Apples, Plums, Orange

you have to do it especially for each name. It is a lot of work, but
then your table is correct.
You can do it also with VBA. Loop through all your names, find and
replace them.


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Hi,

Am Mon, 6 May 2013 17:54:34 +0200 schrieb Claus Busch:

you have to do it especially for each name. It is a lot of work, but
then your table is correct.
You can do it also with VBA. Loop through all your names, find and
replace them.

then you can create a new table with the correct spelling and sum your
items with SUMIF


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

On Tue, 7 May 2013 06:53:54 +0100, Walter Briscoe wrote:

I would have used
d(i, j) = Application.Min(d(i - 1, j) + 1, d(i, j - 1) + 1, d(i - 1, j - 1) + cost)

I am sure your reasoning is better than mine. ;)

My "reasoning" is poor.

At the time, I was looking just at implementing the concept. The code is a copy of a VB routine that I found at the site noted in the code (which now appears to be non-existent). I never spent any time trying to optimize the code and I suspect that the original coder wrote this in VB, and not VBA, and used ony functions that were available in VB.

However, you provoked me to test the timing on this and, it turns out, at least on the very small subset of 3 numbers I tested, the VB implementation runs much faster than does the Application.Min function.

In message of Tue, 7 May
2013 06:07:11 in microsoft.public.excel.worksheet.functions, Ron
Rosenfeld writes
On Tue, 7 May 2013 06:53:54 +0100, Walter Briscoe
wrote:

I would have used
d(i, j) = Application.Min(d(i - 1, j) + 1, d(i, j - 1) + 1, d(i
- 1, j - 1) + cost)

I am sure your reasoning is better than mine. ;)

My "reasoning" is poor.

At the time, I was looking just at implementing the concept. The code
is a copy of a VB routine that I found at the site noted in the code
(which now appears to be non-existent). I never spent any time trying
to optimize the code and I suspect that the original coder wrote this
in VB, and not VBA, and used ony functions that were available in VB.

However, you provoked me to test the timing on this and, it turns out,
at least on the very small subset of 3 numbers I tested, the VB
implementation runs much faster than does the Application.Min function.



That is interesting - mainly from the ability to time small pieces of
code. How do you do it? I am guessing you get the difference in time
between running the 2 pieces of code a large number of times and infer
that division gets a reasonable approximation to the time for a single
call. That inference might be false. Optimisers can confuse. ;)

I rarely worry about the time taken to run small pieces of code.
My code usually gets data from the Internet and is IO-bound.
Each round trip to the net takes of the order of a second.
I have yet to work out how to run several transactions in parallel. ;(
--
Walter Briscoe

On Tue, 7 May 2013 15:12:43 +0100, Walter Briscoe wrote:

That is interesting - mainly from the ability to time small pieces of
code. How do you do it? I am guessing you get the difference in time
between running the 2 pieces of code a large number of times and infer
that division gets a reasonable approximation to the time for a single
call. That inference might be false. Optimisers can confuse. ;)

I use the high precision event timer (HPET). And I got similar results running just a single instance as well as 100 iterations.


I rarely worry about the time taken to run small pieces of code.
My code usually gets data from the Internet and is IO-bound.
Each round trip to the net takes of the order of a second.
I have yet to work out how to run several transactions in parallel.

I rarely worry about it, but I have had some projects which process hundreds of thousands of rows, so speeding up even short segments can have a significant impact.

I think this is the same algorithm...

Private Function LD&(ByVal s$, ByVal t$)
Dim d()&,m&,i&,n&,i&,j&,cost&
Dim s_i$,t_j$

If (Not Len(s)) or (Not Len(t)) Then LD = 0: Exit Function

'Step1
n = Len(s): m = Len(t): ReDim d(0 To n, 0 To m) As Long

'Step2
For i = 0 To n: d(i, 0) = i: Next 'i
For j = 0 To m: d(0, j) = j: Next 'j

For i = 1 To n
s_i = Mid$(s, i, 1) '//step3

For j = 1 To m
t_j = Mid$(t, j, 1) '//step4

If s_i = t_j Then cost = 0 Else cost = 1 '//step5

d(i, j) = Minimum(d(i - 1, j) + 1, _
d(i, j - 1) + 1, _
d(i - 1, j - 1) + cost) '//step6
Next 'j
Next 'i

'Step7
LD = d(n, m): Erase d
End Function

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On Tue, 07 May 2013 14:31:31 -0400, GS wrote:

I think this is the same algorithm...

Private Function LD&(ByVal s$, ByVal t$)
Dim d()&,m&,i&,n&,i&,j&,cost&
Dim s_i$,t_j$

If (Not Len(s)) or (Not Len(t)) Then LD = 0: Exit Function

'Step1
n = Len(s): m = Len(t): ReDim d(0 To n, 0 To m) As Long

'Step2
For i = 0 To n: d(i, 0) = i: Next 'i
For j = 0 To m: d(0, j) = j: Next 'j

For i = 1 To n
s_i = Mid$(s, i, 1) '//step3

For j = 1 To m
t_j = Mid$(t, j, 1) '//step4

If s_i = t_j Then cost = 0 Else cost = 1 '//step5

d(i, j) = Minimum(d(i - 1, j) + 1, _
d(i, j - 1) + 1, _
d(i - 1, j - 1) + cost) '//step6
Next 'j
Next 'i

'Step7
LD = d(n, m): Erase d
End Function

It seems to be exactly the same algorithm, using the same variables, but (to me) making things a bit more obscure by using type declaration characters instead of naming the types, and by combining multiple lines into a single line. Others may prefer this type of presentation.

Also, I don't see the Minimum function in what you've posted.

On Tue, 07 May 2013 14:31:31 -0400, GS wrote:

I think this is the same algorithm...

Private Function LD&(ByVal s$, ByVal t$)
Dim d()&,m&,i&,n&,i&,j&,cost&
Dim s_i$,t_j$

If (Not Len(s)) or (Not Len(t)) Then LD = 0: Exit Function

'Step1
n = Len(s): m = Len(t): ReDim d(0 To n, 0 To m) As Long

'Step2
For i = 0 To n: d(i, 0) = i: Next 'i
For j = 0 To m: d(0, j) = j: Next 'j

For i = 1 To n
s_i = Mid$(s, i, 1) '//step3

For j = 1 To m
t_j = Mid$(t, j, 1) '//step4

If s_i = t_j Then cost = 0 Else cost = 1 '//step5

d(i, j) = Minimum(d(i - 1, j) + 1, _
d(i, j - 1) + 1, _
d(i - 1, j - 1) + cost) '//step6
Next 'j
Next 'i

'Step7
LD = d(n, m): Erase d
End Function

It seems to be exactly the same algorithm, using the same variables,
but (to me) making things a bit more obscure by using type
declaration characters instead of naming the types, and by combining
multiple lines into a single line. Others may prefer this type of
presentation.

Also, I don't see the Minimum function in what you've posted.

Yeah, that was how I copied it from the original webpage into my text
editor. I used the type symbols to shorten things. I got used to
reading these in older VB scripts and so I adopted using them for
common types instead of typing long hand!

I was searching for where I had put this in a code module after
revising it to do Min or Max. It didn't seem to work as expected for
some reason and so I was hoping your link would shed some light but my
browser said the page couldn't be found.

Here's the revised code I found...

Function LD&(s$, t$, Optional MinOrMax&)
Dim d() As Long, m&, n&, i&, j&, cost&
Dim s_i$, t_j$

If (Len(s) = 0) Or (Len(t) = 0) Then LD = 0: Exit Function

'Initialize array
n = Len(s): m = Len(t): ReDim d(0 To n, 0 To m)
For i = 0 To n: d(i, 0) = i: Next 'i
For j = 0 To m: d(0, j) = j: Next 'j

'Load array with min values
For i = 1 To n
s_i = Mid$(s, i, 1)

For j = 1 To m
t_j = Mid$(t, j, 1)

If s_i = t_j Then cost = 0 Else cost = 1

d(i, j) = Get_MinMax(d(i - 1, j) + 1, _
d(i, j - 1) + 1, _
d(i - 1, j - 1) + cost, MinOrMax)
Next 'j
Next 'i
LD = d(n, m): Erase d
End Function

Private Function Get_MinMax&(ByVal a&, ByVal b&, ByVal c&, Optional
Spec&)
Dim mi&
mi = a
If Spec = 0 Then
If b < mi Then mi = b
If c < mi Then mi = c
Else
If b mi Then mi = b
If c mi Then mi = c
End If 'Min = 0
Get_MinMax = mi
End Function

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

I noticed arg defs and a var name did not get changed...

Private Function Get_MinMax&(a&, b&, c&, Optional Spec&)

Dim mi&
mi = a
If Spec = 0 Then
If b < mi Then mi = b
If c < mi Then mi = c
Else
If b mi Then mi = b
If c mi Then mi = c

End If 'Spec = 0

Get_MinMax = mi
End Function

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On Tue, 07 May 2013 15:47:58 -0400, GS wrote:

I used the type symbols to shorten things. I got used to
reading these in older VB scripts and so I adopted using them for
common types instead of typing long hand!

I thought I recalled that you liked using the type symbols.


It didn't seem to work as expected for
some reason and so I was hoping your link would shed some light but my
browser said the page couldn't be found.

Here's the revised code I found...

This revised code seems to function OK with (very) limited testing. Where does it give unexpected results for you?

On Tue, 07 May 2013 15:47:58 -0400, GS wrote:

I used the type symbols to shorten things. I got used to
reading these in older VB scripts and so I adopted using them for
common types instead of typing long hand!

I thought I recalled that you liked using the type symbols.

Yes, as I said I adopted them due to reading lots of VB code that had
them. Once one gets used to them there's no problem identifying type
since I still prefix vars with lowercase type chars...

Dim lLastRow&, sFilename$, iPos%, dlTime#, slTime!

... As Long, String, Integer, Double, Single respectively.


It didn't seem to work as expected for
some reason and so I was hoping your link would shed some light but
my browser said the page couldn't be found.

Here's the revised code I found...

This revised code seems to function OK with (very) limited testing.
Where does it give unexpected results for you?

It doesn't return Min correctly, IMO, when testing via the Immediate
Window. I never used this in a project, ..just thought it was an
interesting function to have available. It would help if I had the
benefit of reading that webpage so I could better understand how to use
it. What test data did you process to test how it works?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On Tue, 07 May 2013 20:37:50 -0400, GS wrote:

It doesn't return Min correctly, IMO, when testing via the Immediate
Window. I never used this in a project, ..just thought it was an
interesting function to have available. It would help if I had the
benefit of reading that webpage so I could better understand how to use
it. What test data did you process to test how it works?

I just tested the LD function. Not the MinMax function.

On Tue, 07 May 2013 20:37:50 -0400, GS wrote:

It doesn't return Min correctly, IMO, when testing via the Immediate
Window. I never used this in a project, ..just thought it was an
interesting function to have available. It would help if I had the
benefit of reading that webpage so I could better understand how to
use it. What test data did you process to test how it works?

I just tested the LD function. Not the MinMax function.

I was able to find some good documentation online and so I now have a
fresh understanding of how this function works AND how it should be
used!<g In retrospect, IMO, it has nothing to do with
WorksheetFunctions Min/Max!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion