Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
"Finalizing" format change to text
Quick Version:
I've changed the format on a column of numbers, text and number-text combinations to text format in order to make it match another file I'm comparing with. The comparison is not finding ANY matches right away. I can go back and double click in a cell (as if to edit it) and then press the enter key and VIOLA! the match appears. It is almost as if I'm having to finalize EACH INDIVIDUAL cell to make it work, which will not be effiecient, as I've got 8,000 cells to click on. QUESTION: Is there a way to "finalize" this format change globally without having to click on each cell? Details: We receive a LARGE price sheet (DOC A) from our vendor periodically. The part number column is formatted as text. There are some alphanumeric values in the column, but the majority are in this format: 12345.60. I export a CSV document from my product database with the current pricing (DOC B) and want to compare this document with the one from our vendor, among others also in text format. When I open this CSV document, I format the part number column to text to match the others. The formula that I use on DOC A (Vendor sheet) looks at DOC B (our current pricing) for the part number. If there is a match, then it dispays the retail price in this column where my formula is. If there is not a match, it dispays "Add To Site". My formula is: =IF(A376="","",IF(ISNA(MATCH(A376,Source!A:A,0))," Add To Site",VLOOKUP(A376,Source!A:B,2,0))) 90% of the columns show "Add to Site" even though we have 90% of the items on our site. I can go to the sheet that I manually formatted to text, double click in one of the cells containing the part number, press my enter key and then the match shows up on our vendor sheet instantly. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
"Finalizing" format change to text
I'm not really following you here but one possibility is that you have your
spreadsheets set to manual, as oopposed to automatic, calculation. Go to Tools--Options--Calculations and set it to automatic. Dave -- Brevity is the soul of wit. "Nancy" wrote: Quick Version: I've changed the format on a column of numbers, text and number-text combinations to text format in order to make it match another file I'm comparing with. The comparison is not finding ANY matches right away. I can go back and double click in a cell (as if to edit it) and then press the enter key and VIOLA! the match appears. It is almost as if I'm having to finalize EACH INDIVIDUAL cell to make it work, which will not be effiecient, as I've got 8,000 cells to click on. QUESTION: Is there a way to "finalize" this format change globally without having to click on each cell? Details: We receive a LARGE price sheet (DOC A) from our vendor periodically. The part number column is formatted as text. There are some alphanumeric values in the column, but the majority are in this format: 12345.60. I export a CSV document from my product database with the current pricing (DOC B) and want to compare this document with the one from our vendor, among others also in text format. When I open this CSV document, I format the part number column to text to match the others. The formula that I use on DOC A (Vendor sheet) looks at DOC B (our current pricing) for the part number. If there is a match, then it dispays the retail price in this column where my formula is. If there is not a match, it dispays "Add To Site". My formula is: =IF(A376="","",IF(ISNA(MATCH(A376,Source!A:A,0))," Add To Site",VLOOKUP(A376,Source!A:B,2,0))) 90% of the columns show "Add to Site" even though we have 90% of the items on our site. I can go to the sheet that I manually formatted to text, double click in one of the cells containing the part number, press my enter key and then the match shows up on our vendor sheet instantly. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
"Finalizing" format change to text
Well, with a Text format, it can cause chaos like that. you can always create
a helper column to the side and have the cells =1* your original text formatted cell amount. This will force-format to number in the helper. Then, you can do your lookups from the new column... "Nancy" wrote: Quick Version: I've changed the format on a column of numbers, text and number-text combinations to text format in order to make it match another file I'm comparing with. The comparison is not finding ANY matches right away. I can go back and double click in a cell (as if to edit it) and then press the enter key and VIOLA! the match appears. It is almost as if I'm having to finalize EACH INDIVIDUAL cell to make it work, which will not be effiecient, as I've got 8,000 cells to click on. QUESTION: Is there a way to "finalize" this format change globally without having to click on each cell? Details: We receive a LARGE price sheet (DOC A) from our vendor periodically. The part number column is formatted as text. There are some alphanumeric values in the column, but the majority are in this format: 12345.60. I export a CSV document from my product database with the current pricing (DOC B) and want to compare this document with the one from our vendor, among others also in text format. When I open this CSV document, I format the part number column to text to match the others. The formula that I use on DOC A (Vendor sheet) looks at DOC B (our current pricing) for the part number. If there is a match, then it dispays the retail price in this column where my formula is. If there is not a match, it dispays "Add To Site". My formula is: =IF(A376="","",IF(ISNA(MATCH(A376,Source!A:A,0))," Add To Site",VLOOKUP(A376,Source!A:B,2,0))) 90% of the columns show "Add to Site" even though we have 90% of the items on our site. I can go to the sheet that I manually formatted to text, double click in one of the cells containing the part number, press my enter key and then the match shows up on our vendor sheet instantly. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
"Finalizing" format change to text
I looked at that and I'm already on automatic..
"Dave F" wrote: I'm not really following you here but one possibility is that you have your spreadsheets set to manual, as oopposed to automatic, calculation. Go to Tools--Options--Calculations and set it to automatic. Dave -- Brevity is the soul of wit. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
"Finalizing" format change to text
When I first did this, I just double-clicked on my CSV file to open it in
Excel. Your post gave me the idea to take it back off of my comparison and actually do the import external data and specify that the column should be text there instead of trying to convert it to text after it's already placed in my file. "Sean Timmons" wrote: Well, with a Text format, it can cause chaos like that. you can always create a helper column to the side and have the cells =1* your original text formatted cell amount. This will force-format to number in the helper. Then, you can do your lookups from the new column... "Nancy" wrote: Quick Version: I've changed the format on a column of numbers, text and number-text combinations to text format in order to make it match another file I'm comparing with. The comparison is not finding ANY matches right away. I can go back and double click in a cell (as if to edit it) and then press the enter key and VIOLA! the match appears. It is almost as if I'm having to finalize EACH INDIVIDUAL cell to make it work, which will not be effiecient, as I've got 8,000 cells to click on. QUESTION: Is there a way to "finalize" this format change globally without having to click on each cell? Details: We receive a LARGE price sheet (DOC A) from our vendor periodically. The part number column is formatted as text. There are some alphanumeric values in the column, but the majority are in this format: 12345.60. I export a CSV document from my product database with the current pricing (DOC B) and want to compare this document with the one from our vendor, among others also in text format. When I open this CSV document, I format the part number column to text to match the others. The formula that I use on DOC A (Vendor sheet) looks at DOC B (our current pricing) for the part number. If there is a match, then it dispays the retail price in this column where my formula is. If there is not a match, it dispays "Add To Site". My formula is: =IF(A376="","",IF(ISNA(MATCH(A376,Source!A:A,0))," Add To Site",VLOOKUP(A376,Source!A:B,2,0))) 90% of the columns show "Add to Site" even though we have 90% of the items on our site. I can go to the sheet that I manually formatted to text, double click in one of the cells containing the part number, press my enter key and then the match shows up on our vendor sheet instantly. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
"Finalizing" format change to text
Nancy
Format all to Genral then either of two methods. 1. Copy an unused cell. Select the data range and EditPaste Special(in place)AddOKesc. 2. Select the column then DataText to ColumnsNextNextFinish Gord Dibben MS Excel MVP On Wed, 10 Jan 2007 11:22:02 -0800, Nancy wrote: Quick Version: I've changed the format on a column of numbers, text and number-text combinations to text format in order to make it match another file I'm comparing with. The comparison is not finding ANY matches right away. I can go back and double click in a cell (as if to edit it) and then press the enter key and VIOLA! the match appears. It is almost as if I'm having to finalize EACH INDIVIDUAL cell to make it work, which will not be effiecient, as I've got 8,000 cells to click on. QUESTION: Is there a way to "finalize" this format change globally without having to click on each cell? Details: We receive a LARGE price sheet (DOC A) from our vendor periodically. The part number column is formatted as text. There are some alphanumeric values in the column, but the majority are in this format: 12345.60. I export a CSV document from my product database with the current pricing (DOC B) and want to compare this document with the one from our vendor, among others also in text format. When I open this CSV document, I format the part number column to text to match the others. The formula that I use on DOC A (Vendor sheet) looks at DOC B (our current pricing) for the part number. If there is a match, then it dispays the retail price in this column where my formula is. If there is not a match, it dispays "Add To Site". My formula is: =IF(A376="","",IF(ISNA(MATCH(A376,Source!A:A,0)), "Add To Site",VLOOKUP(A376,Source!A:B,2,0))) 90% of the columns show "Add to Site" even though we have 90% of the items on our site. I can go to the sheet that I manually formatted to text, double click in one of the cells containing the part number, press my enter key and then the match shows up on our vendor sheet instantly. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conversion to Text file format error | Excel Discussion (Misc queries) | |||
Changing cell format - for example text to numeric | Excel Discussion (Misc queries) | |||
Hoe to change text format .126 to number format 0.126 | Excel Discussion (Misc queries) | |||
How to change text format .126 to number format 0.126 ? | Excel Worksheet Functions | |||
Change FORMAT | Excel Discussion (Misc queries) |