VLOOKUP and #N/A Values

[rkingndayton]

Hello all - new to the forum and would like to start out by saying hello and thanks for hosting this forum.

I'm using Windows 7 64-bit and Excel 2010

The project I'm working on is for inventory control in a distributorship. I'm trying to use the VLOOKUP function to find a description and 12-month usage of a part number.

The problem I'm having is that the value comes back as #N/A UNLESS I go to the table and reenter the value (I changed it from text to general with no luck). I definitely don't have time to reenter every value (over 6,000 part numbers!)

Here's my formula: "=VLOOKUP(A2,'RUNNING 12 JUL 10 - JUN 11 CGS '!A2:AK1000,2,FALSE)"

I did a search through the forums and couldn't find that anyone was having the same issue. I'm assuming this is a rather easy fix, or maybe there's an easier, more effective way.

My goal is to use the data to automatically create a graph showing each items' usage.

 

[328mike]

If your part number information is coming out as a string you can put the number 1 in any cell, copy it and select your range. Do a paste special and select multiply, it will turn any string information into a number and in doing so should also remove any spaces you have or had in your data.

If that doesn't do it for you and spacing is still an issue write a for loop in vba. Not entirely sure on the syntax here but this is the gist of what you would do.

'Variable declaration
i As Long
DataRange as Long

DataRange = Application.WorksheetFunction.CountA(Range("A:A"))
For i = 1 To DataRange
Trim(Cells(1 + i, 2))
Next i

 

[rkingndayton]

WOW!!! T. Valko's macro worked!

Sorry if I sound a little too excited but that's the first time I've ever ran a macro lol.

This fixed everything and I learned something in the process.

Thank you sooo much!