software

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

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

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:
gt; Cell A1 is the following text data quot;SCR: 27001.01, 27002.01,
gt; 27003.01,
gt; 27004.01, 27005.01.quot;
gt;
gt; Cell a2 is the following text data quot;SCR: 29001.01, 29002.01, 29003.01,
gt; 27004.01, 29005.01.quot;
gt;
gt; 27004.01 is duplicated text in both cells.
gt;
gt; Is there a formula or a macro to check for duplicated data (that has
gt; been
gt; entered as text)?
gt;
gt;
gt; --
gt; Thomas--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: www.excelforum.com/member.php...oamp;userid=21059
View this thread: www.excelforum.com/showthread...hreadid=537052Bryan - thanks. Its gonna take me a while to filter the info you provided
thru my brain (that was English you used, wasn't it?)
--
Thomasquot;Bryan Hesseyquot; wrote:

gt;
gt; Assuming this is the only column on the worksheet (12 helper columns
gt; used for the data shown)
gt;
gt; in column B put =Left(A1,Len(A1)-1)
gt; to drop the trailing dot (full stop)
gt;
gt; Select column B and Copy, Paste Special = Values back over itsself
gt; Delete column A
gt;
gt; Select Data Text to Columns and use Delimited with comma and space and
gt; treat consecutive separators as one.
gt;
gt; This should give data in columns B to F that can be tested.
gt;
gt; In H1 put
gt; =COUNTIF(B2:F$9999,B1)
gt;
gt; and formula drag this to column L, then, whilst still selected
gt; bulk-formula drag down to cover all of your data. (F$9999 being the end
gt; of your data)
gt; This counts the duplicates from this point onwards.
gt;
gt; Count non-zero cells in H to L as required.
gt;
gt; Hope this heps
gt;
gt; --
gt;
gt; Thomas O Wrote:
gt; gt; Cell A1 is the following text data quot;SCR: 27001.01, 27002.01,
gt; gt; 27003.01,
gt; gt; 27004.01, 27005.01.quot;
gt; gt;
gt; gt; Cell a2 is the following text data quot;SCR: 29001.01, 29002.01, 29003.01,
gt; gt; 27004.01, 29005.01.quot;
gt; gt;
gt; gt; 27004.01 is duplicated text in both cells.
gt; gt;
gt; gt; Is there a formula or a macro to check for duplicated data (that has
gt; gt; been
gt; gt; entered as text)?
gt; gt;
gt; gt;
gt; gt; --
gt; gt; Thomas
gt;
gt;
gt; --
gt; Bryan Hessey
gt; ------------------------------------------------------------------------
gt; Bryan Hessey's Profile: www.excelforum.com/member.php...oamp;userid=21059
gt; View this thread: www.excelforum.com/showthread...hreadid=537052
gt;
gt;


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)=quot;.quot;,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:
gt; Bryan - thanks. Its gonna take me a while to filter the info you
gt; provided
gt; thru my brain (that was English you used, wasn't it?)
gt; --
gt; Thomas
gt;
gt;
gt; quot;Bryan Hesseyquot; wrote:
gt;
gt; gt;
gt; gt; Assuming this is the only column on the worksheet (12 helper columns
gt; gt; used for the data shown)
gt; gt;
gt; gt; in column B put =Left(A1,Len(A1)-1)
gt; gt; to drop the trailing dot (full stop)
gt; gt;
gt; gt; Select column B and Copy, Paste Special = Values back over itsself
gt; gt; Delete column A
gt; gt;
gt; gt; Select Data Text to Columns and use Delimited with comma and space
gt; and
gt; gt; treat consecutive separators as one.
gt; gt;
gt; gt; This should give data in columns B to F that can be tested.
gt; gt;
gt; gt; In H1 put
gt; gt; =COUNTIF(B2:F$9999,B1)
gt; gt;
gt; gt; and formula drag this to column L, then, whilst still selected
gt; gt; bulk-formula drag down to cover all of your data. (F$9999 being the
gt; end
gt; gt; of your data)
gt; gt; This counts the duplicates from this point onwards.
gt; gt;
gt; gt; Count non-zero cells in H to L as required.
gt; gt;
gt; gt; Hope this heps
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Thomas O Wrote:
gt; gt; gt; Cell A1 is the following text data quot;SCR: 27001.01, 27002.01,
gt; gt; gt; 27003.01,
gt; gt; gt; 27004.01, 27005.01.quot;
gt; gt; gt;
gt; gt; gt; Cell a2 is the following text data quot;SCR: 29001.01, 29002.01,
gt; 29003.01,
gt; gt; gt; 27004.01, 29005.01.quot;
gt; gt; gt;
gt; gt; gt; 27004.01 is duplicated text in both cells.
gt; gt; gt;
gt; gt; gt; Is there a formula or a macro to check for duplicated data (that
gt; has
gt; gt; gt; been
gt; gt; gt; entered as text)?
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; Thomas
gt; gt;
gt; gt;
gt; gt; --
gt; gt; Bryan Hessey
gt; gt;
gt; ------------------------------------------------------------------------
gt; gt; Bryan Hessey's Profile:
gt; www.excelforum.com/member.php...oamp;userid=21059
gt; gt; View this thread:
gt; www.excelforum.com/showthread...hreadid=537052
gt; gt;
gt; gt;--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: www.excelforum.com/member.php...oamp;userid=21059
View this thread: www.excelforum.com/showthread...hreadid=537052