I believe I have tried everything and cannot get these particular two
worksheets to bring me the matching data - there are over 6,000 lines so I
really need to make it work.

I have part numbers that I ensured have no spaces at the end, one file came
from the ERA system so may have been different, but not I think they are both
'text'.
=VLOOKUP(A:A,Cdn!C:I,6,FALSE)

any suggestions?
thanks

Where is the data you trying to look up? You can't look up an entire column
in one formula. Maybe you want something like this:
=VLOOKUP(A1,Cdn!C:I,6,FALSE)

and copy down to the end of the column.

Regards,
Fred.


"mkcma" <u48424@uwe wrote in message news:8f41619ea2501@uwe...
I believe I have tried everything and cannot get these particular two
worksheets to bring me the matching data - there are over 6,000 lines so I
really need to make it work.

I have part numbers that I ensured have no spaces at the end, one file
came
from the ERA system so may have been different, but not I think they are
both
'text'.
=VLOOKUP(A:A,Cdn!C:I,6,FALSE)

any suggestions?
thanks

Thankx for your reply. I have two sheets in one workbook. I want to take
one or the other of them and bring over the matching price for each part that
has a price. Then I will compare them to determine if they are
same/different.

I have used the formula for an entire column before, but then did try yours
and copied it down.

It again didn't work???

mk

Fred Smith wrote:
Where is the data you trying to look up? You can't look up an entire column
in one formula. Maybe you want something like this:
=VLOOKUP(A1,Cdn!C:I,6,FALSE)

and copy down to the end of the column.

Regards,
Fred.

I believe I have tried everything and cannot get these particular two
worksheets to bring me the matching data - there are over 6,000 lines so I
[quoted text clipped - 9 lines]
any suggestions?
thanks

In article <8f43ca5cbde95@uwe, "mkcma" <u48424@uwe wrote:

It again didn't work???

Can you be a bit more specific about what "didn't work" means?

Did you get an error message? and error value? incorrect values? a crash?

What was your exact formula and data layout?

I know there are matching part numbers, and all of the formulas pasted down
show N/A. When I don't attach the last value in the formula as 'false' it
brings the closest matching part number, so I know a formula can work.

