Duplicate Part of a cell

  • Thread starter Thread starter Shawn
  • Start date Start date
S

Shawn

Greetings - am trying to create a formula, possibly an
array formula, that compares the last four digits of a
text string to others in the same range, and let's me
know if the last four digits match any of the others.
There are blanks in the range as well. For example:

Red(BD)
blank cell
Yellow(ZZ)
blank cell
blank cell
Yellow(BD)

This would be considered a duplicate because the first
entry and the last entry both end with (BD). BTW, all
entries have the same pattern, that is - (**).

Any suggestions?
Thanks in advance for any time spent on this formula,
Shawn
 
Shawn

Try this:

=IF(SUM(IF(RIGHT(D5,4)=RIGHT($D$5:$D$10,4),1,0))>1,IF(LEN
(RIGHT(D5,4))=0,"","DUPLICATE"),"NOT DUPLICATE")

It is an array formula, so commit with CTRL+SHFT+Enter
It will ignore blank cells.


Cheers
Juan
 
Juan,
Outstanding. Works great. I don't understand why it uses
the SUM function (summing a text string = 0, correct?),
but it works like a charm.

One last question - it doesn't work if the first cell in
the range is blank. I don't know if this is an issue yet
with this workbook, but I thought I would ask - do you
have a solution if the first cell of the range does not
contain text?

If not, I am still happy with the results.
Thanks so much Juan-
Shawn
 
In regards to the previous email, I guess I should be
more specific. "Doesn't work" isn't totally correct. The
part of the formula: *IF(LEN>(RIGHT(D5,4))=0,""* does
check for a blank space in the first cell, and returns
nothing. But that wasn't what I had in mind... I was
hoping it wouldn't matter whether or not the first cell
contained any text... it appears there are some points in
the ranges where the first cell is blank... but the next
month it may contain text....
 
Shawn

It does ignores If the Cell you are trying to evaluate is
blank. Basically what whis formula is doing is checking to
see if the first cell in the range (D5) in this case is
duplicated. If you want the formula to check if there is a
duplicate value with in the range regardless of the
position we need something different.

Lets try this:

If you want to check the first(or any other value) on the
range, to see if it is duplicated, and want to know if the
cell your are trying to evaluate is a blank cell, if there
are more blanck cells use this:

=IF(SUM(IF(RIGHT(D5,4)=RIGHT($D$5:$D$10,4),1,0))
1,"DUPLICATE","NOT DUPLICATE")
Click to expand...

If you just want to check the range to see if there are
any duplicate values with in, ignoring blanks, meaning
that something like this:

Blank
Red(ZZ)
Blank
RED(BA)
Yello(YZ)
Blank
Blank
Yellow(BA)

Would be call a duplicate because of the Red (BA) & the
Yellow(BA) then use something like this:

=IF(SUM(IF(FREQUENCY(IF(LEN(RIGHT(MyRange,4))>0,MATCH(RIGHT
(MyRange,4),RIGHT(MyRange,4),0),""),
IF(LEN(RIGHT(MyRange,4))>0,MATCH(RIGHT(MyRange,4),RIGHT
(MyRange,4),0),""))>0,1))=(ROWS(MyRange)-SUM(IF(ISBLANK
(MyRange),1,0))),"NOT DUPLICATE","DUPLICATE")

It took me a wile to figure it out, and I used some of the
formulae posted at
http://www.cpearson.com/excel/duplicat.htm#CountingUnique

You might want to chek it out...

You might want to NAME the range its a lot easier...

Cheers
Juan
 
All I can say: sweeeeet. Thanks Juan. Works perfectly. I
too went to Chip's site, but wasn't able to piece it
together from his information - just a little above my
head. It will take me awhile myself just to analyze this
formula - try to figure out how it works.

Thanks for all of your time!!! Very helpful.
Shawn
 
Shawn...

Thanks for taking the time to post back with thanks!!!

Glad to help..

Cheer
Juan
 
Back
Top