I have successful Vlookup formulas and duplicate values between multiple
columns in a multipage spreadsheet. However, when I try to compare by
duplicate value a third set of 2 columns, Excel 2007 sees the entries as
unique while they are clearly duplicates. The only thing I see different in
this comparison over the other column comparisons is that I am using a
formula =LEFT(J3, SEARCH(" ",J3,SEARCH(" ",J3,1)+1)) in one of the columns.
Any suggestions?

Venturing some thoughts. It could be just data inconsistency issues throwing
apparent duplicates recognition off. Eg: extraneous white spaces somewhere
which are hard-to-see/detect. Try TRIM to handle this, try replacing "J3"
with TRIM(J3) in your LEFT expression. Another possibility is text numbers
are being compared with equivalent real numbers. The comparison will fail.
LEFT per se will return "nums" as text numbers. You could try a "+0" to
coerce it to real numbers to enable correct matching, eg: LEFT(...)+0
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Ed2" wrote:
I have successful Vlookup formulas and duplicate values between multiple
columns in a multipage spreadsheet. However, when I try to compare by
duplicate value a third set of 2 columns, Excel 2007 sees the entries as
unique while they are clearly duplicates. The only thing I see different in
this comparison over the other column comparisons is that I am using a
formula =LEFT(J3, SEARCH(" ",J3,SEARCH(" ",J3,1)+1)) in one of the columns.
Any suggestions?

Thank you Max. I have actually checked for spaces and text/numbers
differentials. I tried the TRIM idea anyway but to no avail.

"Max" wrote:

Venturing some thoughts. It could be just data inconsistency issues throwing
apparent duplicates recognition off. Eg: extraneous white spaces somewhere
which are hard-to-see/detect. Try TRIM to handle this, try replacing "J3"
with TRIM(J3) in your LEFT expression. Another possibility is text numbers
are being compared with equivalent real numbers. The comparison will fail.
LEFT per se will return "nums" as text numbers. You could try a "+0" to
coerce it to real numbers to enable correct matching, eg: LEFT(...)+0
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Ed2" wrote:
I have successful Vlookup formulas and duplicate values between multiple
columns in a multipage spreadsheet. However, when I try to compare by
duplicate value a third set of 2 columns, Excel 2007 sees the entries as
unique while they are clearly duplicates. The only thing I see different in
this comparison over the other column comparisons is that I am using a
formula =LEFT(J3, SEARCH(" ",J3,SEARCH(" ",J3,1)+1)) in one of the columns.
Any suggestions?

Try Tools / Formula Auditing / Evaluate Formula and see if each section of your
formula evaluates as you expect it to.

Ed2 wrote:
Thank you Max. I have actually checked for spaces and text/numbers
differentials. I tried the TRIM idea anyway but to no avail.

"Max" wrote:

Venturing some thoughts. It could be just data inconsistency issues throwing
apparent duplicates recognition off. Eg: extraneous white spaces somewhere
which are hard-to-see/detect. Try TRIM to handle this, try replacing "J3"
with TRIM(J3) in your LEFT expression. Another possibility is text numbers
are being compared with equivalent real numbers. The comparison will fail.
LEFT per se will return "nums" as text numbers. You could try a "+0" to
coerce it to real numbers to enable correct matching, eg: LEFT(...)+0
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Ed2" wrote:
I have successful Vlookup formulas and duplicate values between multiple
columns in a multipage spreadsheet. However, when I try to compare by
duplicate value a third set of 2 columns, Excel 2007 sees the entries as
unique while they are clearly duplicates. The only thing I see different in
this comparison over the other column comparisons is that I am using a
formula =LEFT(J3, SEARCH(" ",J3,SEARCH(" ",J3,1)+1)) in one of the columns.
Any suggestions?

Glenn, Thank you for your response. As I've stepped through randomly chosen
lines of the formulas =LEFT(J3, SEARCH(" ",J3,SEARCH(" ",J3,1)+1)) and the
formulas =VLOOKUP(BB_Users!$A1,BB_Users!$A$1:$L$2000,1,FALS E) they are
working fine. As I visually check the results, they are also fine. What is
really perplexing is that when I apply a conditional formula to the columns
it recognizes that they are unique and highlights them and it is consistant
for the 700 lines throughout the column.

"Glenn" wrote:

Try Tools / Formula Auditing / Evaluate Formula and see if each section of your
formula evaluates as you expect it to.

Ed2 wrote:
Thank you Max. I have actually checked for spaces and text/numbers
differentials. I tried the TRIM idea anyway but to no avail.

"Max" wrote:

Venturing some thoughts. It could be just data inconsistency issues throwing
apparent duplicates recognition off. Eg: extraneous white spaces somewhere
which are hard-to-see/detect. Try TRIM to handle this, try replacing "J3"
with TRIM(J3) in your LEFT expression. Another possibility is text numbers
are being compared with equivalent real numbers. The comparison will fail.
LEFT per se will return "nums" as text numbers. You could try a "+0" to
coerce it to real numbers to enable correct matching, eg: LEFT(...)+0
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Ed2" wrote:
I have successful Vlookup formulas and duplicate values between multiple
columns in a multipage spreadsheet. However, when I try to compare by
duplicate value a third set of 2 columns, Excel 2007 sees the entries as
unique while they are clearly duplicates. The only thing I see different in
this comparison over the other column comparisons is that I am using a
formula =LEFT(J3, SEARCH(" ",J3,SEARCH(" ",J3,1)+1)) in one of the columns.
Any suggestions?

Max, Thank you. You're solution of the Trim function worked....I was
misusing the function. =TRIM(LEFT(J3,SEARCH(" ",J3,SEARCH(" ",J3,1)+1)))
solved the problem. Thank you again


"Max" wrote:

Venturing some thoughts. It could be just data inconsistency issues throwing
apparent duplicates recognition off. Eg: extraneous white spaces somewhere
which are hard-to-see/detect. Try TRIM to handle this, try replacing "J3"
with TRIM(J3) in your LEFT expression. Another possibility is text numbers
are being compared with equivalent real numbers. The comparison will fail.
LEFT per se will return "nums" as text numbers. You could try a "+0" to
coerce it to real numbers to enable correct matching, eg: LEFT(...)+0
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Ed2" wrote:
I have successful Vlookup formulas and duplicate values between multiple
columns in a multipage spreadsheet. However, when I try to compare by
duplicate value a third set of 2 columns, Excel 2007 sees the entries as
unique while they are clearly duplicates. The only thing I see different in
this comparison over the other column comparisons is that I am using a
formula =LEFT(J3, SEARCH(" ",J3,SEARCH(" ",J3,1)+1)) in one of the columns.
Any suggestions?

Good to hear, Ed2. Pl take a moment to press the YES button (like the one
below) in all responses which helped.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Ed2" wrote:
Max, Thank you. You're solution of the Trim function worked....I was
misusing the function. =TRIM(LEFT(J3,SEARCH(" ",J3,SEARCH(" ",J3,1)+1)))
solved the problem. Thank you again