Formatting after import question/problem
Posted by Bill T on September 03, 2000 10:28 AM
I use a spreadsheet that has one column for invoice numbers and another for the physical area the invoice is located.
Daily I get a huge text file from the mainframe which is too large for Excel, so it starts out in Access. I then export certain data into Excel.
I have to change the text invoice to numbers by first formatting the cells to numbers and then have multiply the cells by 1 to strip a leading zero to get them to sort correctly with "yesterdays" invoice numbers.
Now for the problem. The text for the area looks the same, for example, AR for Accounts Rec. It will sort OK with the old data. But on a pivot table or in a chart using a countif formula, the new AR is not recognized as the same as the old AR, i.e., the pivot table will report 14 AR's of "yesterdays: data, and 16 AR's of "todays" data.
I can clear the contents of the new cells and type in AR and copy and paste to the new cells, but I have a lot of areas and it's a pain.
Why is the new text AR seen differently from the old AR? And is there another way to fix this besides the manual change?? Simply formatting all cells in the column as text does not do anything.
Thanks for any advice you can give me.