My data layout is:
Sheet 1 - Column A: Part #
Column B: Description
Column C: 5 Yr Warr USA
Column D: 5 Yr Warr CDN (this was a formula of column C - I
converted it
to paste special and it is now a valule.

Sheet 2 - Column C: Part #
Column D: Description
Column I: Cdn Price (This sheet downloaded from a program
called ERA)

On Sheet One I have put a formula as follows
=VLOOKUP(A1,Cdn!C:I,6,FALSE)




E McGimpsey wrote:
It again didn't work???

Can you be a bit more specific about what "didn't work" means?

Did you get an error message? and error value? incorrect values? a crash?

What was your exact formula and data layout?

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200812/1

try this

=VLOOKUP(A2,Sheet2!C:I,7,0)

A2 is the value you want to look up, this is in Sheet1
Sheet2!C:I is your table,
make sure that you have a sheet named "Cdn" exactly and that your column
number
is correct at 6 where you want the value to return.
In the above, I am using 7 as I found col I is column no 7

Does it works for you?

HTH

Pls click the Yes button below if this is helpful

cheers, francis

mkcma wrote:
Thankx for your reply. I have two sheets in one workbook. I want to take
one or the other of them and bring over the matching price for each part that
has a price. Then I will compare them to determine if they are
same/different.

I have used the formula for an entire column before, but then did try yours
and copied it down.

It again didn't work???

mk

Where is the data you trying to look up? You can't look up an entire column
in one formula. Maybe you want something like this:
[quoted text clipped - 10 lines]
any suggestions?
thanks

--
Message posted via http://www.officekb.com

No, sorry that didn't work either.

I am almost convinced (because i have done vlookups on tons of worksheets I
produced) that there is something wrong with the data (part numbers). Is
there any way to ensure that the two sheets have exactly the correct matching
format? (I presently have paint formatted all to text).

thanks,
mk

xlm wrote:
try this

=VLOOKUP(A2,Sheet2!C:I,7,0)

A2 is the value you want to look up, this is in Sheet1
Sheet2!C:I is your table,
make sure that you have a sheet named "Cdn" exactly and that your column
number
is correct at 6 where you want the value to return.
In the above, I am using 7 as I found col I is column no 7

Does it works for you?

HTH

Pls click the Yes button below if this is helpful

cheers, francis

Thankx for your reply. I have two sheets in one workbook. I want to take
one or the other of them and bring over the matching price for each part that
[quoted text clipped - 13 lines]
any suggestions?
thanks

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200812/1

If you are receiving #N/A as a result when you use FALSE as the fourth
parament, but not when you use TRUE, then the problem them is your data.

First, don't worry about the format of your data. Format affects only how
the data looks, not its underlying values. Vlookup, as with virtually all
Excel functions, ignores the cell's format.
Second, formatting a cell as Text *after* you have entered data, has no
effect. If you want to force a cell to be text, you must format the cell
*before* data is entered.
Regardless, formatting isn't your problem. Your data is. Look for stuff
which makes no difference to us humans, but does to a computer, like
trailing spaces, or non-printing characters (especially char(160)).

Regards,
Fred.

"mkcma via OfficeKB.com" <u48424@uwe wrote in message
news:8f45ffdb9c876@uwe...
I know there are matching part numbers, and all of the formulas pasted down
show N/A. When I don't attach the last value in the formula as 'false' it
brings the closest matching part number, so I know a formula can work.

My data layout is:
Sheet 1 - Column A: Part #
Column B: Description
Column C: 5 Yr Warr USA
Column D: 5 Yr Warr CDN (this was a formula of column C -
I
converted it
to paste special and it is now a valule.

Sheet 2 - Column C: Part #
Column D: Description
Column I: Cdn Price (This sheet downloaded from a
program
called ERA)

On Sheet One I have put a formula as follows
=VLOOKUP(A1,Cdn!C:I,6,FALSE)




E McGimpsey wrote:
It again didn't work???

Can you be a bit more specific about what "didn't work" means?

Did you get an error message? and error value? incorrect values? a crash?

What was your exact formula and data layout?

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200812/1

Hi,

Try

=VLOOKUP(A1,Cdn!C:I,6,)

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"mkcma" wrote:

I believe I have tried everything and cannot get these particular two
worksheets to bring me the matching data - there are over 6,000 lines so I
really need to make it work.

I have part numbers that I ensured have no spaces at the end, one file came
from the ERA system so may have been different, but not I think they are both
'text'.
=VLOOKUP(A:A,Cdn!C:I,6,FALSE)

any suggestions?
thanks

Check if the imported data are text and that there are no leading / trailing
spaces
which normally are present for data that were imported.

Let me know how its goes

HTH
Pls click the Yes button if this help.

cheers, francis

mkcma wrote:
No, sorry that didn't work either.

I am almost convinced (because i have done vlookups on tons of worksheets I
produced) that there is something wrong with the data (part numbers). Is
there any way to ensure that the two sheets have exactly the correct matching
format? (I presently have paint formatted all to text).

thanks,
mk

try this

[quoted text clipped - 20 lines]
any suggestions?
thanks

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200812/1

On Fri, 26 Dec 2008 20:52:42 GMT, "mkcma" <u48424@uwe wrote:

I believe I have tried everything and cannot get these particular two
worksheets to bring me the matching data - there are over 6,000 lines so I
really need to make it work.

I have part numbers that I ensured have no spaces at the end, one file came
from the ERA system so may have been different, but not I think they are both
'text'.
=VLOOKUP(A:A,Cdn!C:I,6,FALSE)

any suggestions?
thanks


Most likely, your data is not identical.

You should be able to "prove" this by formulas.

For example, if A1 contains a part number and Cdn!H7 contains the matching part
number, if they are identical then:

=A1=Cdn!H7 -- TRUE
=len(a1) = len(Cdn!H7) -- TRUE

etc.

The most common "invisible" character is an <nbsp CHAR(160) at the end of your
data.

--ron

I did a length count and one set has 8, the others have 7 - and 10 vs 9 etc.

How can I bring the numbers over without that last space?

=(left7) ??

tks,
mk

Ron Rosenfeld wrote:
I believe I have tried everything and cannot get these particular two
worksheets to bring me the matching data - there are over 6,000 lines so I
[quoted text clipped - 7 lines]
any suggestions?
thanks

Most likely, your data is not identical.

You should be able to "prove" this by formulas.

For example, if A1 contains a part number and Cdn!H7 contains the matching part
number, if they are identical then:

=A1=Cdn!H7 -- TRUE
=len(a1) = len(Cdn!H7) -- TRUE

etc.

The most common "invisible" character is an <nbsp CHAR(160) at the end of your
data.

--ron

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200812/1

Use the Trim or CLEAN function to clear unwanted characters,
eg.
=TRIM(your data) or =CLEAN(your data)

Look at Help, there's some examples


HTH

Pls click the Yes button if this help.

cheers, francis

mkcma wrote:
I did a length count and one set has 8, the others have 7 - and 10 vs 9 etc.

How can I bring the numbers over without that last space?

=(left7) ??

tks,
mk

I believe I have tried everything and cannot get these particular two
worksheets to bring me the matching data - there are over 6,000 lines so I
[quoted text clipped - 18 lines]

--ron

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200812/1

On Sun, 28 Dec 2008 02:40:52 GMT, "mkcma via OfficeKB.com" <u48424@uwe wrote:

I did a length count and one set has 8, the others have 7 - and 10 vs 9 etc.

How can I bring the numbers over without that last space?

=(left7) ??

tks,
mk

What do you mean by "bring the numbers over ..."??

After the values have been pasted into your workbook, you can process to get
rid of the last character. The "best" way to do that depends on what that
character is; and also the nature of your data; and whether that character
needs to be retained anyplace else within the string.

For example, if the character is a <nbsp, you could do Find/Replace

Find What: <alt-0160 Replace with: <blank Replace All
(note that when you type the code for the nbsp, you must enter the
digits using the NUMERIC KEYPAD).

If you want to process the numbers so that when they are "brought over" they
are already correct, you'll need to make changes in your data source reporting
routines; or write a VBA routine to pre-process the files.
--ron

0221737Â*
this is an example on one sheet that is 7 visible numbers (format is back to
'general; at this point).

I did =Clean and have tried =Trim. The =LEN(-) Still counts 8. Do I have to
re-type each and every part number?
mk
(tks for everyone's patience)

xlmate wrote:
Use the Trim or CLEAN function to clear unwanted characters,
eg.
=TRIM(your data) or =CLEAN(your data)

Look at Help, there's some examples

HTH

Pls click the Yes button if this help.

cheers, francis

I did a length count and one set has 8, the others have 7 - and 10 vs 9 etc.

[quoted text clipped - 10 lines]

--ron

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200812/1

If you've got a strange character at the beginning or end of your string,
=CODE(LEFT(A2)) or =CODE(RIGHT(A2)) should show it. If it were a normal
space the result would be 32 (hex 20). If it's a non-breaking space the
result will be 160.
Once you know what you've got you can cure it with something like
=SUBSTITUTE(A2,CHAR(160),"").
--
David Biddulph

"mkcma via OfficeKB.com" <u48424@uwe wrote in message
news:8f59d5cd3d13f@uwe...
0221737
this is an example on one sheet that is 7 visible numbers (format is back
to
'general; at this point).

I did =Clean and have tried =Trim. The =LEN(-) Still counts 8. Do I have
to
re-type each and every part number?
mk
(tks for everyone's patience)

xlmate wrote:
Use the Trim or CLEAN function to clear unwanted characters,
eg.
=TRIM(your data) or =CLEAN(your data)

Look at Help, there's some examples

HTH

Pls click the Yes button if this help.

cheers, francis

I did a length count and one set has 8, the others have 7 - and 10 vs 9
etc.

[quoted text clipped - 10 lines]

--ron

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200812/1

You may think the format is General, but if it were the leading 0 in 022137
would be dropped by Excel.

Your "number" is being treated by Excel as text.

I think you have non-breaking spaces which you cannot see or get rid of by
Trim or Clean.

See Ron's post about character 160 removal.


Gord Dibben MS Excel MVP

On Sun, 28 Dec 2008 19:33:27 GMT, "mkcma via OfficeKB.com" <u48424@uwe
wrote:

0221737*
this is an example on one sheet that is 7 visible numbers (format is back to
'general; at this point).

I did =Clean and have tried =Trim. The =LEN(-) Still counts 8. Do I have to
re-type each and every part number?
mk
(tks for everyone's patience)

xlmate wrote:
Use the Trim or CLEAN function to clear unwanted characters,
eg.
=TRIM(your data) or =CLEAN(your data)

Look at Help, there's some examples

HTH

Pls click the Yes button if this help.

cheers, francis

I did a length count and one set has 8, the others have 7 - and 10 vs 9 etc.

[quoted text clipped - 10 lines]

--ron

I finally made vLookup work!!

Thank you all for our suggestions. I think I tried some, but maybe didn't
get it right or copy it correctly.

Regardly, my simple solution was: =LEFT(A2,7)
I copied this down, and changed it for those that were 5, 6 or 8 numerals.
Then I copied/paste special 'values' into another column.
This was the column I used for vLookup, which actually brought me the
matching prices!!!

Thanks again.
mk


Gord Dibben wrote:
You may think the format is General, but if it were the leading 0 in 022137
would be dropped by Excel.

Your "number" is being treated by Excel as text.

I think you have non-breaking spaces which you cannot see or get rid of by
Trim or Clean.

See Ron's post about character 160 removal.

Gord Dibben MS Excel MVP

0221737Â*
this is an example on one sheet that is 7 visible numbers (format is back to
[quoted text clipped - 22 lines]

--ron

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200812/1

Hi mk

glad that you have finally made it.

Happy New Year

cheers, francis

mkcma wrote:
I finally made vLookup work!!

Thank you all for our suggestions. I think I tried some, but maybe didn't
get it right or copy it correctly.

Regardly, my simple solution was: =LEFT(A2,7)
I copied this down, and changed it for those that were 5, 6 or 8 numerals.
Then I copied/paste special 'values' into another column.
This was the column I used for vLookup, which actually brought me the
matching prices!!!

Thanks again.
mk

You may think the format is General, but if it were the leading 0 in 022137
would be dropped by Excel.
[quoted text clipped - 13 lines]

--ron

--
Message posted via http://www.officekb.com