Count unique text and finding spelling mistakes

**Ardiko** · 10-06-2013, 06:53 PM

Hi,

I am currently trying to remove any errors from a spreadsheet. On the left I have the number of species with the amount of counts they are getting.
I can seem to either A) find the misspellings of the species in column C or B) find the missing species name in the e to u column.

Any solution WOULD be greatly appreciated!!!

Tree Species Problem.xlsx

As it stands:

Total Trees recorded per individual species :306
Total Trees in all Experiments 330
Percentage correct 92.72727273

**martindwilson** · 10-06-2013, 07:05 PM

hmm i looked again and you already have checked with countif in column c
so what are you trying to identify?

**Ardiko** · 10-06-2013, 07:12 PM

Thanks martindwilson,

Could you elaborate a tiny bit more? Still a little bit lost. I can see that all the orange are 'true' as in correct but can't get it to work.
My problem is that from columns F-U there are spelling errors. I need to figure out where they are compared to Column B.

Apologies if this isn't that clear - getting a bit flustered with it.

**martindwilson** · 10-06-2013, 07:14 PM

so its f:u that are spelt wrongly?

**Ardiko** · 10-06-2013, 07:18 PM

Yes - I'm trying to compile a list of hits on the left hand side so there may be misspellings in the names. What I've figured out it is for 24 of the tree species between f:u but its a needle in a hay stack for me.

**Richard Buttrey** · 10-06-2013, 07:18 PM

Hi,

If you use the formula

Formula:

=COUNTIF($F$2:$U$143,LEFT(B2,9)&"*")

in C2 copied down you'll get the answer 330.

However this is by trial and error after trying constants other than 9.

Other than the wildcard "*" Excel doesn't really have any fuzzy logic. For this sort of fuzzy search try Microsoft's add in which can be downloaded here

http://www.microsoft.com/en-gb/downl....aspx?id=15011

**Ardiko** · 10-06-2013, 07:22 PM

Hi Richard,

That's good that it shows that it's is very close to having the correct values. Still don't know which ones to correct though based on that. I'll have a look at your link now.

Cheers.

**martindwilson** · 10-06-2013, 07:35 PM

well if you are only checking spelling (and this includes exctra spaces after names then yo can conditional format e:u checking against col b
all those in orange dont match or have trailing spaces

**Ardiko** · 10-06-2013, 07:38 PM

Thanks, that's exactly what I need. I downloaded the fuzzy look up! I wish I knew about that sooner! Thank you very much for your help martindwilson and Richard Buttrey!