Excel v lookup

[michelle montgomery]

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.

[Killer42]

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?

[michelle montgomery]

How are you merging them?

I am using the v lookup and choosing the code cells for the look up

[Killer42]

I am using the v lookup and choosing the code cells for the look up

Can you show us the code (or formula) used?

[michelle montgomery]

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.

[Killer42]

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.

[michelle montgomery]

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.

[michelle montgomery]

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. ?

[Killer42]

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.

[michelle montgomery]

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.

[Killer42]

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.

[michelle montgomery]

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.

[Killer42]

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.

[michelle montgomery]

[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.

[Killer42]

...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.