Hi Thomas,
Yes, I hope it was English.
Your data as shown would not be easy to search, some data items
followed by fullstop, some by comma. It is easier to search if the data
is standardised. Excel has a feature called Text to Columns which
separates your items for easy searching.
The first line drops the fullstop if one exists - thus:
=if(right(a1,1)=".",Left(A1,Len(A1)-1),A1)
says If the last character = a dot, drop one character.
The formula goes in B1 and can then be formula dragged down the column
(select/highlight B1 and click-drag the small + in the bottom right
corner of the cell). Column B then looks like column A but with no
trailling dot.
Column B is also the result of a formula, and we want to 'freeze' the
dispplayed text (ie, remove the formula but leave the answer), so Copy
and Paste special = Values.
Text to Columns can then be used, the results being obvious, followed
by the formula, again, formula-drag, firstly to the right to cover
columns H to L, and then downwards to cover H1 to L9999 (where 9999
just means the end of your data)
The formula counts the duplicates from that point forwards, so a
triplicate will show a '2', then a '1' then nothing on the last item.
The formula does not count duplicates within the same line, but with
the data shown that would not occur.
As stated, it uses columns B to L to work in, if you have data in these
columns then you can either copy column A to a new sheet, insert a dozen
new columns that can be deleted later, or use columns further to the
right (BB to BL etc) and adjust the formula accordingly.
Note the formula supplied was incorrect and should be
=COUNTIF($B2:$F$9999,B1)
- the $B and $F to retain the searched area as standard.
Hope this helps
--