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