By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,708 Members | 2,086 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,708 IT Pros & Developers. It's quick & easy.

Excel v lookup

P: 8
I am trying to merge two spreadsheets together for a price update and the codes on both spreadsheets are the same but they do not register. If i type over one of the codes it then will register and the appropriate information will be brought over. I believe it has something to do with the format of the cell but i cant work it out.
Feb 28 '07 #1
Share this Question
Share on Google+
14 Replies


Expert 5K+
P: 8,434
I am trying to merge two spreadsheets together for a price update and the codes on both spreadsheets are the same but they do not register. If i type over one of the codes it then will register and the appropriate information will be brought over. I believe it has something to do with the format of the cell but i cant work it out.
How are you merging them?
Mar 2 '07 #2

P: 8
How are you merging them?
I am using the v lookup and choosing the code cells for the look up
Mar 6 '07 #3

Expert 5K+
P: 8,434
I am using the v lookup and choosing the code cells for the look up
Can you show us the code (or formula) used?
Mar 7 '07 #4

P: 8
Can you show us the code (or formula) used?
Sorry it has taken so long to get back to you but work has been an nightmare. I cant copy the formulas over but i will try to expain. I highlight thecodes from our computing sytem for the first section of the look up and then i highlight all the information from the suppliers spreadsheet by starting at row 1 and scrolling down to the end then i select the colum (suppliers spreadsheet) that the pricing is in (example 5) then type False for the last question. then i process the lookup.

This works for some suppliers but not others even if the codes look identical.
Mar 13 '07 #5

Expert 5K+
P: 8,434
Sorry it has taken so long to get back to you but work has been an nightmare. I cant copy the formulas over but i will try to expain. I highlight thecodes from our computing sytem for the first section of the look up and then i highlight all the information from the suppliers spreadsheet by starting at row 1 and scrolling down to the end then i select the colum (suppliers spreadsheet) that the pricing is in (example 5) then type False for the last question. then i process the lookup.

This works for some suppliers but not others even if the codes look identical.
I won't have time to catch up on this thread for at least a few hours. But I do know that codes which look identical can be a major pain in Excel. I seem to recall that setting format to Text can sometimes help you to determine exactly what's in a range of cells. You may find that one is a number, the other is a text string - not the same value at all, even though they may be presented the same way on the screen.
Mar 13 '07 #6

P: 8
I won't have time to catch up on this thread for at least a few hours. But I do know that codes which look identical can be a major pain in Excel. I seem to recall that setting format to Text can sometimes help you to determine exactly what's in a range of cells. You may find that one is a number, the other is a text string - not the same value at all, even though they may be presented the same way on the screen.

We have tried to change the entire column to text then number to see if we could get the code to recognise each other but no luck.
Mar 15 '07 #7

P: 8
We have tried to change the entire column to text then number to see if we could get the code to recognise each other but no luck.
Also when i change the format in the cell by say typing over the code and it then attaches the appropriate pricing the cell format has a message that says
The number in this cell is formatted as text or followed by an apostrophe. ?
Mar 20 '07 #8

Expert 5K+
P: 8,434
Also when i change the format in the cell by say typing over the code and it then attaches the appropriate pricing the cell format has a message that says
The number in this cell is formatted as text or followed by an apostrophe. ?
It sounds as though the original value is in fact a string, and not a number. How does it get there in the first place?

Also, to test that, can you go to the "bad" value, which doesn't calculate, press F2 to edit, then enter (without changing anything). If it is originally text, probably one of two things will happen. Either you'll find during the edit that it has an apostrophe at the start, or it will switch to a number when you press enter.
Mar 21 '07 #9

P: 8
It sounds as though the original value is in fact a string, and not a number. How does it get there in the first place?

