Home |
Search |
Today's Posts |
#1
|
|||
|
|||
vlookup cell format problems
I have a spreadsheet with a column of serial numbers comprised of both
numbers and letters. I have formatted the column to "general" and am comparing it to another spreadsheet for matches. When I create my vlookup formula I get partial matches. Many of the responses show #N/A but if I double click on the original serial number column and go down the list cell by cell hitting F2 and Enter, then the match shows up where it should have before. I have tried several ways to ensure the columns I am comparing are both formated as "general". Some of my columns are over 6000 rows long and I cannot go row by row hitting F2 and Enter. It appears there is some residual formatting hanging which is not allowing the column to be truly formatted as "general". Any ideas how to force the formatting to "general"? |
#2
|
|||
|
|||
I don't think it is related to the General format since that only
impacts the display properties of the cell. It sounds to me like the second set of data may have been imported from another application. I often ran into this when copying the results of an Access query into Excel. Excel would think the numbers were really characters. There are a couple of fixes available. I don't recall the exact fix so you give them a try. Perform a Copy - Paste Special/Values. (I assume you know how. The quick version is highlight the column, pick Edit | Copy then Edit | Paste Special | Values and press OK. Then escape to unselect the column. Multiple all values by 1. In a separate column, enter a column of 1s. Highlight the first column, pick Edit | Copy, then select the column with 1s and pick Edit | Paste Special | Multiply and press OK. Delete the first column. Hope that helps. - John |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
cell color index comparison | New Users to Excel | |||
Custom cell format problems | Excel Discussion (Misc queries) | |||
How do I copy data in single cell format to a merged cell format | Excel Discussion (Misc queries) | |||
can't format cell - have tried unlocking and unprotecting | Excel Discussion (Misc queries) | |||
Problems with cell formatting | Excel Discussion (Misc queries) |