dslreports logo
site
 
    All Forums Hot Topics Gallery
spc

spacer




how-to block ads


Search Topic:
uniqs
1899
share rss forum feed


Ian
Premium
join:2002-06-18
ON
kudos:3

[Excel] Logical Test Problem

I’ve got a problem with some sheets that are attempting to filter parts by part number (vendors or customers) to find an Internal part number match.




The comparison field (J3 in this example) is pulled from an Input sheet. The cells (both J3 and the referred cell) are formatted “General” as the part number can take any form of Alpha-Numeric system. Cell L3 in this example is a “Vendor P/N” pulled from another sheet that has imported data from a SQL data source. Again, the cells are formatted general. Cell M1 here is a logical test to see if J3=L3. Specifically “=IF(J3=L3,1,0)”. As you can see, the formula returns a false value of “0”, when it should state there is a match. The scheme works fine as long as there is just one Alphabetic character in the data to be matched. And a simple test of the two fields, ie =J3*2, and =L3*2 yield identical results. We’ve tried formatting the cells in question all different ways, text, number, etc.. with no luck.

Any ideas?
--
"Idealism is based on big ideas. And, as anybody who has ever been asked "What's the big idea?" knows, most big ideas are bad ones."
O'Rourke, P.J.


Ian
Premium
join:2002-06-18
ON
kudos:3
I know I'm replying to my own post, but I think I found a potential work around. I made a function to search one cell to see if it contained the text of another. This seems to make the filter happy even if Excel does NOT, for some bizarre reason think that they're equivalent. The down side is that a partial part number will return "hits" as a match i.e. "AB606" will return matches from "LAB6062" and "AB6062234" etc....

=IF(E6="","",IF($P$1=0,"",IF(ISNUMBER(SEARCH($P$1,T6)),"","*")))
--
"Idealism is based on big ideas. And, as anybody who has ever been asked "What's the big idea?" knows, most big ideas are bad ones."
O'Rourke, P.J.


2kmaro
Think
Premium,ExMod 1 BC
join:2000-07-11
ColossalCave
kudos:1

1 edit

1 recommendation


Mixed Comparison Formula
In looking at your screen shot, and understanding that you have columns J and L formatted as General, it appears that J3 is being treated as a Number (check to see what the indicator says if you click the green triangle in upper left corner of J3 - a yellow diamond will appear that you can click to see what the 'problem' is). Meanwhile, it appears that what has been placed into L3 is being being treated as Text.

Since your values can be either 'pure' numbers as "1234" or alpha-numeric entries as "a1234" or "12a44-66c" both cells that you carry out these tests against should be formatted as TEXT, not General.

Note that if you format the entire column as Text, as you try to add formulas into cells later, the formula will show up as the content, not the results. You'll have to change the format of those cells to General, enter the formula, change back to Text.

But this solution may work for both General/Text or one column one way, the other in the other format. See my screen shot.

Notice that I have a number being compared to a text entry representing that same number. Your test (column M) shows that they are different, but my modification of that formula in N3 comes up with the expected result!! N2 shows what the formula in N3 is (as does M2 for M3).

It may be hard to read in the screen shot, so I'll repeat it here, using periods to provide some spacing between the characters - there are NO periods/dots in the actual formula.

=IF(("..'.."..&..J3)=("..'.."..&..L3),1,0)

so the important section of each for the change is
double-quote|single-quote|double-quote|ampersand
in front of the cell references. What that's doing, in effect, is converting both to text for the comparison testing regardless of what Excel thinks they are when it looks at the values in the cells themselves.

Hope this helps some.
--
...then THINK! again.


Ian
Premium
join:2002-06-18
ON
kudos:3
said by 2kmaro:

What that's doing, in effect, is converting both to text for the comparison testing regardless of what Excel thinks they are when it looks at the values in the cells themselves.

Hope this helps some.
Perfect! That's great. Thanks 2kmaro See Profile.




We might still explore the "search" function as there may be times when we might want to search on a partial number, but this helps. Using Excel 2003 and there wasn't actually that yellow "dot" indicating the true format that Excel was treating it as. I wonder if my SQL query can be modified to return text as text and numbers as numbers..... Or if the database field itself has formatting issues in SQL Server 2005.
--
"Idealism is based on big ideas. And, as anybody who has ever been asked "What's the big idea?" knows, most big ideas are bad ones."
O'Rourke, P.J.