Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
When data look exactly the same but...
Hi all,
What's the cause of incidents when data in two cells, looking exactly the same, are not recognised by Excel as the same? It happens quite often when: - vlookup function doesn't return any value even though the lookup_value exist and looking exactly the same in lookup table; - the same data appears multiple times when you do advanced filtering and check "unique records only" checkbox; etc. etc. I've looked at usual suspects, like numbers entered as text?, space at the end of the record?, so on so forth, but, believe it or not, sometimes, these things are exactly the same, at least to my naked eyes, and still Excel seems to think they are different. What other causes can there be in cases like this? Thanks for your help. Cheers, -- Maki @ Canberra.AU |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
When data look exactly the same but...
Try selecting the whole column of the Lookup_value and go to Data / Text to
Columns - select Delimeted then Finish (if your data is text you will need to select text before Finish utherwiseyou will lose preceeding zeros) Do the same on the column you are looking up the vlookup can be in place as you do it and if successful the data will come through when you do the text to columns you will see the data format "Maki" wrote: Hi all, What's the cause of incidents when data in two cells, looking exactly the same, are not recognised by Excel as the same? It happens quite often when: - vlookup function doesn't return any value even though the lookup_value exist and looking exactly the same in lookup table; - the same data appears multiple times when you do advanced filtering and check "unique records only" checkbox; etc. etc. I've looked at usual suspects, like numbers entered as text?, space at the end of the record?, so on so forth, but, believe it or not, sometimes, these things are exactly the same, at least to my naked eyes, and still Excel seems to think they are different. What other causes can there be in cases like this? Thanks for your help. Cheers, -- Maki @ Canberra.AU |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
When data look exactly the same but...
"Maki" wrote:
What's the cause of incidents when data in two cells, looking exactly the same, are not recognised by Excel as the same? Sorry, but my mindreading powers are weakened by the kryptonite sitting next to me. Exactly how are you using VLOOKUP? What is the lookup value? What values are in the lookup table? How are the values derived? For example, is text imported; are numbers the result of formulas? If you are looking at numbers with decimal fractions that are the result of formulas, it is quite common that the displayed value is not exactly equal to the underlying actual value. If you are looking at text that was imported, it is quite common that what appears to be spaces are actually a non-breaking spaces (NBSPs), ASCII code 160. There are ways of dealing with both. But it really is not worth the time to explore each problem, since there is no way to know which one applies to your situation, if either does. ----- original message ----- "Maki" wrote: Hi all, What's the cause of incidents when data in two cells, looking exactly the same, are not recognised by Excel as the same? It happens quite often when: - vlookup function doesn't return any value even though the lookup_value exist and looking exactly the same in lookup table; - the same data appears multiple times when you do advanced filtering and check "unique records only" checkbox; etc. etc. I've looked at usual suspects, like numbers entered as text?, space at the end of the record?, so on so forth, but, believe it or not, sometimes, these things are exactly the same, at least to my naked eyes, and still Excel seems to think they are different. What other causes can there be in cases like this? Thanks for your help. Cheers, -- Maki @ Canberra.AU |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
When data look exactly the same but...
Assuming you are not finding leading or trailing spaces and the information
being looked up is numeric in nature then I would suggest that the issue is most likely text and numbers. If cells with numeric data are being stored as text just chaning the cell format does not change the underlying text to numbers. You still need to convert the text to numbers. Easiest wasy is to place a 1 in an unused cell Copy it and paste special | Multiply over the range of numbers. To catch this error in your vlookup you can use countif to distinguish text from numbers. Countif treats everything as text so it will find matches when one cell is text and the other number. =if(countif(A1:A10, D1) = 0, "Not Found", vlookup(D1, A1:B10, 2, 0)) If countif finds a match then it proceeds to the vlookup. If the vlookup returns an error then you have a data type mismatch between text and number. -- HTH... Jim Thomlinson "Maki" wrote: Hi all, What's the cause of incidents when data in two cells, looking exactly the same, are not recognised by Excel as the same? It happens quite often when: - vlookup function doesn't return any value even though the lookup_value exist and looking exactly the same in lookup table; - the same data appears multiple times when you do advanced filtering and check "unique records only" checkbox; etc. etc. I've looked at usual suspects, like numbers entered as text?, space at the end of the record?, so on so forth, but, believe it or not, sometimes, these things are exactly the same, at least to my naked eyes, and still Excel seems to think they are different. What other causes can there be in cases like this? Thanks for your help. Cheers, -- Maki @ Canberra.AU |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Labels- POssible to show data value and data label together? | Charts and Charting in Excel | |||
Increase Your Business By Data Conversion, Data Format and Data EntryServices in India | Excel Worksheet Functions | |||
Save 20% On Data Conversion and Data Formats Services by Data EntryIndia | Excel Discussion (Misc queries) | |||
Data Entry Online, Data Format, Data Conversion and Data EntryServices through Data Entry Outsourcing | Excel Discussion (Misc queries) | |||
MULTIPLE DATA - How to insert new data into existing data.... | Excel Discussion (Misc queries) |