Excel Question

Hopefully there is something simple that someone knows that will save me a bunch of trouble.

I’ve got a file that I pulled from a mainframe and am trying to do some comparisons in Excel. They are phone numbers from a couple of sources that I’m trying to check if one source has been updated and not the other. The problem is that they are not formatted the same. Actually this isn’t the problem I’m posting about, because I’ve already stripped out all of the spaces, /'s, etc. and have appended country codes etc so I have two strings of numbers (actually a character string that contains mainly numbers) to compare. I’ve then used an If function to tell me which match and which don’t.

Here is the real problem. Many of them appear to match, but my If function returns “Bad” (my un-matched value). But when I double click on one of the cells it “refreshes” and I suddenly get a “Match.” Rather than double clicking on 3,000 cells or so, is there any way to double click the entire column? I’ve tried things like double clicking the column header, cutting and pasting, cutting and pasting special-values, re-calculating the spreadsheet, and nothing works other than double clicking the cells.

Somebody surely has a quick and easy solution. I’m using Excel 97 if that helps.

Wild guess: you may have your “calculation” control set to manual (use your pull-down menus: Tools/Options, then the “Calculation” tab, at the top of that tab). If so you can either reszet it to “Automatic” or just hit F9 to “Calculate Now”. Then again, your fix could be something entirely different. Good Luck!

If cybersnark’s fix doesn’t work, make sure you have the following Add-in’s enabled (done thru Tools>Add-ins in your menu bar)

Analysis ToolPack
Analysis ToolPack-VBA
Conditional Sum Wizard
Lookup Wizard
Solver Add-in

Thanks for the response, but nope… I’ve already checked that. It is set to automatic, and I even re-calculated it just for good measure.

Here is the latest that hopefully will help solve the problem. I decided that I could possibly import it into Access and maybe it would “fix” itself in the process. Actually it got worse and several of the “numbers” (it is actually a text field) got converted into what looks like scientific notation. Example: 8094761234 (not an actual number) got converted to 8.09477e+009.

Thanks anyway.

I don’t understand how double-clicking a cell somehow changes or recalculates its value. Maybe your solution hinges on discovering why this is happening.

Beats me as well. When I double click on it, the string shifts from left justified in the cell to right justified. As soon as I click on another cell it goes back to left justified. If I double click on an already “refreshed” cell it stays left justified. I’m now looking at a series that in the Excel sheet are in the scientific notation format, but once I click on them they revert to the regular number. As I said, these are actually character strings, but somehow in the process of moving the data from the mainframe to Excel things are getting confused. The data from the mainframe DB2 tables is character, I then FTP it to a fixed length text file, and then I used the import wizard in Excel to import it.

The only thing I’ve seen similar is if I accidentally put a formula into a cell that is formated to text. This gives me the formula spelled out in the cell. If I reformat it to general, the formula remains displayed until I double click the cell… then it returns the value.

Sorry Thunderbug, didn’t notice you snuck one in on me. Looks like I only have the Lookup add-in clicked. What will these others do for me?

Select all, then Format | Cells, and make them all numbers instead of general?

What will they do for you?! What will they do for you?!?! Actually, they probably won’t help now that you’ve explained it more. But they are good to have in, gives you more options on formulas and such.

We’ve actually had this problem here before when using Excel 97. 97 is fickle sometimes. I’m not exactly sure (means don’t remember :smack:) how we solved the problem other than going in and editing the cell (but F2 is quicker than double clicking). But I’ll throw out another suggestion or two.

Sounds like the numbers are gynormous from your Access attempt and for some reason Excel isn’t showing it as such. In Excel, use the ROUND function to get the numbers down to 0 decimal points,or 1 or 2 or however many you need in the column next to the numbers in question. =ROUND(A3,0) where A3 is the cell and 0 is the number of decimal places. Copy and paste down that formula so that all the numbers in question are covered, then do a Copy>Paste Special>Value to another column and use that column. That might do it.

If not, try the VALUE function. =VALUE(A3) is what it would look like (simple, eh?) and will convert text to numbers. Now that I think about it, you might want to try that first.