Also, to test that, can you go to the "bad" value, which doesn't calculate, press F2 to edit, then enter (without changing anything). If it is originally text, probably one of two things will happen. Either you'll find during the edit that it has an apostrophe at the start, or it will switch to a number when you press enter.
Yes i have just tested on list and by pressing f2 on the suppliers code and then entering the pricing is brought through. I also just had a list of codes that had spaces at the end of the code and if i selected f2 then backspaced back to the end of the code and pressed enter the pricing registered and came through. My problem with this is that i had 11000 codes from the supplier that i need to f2 we only stocked 500 items.
Mar 21 '07 #10

Expert 5K+
P: 8,434
Yes i have just tested on list and by pressing f2 on the suppliers code and then entering the pricing is brought through. I also just had a list of codes that had spaces at the end of the code and if i selected f2 then backspaced back to the end of the code and pressed enter the pricing registered and came through. My problem with this is that i had 11000 codes from the supplier that i need to f2 we only stocked 500 items.
Sorry, you lost me a little toward the end. Is this resolved, or do you still need to fix 500 or 11,000 values?

If the latter, a little searching (see search box, top-right) should produce you information on how to convert a range of cells from text to number.
Mar 22 '07 #11

P: 8
Sorry about the confusion i am just learning about merging spreadsheets. I did manage to change the formula on the 10000 items manual by f2 but i would still like to be able to work out how to change the formulas with out having to change every single item manually. Is there a way to f2 a range of cells instead of each one individually.
Mar 22 '07 #12

Expert 5K+
P: 8,434
Sorry about the confusion i am just learning about merging spreadsheets. I did manage to change the formula on the 10000 items manual by f2 but i would still like to be able to work out how to change the formulas with out having to change every single item manually. Is there a way to f2 a range of cells instead of each one individually.
There are probably many ways to achieve this such as selecting the range and invoking a macro. But I have a strong feeling that there is a very quick and easy way to change your text values to numbers, but just can't think of it.

...

Ah! Found it at last. There's an entry in Excel's online help entitled Convert numbers stored as text to numbers. The sub-heading "A whole range at once" gives this advice...
  1. In an empty cell, enter the number 1.
  2. Select the cell, and on the Edit menu, click Copy.
  3. Select the range of numbers stored as text you want to convert.
  4. On the Edit menu, click Paste Special.
  5. Under Operation, click Multiply.
  6. Click OK.
  7. Delete the content of the cell entered in the first step.
I guess what it boils down to is that you need to perform an arithmetic operation on the range of cells, forcing it to make them numeric where possible.

P.S. You did a manual edit on 10,000 cells? That must have taken a while.
Mar 23 '07 #13

P: 8
[quote=Killer42]There are probably many ways to achieve this such as selecting the range and invoking a macro. But I have a strong feeling that there is a very quick and easy way to change your text values to numbers, but just can't think of it.

...

Ah! Found it at last. There's an entry in Excel's online help entitled Convert numbers stored as text to numbers. The sub-heading "A whole range at once" gives this advice...
  1. In an empty cell, enter the number 1.
  2. Select the cell, and on the Edit menu, click Copy.
  3. Select the range of numbers stored as text you want to convert.
  4. On the Edit menu, click Paste Special.
  5. Under Operation, click Multiply.
  6. Click OK.
  7. Delete the content of the cell entered in the first step.
I guess what it boils down to is that you need to perform an arithmetic operation on the range of cells, forcing it to make them numeric where possible.

P.S. You did a manual edit on 10,000 cells? That must have taken a while.[/QUOTE

Yes the manual changes took me 4 hours to do.

Great i have just checked your suggestion and it has worked. I did try it on the supplier list at work on friday that had the spaces after the code these didnt work. Thankyou very much.
Mar 25 '07 #14

Expert 5K+
P: 8,434
...Yes the manual changes took me 4 hours to do.
Ouch!

Great i have just checked your suggestion and it has worked. I did try it on the supplier list at work on friday that had the spaces after the code these didnt work. Thankyou very much.
Hm... you could probably get around that fairly simply by first selecting the range of cells and doing a replace of space within nothing.

Anyway, glad to see things seem to be resolved.
Mar 25 '07 #15

Post your reply

Sign in to post your reply or Sign up for a free account.