I have a column of data that is in text format:

ex:

'0215
'6251
'5489
'2351
'6350
'5126

How can I reference this data in another cell while keeping the same format.
For example:

I am trying to extract all of the unique values in my column of data by
using the formula I found on cpearson (=IF(COUNTIF($A$1:A1,A1)=1,A1,"")) This
formula works great but the data that it returns does not contain the
apostrophy in front of the number which is needed. I have tried having the
cell be equal to "'"&A1 if the formula is true but the the apostrophy is
visible and still doesn't wok with the sume product. Any help on this would
be greatly appreciated.

Chad

=IF(COUNTIF($A$1:A1,A1)=1,"'"&A1,"")


--
Regards,
Dave


"Chad" wrote:

I have a column of data that is in text format:

ex:

'0215
'6251
'5489
'2351
'6350
'5126

How can I reference this data in another cell while keeping the same format.
For example:

I am trying to extract all of the unique values in my column of data by
using the formula I found on cpearson (=IF(COUNTIF($A$1:A1,A1)=1,A1,"")) This
formula works great but the data that it returns does not contain the
apostrophy in front of the number which is needed. I have tried having the
cell be equal to "'"&A1 if the formula is true but the the apostrophy is
visible and still doesn't wok with the sume product. Any help on this would
be greatly appreciated.

Chad

David,
Thanks for the quickl reply. Like I said below, I have already tried
this approach but unfortunately that returns me the wrong value. In the cell,
instead ofreturning the origionall number with a green arrow in the
upper-left corner, it just returns the number with an apostrophy in front of
it. Please let me know if you think of anything else.

Thanks,

"David Billigmeier" wrote:

=IF(COUNTIF($A$1:A1,A1)=1,"'"&A1,"")


--
Regards,
Dave


"Chad" wrote:

I have a column of data that is in text format:

ex:

'0215
'6251
'5489
'2351
'6350
'5126

How can I reference this data in another cell while keeping the same format.
For example:

I am trying to extract all of the unique values in my column of data by
using the formula I found on cpearson (=IF(COUNTIF($A$1:A1,A1)=1,A1,"")) This
formula works great but the data that it returns does not contain the
apostrophy in front of the number which is needed. I have tried having the
cell be equal to "'"&A1 if the formula is true but the the apostrophy is
visible and still doesn't wok with the sume product. Any help on this would
be greatly appreciated.

Chad

In your post you said the apostrophe IS needed to be returned in front of the
returned value:

"The data that it returns does not contain the apostrophy in front of the
number which is needed"

So, what you actually meant, then, is you need the returned value to be
formatted as text, correct? If this is the case use this:

=IF(COUNTIF($A$1:A1,A1)=1,TEXT(A1,"0000"),"")

If you were to copy-"paste values" on this range you will get those green
arrows you are talking about.

--
Regards,
Dave


"Chad" wrote:

David,
Thanks for the quickl reply. Like I said below, I have already tried
this approach but unfortunately that returns me the wrong value. In the cell,
instead ofreturning the origionall number with a green arrow in the
upper-left corner, it just returns the number with an apostrophy in front of
it. Please let me know if you think of anything else.

Thanks,

"David Billigmeier" wrote:

=IF(COUNTIF($A$1:A1,A1)=1,"'"&A1,"")


--
Regards,
Dave


"Chad" wrote:

I have a column of data that is in text format:

ex:

'0215
'6251
'5489
'2351
'6350
'5126

How can I reference this data in another cell while keeping the same format.
For example:

I am trying to extract all of the unique values in my column of data by
using the formula I found on cpearson (=IF(COUNTIF($A$1:A1,A1)=1,A1,"")) This
formula works great but the data that it returns does not contain the
apostrophy in front of the number which is needed. I have tried having the
cell be equal to "'"&A1 if the formula is true but the the apostrophy is
visible and still doesn't wok with the sume product. Any help on this would
be greatly appreciated.

Chad