Are the numbers of uniform size, say 6 digits long? You can always use the LEFT function. =LEFT(A3,6) will give you the the first 6 characters of any string. So if you have 12345678ABC in A3, said formula would return 123456. You can then COPY>PASTE SPECIAL>VALUE to get the number as a number.

Already tried… didn’t work. I’m stumped, and I’m usually the one everyone comes to for troubleshooting.

I’ve been clicking away, and have my list down from 1,500 lines to 336 so I’m making headway. The only problem is I’m doing this for North America, and my counterparts in Latin America, Europe and Asia/Pacific will need to do the same thing soon and I’d like to be able to give them a procedure that works smoothly.

It might also help to reformat the cells to deal with the telephone numbers as text strings, rather than as numbers. That gets you away from the scientific notation problem (8.09477e+009, for example), and away from what I call the “fuzz” problem. Sometimes, even in Excel 2002, I can have a cell that has the result of the simple addition of several other cells. If I then copy that cell, and paste its value somewhere else (i.e., leaving the computation behind), the value comes out as, for example, 123.00000001, instead of the 123 that’s the actual result of the computation. Very weird.

Dang Thunderbug you snuck another in on me. My response was to VD (I even previewed and swear it wasn’t there).

I just tried Value and Round, and no luck. Unfortunately they are not all the same length so I had already tossed that option.

F2 though… now that is a winner. Not perfect, but better than double clicking. There has got to be some way to F2 the whole sheet (although select all and F2 didn’t work).

Early Out… they already are text strings. If I would have imported them any other way they would have tried to calculate the numbers (i.e. 888/555-1234 would have been -1232.4). I defined them specifically as text as I was importing them. That is why I was surprised to find several showing as scientific notation after the import (this was actually the case before I bumped it into and out of Access… I deleted that copy). I tried changing them from Text to Numbers and Back, cutting and pasting, copying and pasting, you name it… all with no luck. So far the only thing that works is to double click them.

The “fuzz” problem is a result of floating point arithmetic. It can only keep track of so many digits. Place the following numbers in separate cells and add them in that order:
+10,000,000,003.30 , -10,000,000,000.00
The result given by excel is 3.299999237

Also, if you want a string of numbers to be treated as a string and not as a number all you have to do is preface it with a single apostrophe: '2028322978

There are ways to handle this issue in Excel but…

I’d recommend sticking to Access, Excel is a great tool for handling numbers, but as you noted yourself, tel no.s are not ideal handled as numerical data ! (especially when there are + and - characters involved)

When you used Access, did you import the data, or did you copy/paste ? Either way, if you make sure all the data fields are defined as text only your problem should dissappear…

Ideally I’d use the data import utility, and define the fields that way

On occasions where I’ve still had trouble even after checking that all the fields are text data w/ no extras, a quick and dirty is to copy/paste from Excel into a word processor (notepad or whatever) and then copy into access - this has always worked for me.

good luck !

I’m very, very sneaky.

And Excel 97 can be very, very fickle. I’ll try and remember what exactly we did and get back to you tomorrow. Hopefully it won’t be too late for you.

I had this problem before, too.

Take a look in the help files at the TEXT worksheet function. It has the line in it, “Formatting a cell with an option on the Number tab (Cells command, Format menu) changes only the format, not the value. Using the TEXT function converts a value to formatted text, and the result is no longer calculated as a number.”

You said the phone numbers had different lenghts. Therefore the first thing I would do is use the LEN function to find out the longest phone number in your list. (Assume column A has the first phone number list, column B, the second).

In the next available column, enter =TEXT(a1,"#####") with the number of #'s the largest result of the LEN function.

Do the same in the next column over for the phone list in B1.

Now you should be able to compare the two on equal grounds. At least, all the tests I just ran showed that it should work…

How’s that for a last minute wiggle room?

I have the same problem with items I copy from a mainframe at work. Here’s my workaround: I copy the column in question into Windows Notepad, then cut the entire contents from Notepad and paste it back into Excel. Everything then converts into the appropriate format and is in the same order.