Same text basic setup

[Manolocs]

Hello I am trying to build a "formula" to show duplicated text between two rows:
The list is sorted alphabetically.
The column "A" is the only column that matters.
Some of the cells have a space in excess so the formula is false, any idea how make the formula dismiss the difference in spaces?
Would be nice to show both cells instead of only one when the formula is true.
Thanks in advance.

[FONT=Lucida Grande]=IF([/FONT][COLOR=#0057D6][FONT=Lucida Grande]D2[/FONT][/COLOR][FONT=Lucida Grande]=[/FONT][COLOR=#006107][FONT=Lucida Grande]D3[/FONT][/COLOR][FONT=Lucida Grande],"Delete"," ")[/FONT]

 

[etaf]

try using a countif ()

=countif(D:D, D2) >1
that will show where there is more than 1

you can add an IF to use the "delete" text if required

you say

The column "A" is the only column that matters.

but your testing column D

where are the spaces - leading trailing or somewhere in between
so is

"AA AA" the same as "AAAA"
" BB BB" the same as "BBBB "

 

[Manolocs]

Hi etaf, The spaces normally are at the beginning or at the end of the text " AAAA" "AAAA ".
your formula still can not show with different spaces at front and end.
Instead of False/True any other way to better visualize the different cells?
Thanks for your time.

 

[etaf]

=IF( countif(D:D, D2) >1 , "delete", "")

i can get the 2nd duplicate to work using

=countif(D:D, "*"&D2&"*") >1

but not identify the first duplicate for some reason

 

[Manolocs]

Still does not work

 

[Peter_SSs]

Could you show us some sample data and expected results? If there is a problem showing spaces, use a "." in place of a space.

 

[Manolocs]

Could you show us some sample data and expected results? If there is a problem showing spaces, use a "." in place of a space.

The formula does not work when: ".AAAA" or "AAAA...." "AAAA."

 

[Peter_SSs]

The formula does not work when: ".AAAA" or "AAAA...." "AAAA."

One example doesn't show much. That also doesn't show layout or expected results. Your earlier formula had "Delete" as one possible outcome. There is no sign of that here.

Could we see say 10 rows of data and the expected results, including layout?

 

[Manolocs]

please take a look in this model of data there are many duplicates in here.
Thanks for your time Peter_SSs,

https://dl.dropboxusercontent.com/u/14313867/some questions.xlsx