Cell A1 is the following text data "SCR: 27001.01, 27002.01, 27003.01,
27004.01, 27005.01."

Cell a2 is the following text data "SCR: 29001.01, 29002.01, 29003.01,
27004.01, 29005.01."

27004.01 is duplicated text in both cells.

Is there a formula or a macro to check for duplicated data (that has been
entered as text)?


--
Thomas

Assuming this is the only column on the worksheet (12 helper columns
used for the data shown)

in column B put =Left(A1,Len(A1)-1)
to drop the trailing dot (full stop)

Select column B and Copy, Paste Special = Values back over itsself
Delete column A

Select Data Text to Columns and use Delimited with comma and space and
treat consecutive separators as one.

This should give data in columns B to F that can be tested.

In H1 put
=COUNTIF(B2:F$9999,B1)

and formula drag this to column L, then, whilst still selected
bulk-formula drag down to cover all of your data. (F$9999 being the end
of your data)
This counts the duplicates from this point onwards.

Count non-zero cells in H to L as required.

Hope this heps

--

Thomas O Wrote:
Cell A1 is the following text data "SCR: 27001.01, 27002.01,
27003.01,
27004.01, 27005.01."

Cell a2 is the following text data "SCR: 29001.01, 29002.01, 29003.01,
27004.01, 29005.01."

27004.01 is duplicated text in both cells.

Is there a formula or a macro to check for duplicated data (that has
been
entered as text)?


--
Thomas


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=537052

Bryan - thanks. Its gonna take me a while to filter the info you provided
thru my brain (that was English you used, wasn't it?)
--
Thomas


"Bryan Hessey" wrote:


Assuming this is the only column on the worksheet (12 helper columns
used for the data shown)

in column B put =Left(A1,Len(A1)-1)
to drop the trailing dot (full stop)

Select column B and Copy, Paste Special = Values back over itsself
Delete column A

Select Data Text to Columns and use Delimited with comma and space and
treat consecutive separators as one.

This should give data in columns B to F that can be tested.

In H1 put
=COUNTIF(B2:F$9999,B1)

and formula drag this to column L, then, whilst still selected
bulk-formula drag down to cover all of your data. (F$9999 being the end
of your data)
This counts the duplicates from this point onwards.

Count non-zero cells in H to L as required.

Hope this heps

--

Thomas O Wrote:
Cell A1 is the following text data "SCR: 27001.01, 27002.01,
27003.01,
27004.01, 27005.01."

Cell a2 is the following text data "SCR: 29001.01, 29002.01, 29003.01,
27004.01, 29005.01."

27004.01 is duplicated text in both cells.

Is there a formula or a macro to check for duplicated data (that has
been
entered as text)?


--
Thomas


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=537052

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

--

Thomas O Wrote:
Bryan - thanks. Its gonna take me a while to filter the info you
provided
thru my brain (that was English you used, wasn't it?)
--
Thomas


"Bryan Hessey" wrote:


Assuming this is the only column on the worksheet (12 helper columns
used for the data shown)

in column B put =Left(A1,Len(A1)-1)
to drop the trailing dot (full stop)

Select column B and Copy, Paste Special = Values back over itsself
Delete column A

Select Data Text to Columns and use Delimited with comma and space
and
treat consecutive separators as one.

This should give data in columns B to F that can be tested.

In H1 put
=COUNTIF(B2:F$9999,B1)

and formula drag this to column L, then, whilst still selected
bulk-formula drag down to cover all of your data. (F$9999 being the
end
of your data)
This counts the duplicates from this point onwards.

Count non-zero cells in H to L as required.

Hope this heps

--

Thomas O Wrote:
Cell A1 is the following text data "SCR: 27001.01, 27002.01,
27003.01,
27004.01, 27005.01."

Cell a2 is the following text data "SCR: 29001.01, 29002.01,
29003.01,
27004.01, 29005.01."

27004.01 is duplicated text in both cells.

Is there a formula or a macro to check for duplicated data (that
has
been
entered as text)?


--
Thomas


--
Bryan Hessey

------------------------------------------------------------------------
Bryan Hessey's Profile:
http://www.excelforum.com/member.php...o&userid=21059
View this thread:
http://www.excelforum.com/showthread...hreadid=537052




--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=537052