Helping you out!

Question:

Anonymous said on 1/7/08:

"I have two fields that I need to compare and bring back data in a third field if they match.

Basically, it's a simple VLOOKUP equation.
But, these two number fields (which are currently both formatted as text) don't always bring back the data, unless I 'retype' the data, then it recognizes that they are the same.
Can you think of why I would need to retype the exact same data for it to recognize it?

I'm bringing in the data via copy/paste, but using field format options to ensure they're both the same."

You're using the field format option to ensure they are both the same, and that is definitely the right thing to do. You may just have to take it a step further.

OPTION 1

When you bring in the data via copy/paste, choose Paste Special and paste as values.  If that doesn't work, go back to the original lookup value, select that column, copy it and paste it special as values (don't paste it somewhere else.  Paste it right where it is).

OPTION 2

If the steps in Option 1 don't work, take it to the next level by formatting the fields so they are exactly the same using the Data-->Text to columns wizard.

1. Select the column that contains the lookup value.


2. Click on Data-->Text to Columns.  This will bring up a "Convert Text to Columns Wizard."


3. The first step is to choose whether your data is "delimited" or "fixed width."  Choose delimited here.


4. Second step is to either choose the delimiter or the width of your text.  Make it as long as your longest data.


5. Third step is to choose the data format.  Make it a number or a text, or whatever makes the most sense to you.

Now, select the first column in your table array and repeat the steps, ensuring that you set up this column of data precisely the same way you set up your lookup value.  You may need to play with the options in each of the three steps.  Choosing delimited always worked for me.  But, that might not work for you.

OPTION 3

Another thing that may be hinking you up are spaces.  If your lookup values should not contain spaces, you can do this:

1. Select the column that contains the lookup value.


2. Hit Ctrl-F (or Ctrl-H to save step 4...either works exactly the same).


3. Enter a space in the "Find What" box.


4. Hit the replace tab.


5. Enter nothing in the "Replace with" box.


6. Hit "Replace All"


7. Repeat these steps the first column in your table array.

The first option might do the trick.  The second option should work all the time (I hope.  It's always worked for me.)  The third option is a little easier, but you have to be careful and you can't use this if your lookup data is suppose to contain spaces.

Let me know if you need pictures, and let me know if this works (or not)!