 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / July 2006Autocorrect Question?
Here's a question for you all. I have an excel sheet that is produced via an export of data from Crystal Reports. One of the columns holds a list of all different codes that are all in my autocorrect list. Now if I were to just type these codes into a cell then the autocorrect function would just replace it as I type it, but as they have been exported straight into the worksheet from a database, they are all listed without being automatically corrected, see below. SUBJECTS HDWDM HDWSUQ HDWDM CASV10GCI ADANGT NCAERR ADMPI CASV10GCI HDWDM To change them I either have to double click in each one then click out the cell or select the cell, press F2 then ENTER, then F2, then ENTER and so forth as it works it's way down the list replacing each as I go. What I want to know is that *"is there a way where you can select all and get them all to be replaced with their corresponding names in the autocorrect list, without manually selecting each one everytime."* I currently have to do this everyday and it's a real pain, I've been looking for sometime to try and find a solution but don't even know if it's possible to do.  Signaturepork1977 Not a fully solution but just a thought if you have a list of the autocorrects this is just 2 columns and in your report just one entry per cell? why not trysomething like =IF(ISNA(VLOOKUP(A1,$C$1:$D$1000,2,FALSE)),A1,VLOOKUP(A1,$C$1:$D$1000,2,FALSE)) where your autocorrect list in c1:c1000 Regards Dav SignatureDav Dav, Thanks for your swift reply! I'm a bit unsure of how the formula itself works, can you explain what needs to go in each section. If HDWDM should be corrected as Hardware. Where should I be placing hardware? Assuming I start the spreadsheet from A1. Thank you in advance! Signaturepork1977 You can start the spreadsheet whereever you want, but it would make sense if the formula was on the same row for obvious reasons =IF(ISNA(VLOOKUP(A1,$C$1:$D$1000,2,FALSE)),A1,VLOO KUP(A1,$C$1:$D$1000,2,FALSE)) so the above formua would go on row1 if it was row 2 change a1 to a2 It can be copied down andwill adjust once you put the first formula in correctly somwhere you need a list of your autocorrect option,in my example they are in c1:d1000, this may need to be a bigger or smaller range, but you can adjust that c1 contains the error and d1 its correction c2 contains the next error and d2 its correction etc So in your example HDWDM should be in column c with Hardware in column d but the same row the vlookup looks up the value in a1 finds it in c1:c1000 and then returns the value in the column tothe right(d), thats what the 2 means. the false means an exact match has to me made however not everything will need autocorrecting, some values will be correct, in that case the lookup will fail and return #n/a in this instance stick with the original value in cell A1 Regards Dav SignatureDav Dan, thanks for the detailed description of how it works. I do have a small problem with it though. The list of codes I have can be over 1000 rows long and with thi formula, it relies on having all the corrected values on the same row. It just seems like more work to be doing this than it would to Press F then ENTER all the way down. There has to be an easier way to do it, can you have the auto correcte list in another worksheet or a macro? I did try creating a macro wher I just recorded F2 and ENTER a million times but when running it o another worksheet, it run ok but just pasted the orrigional values fro the existing sheet over the new codes and they didn't tally up with eac other. Perhaps a vb script might work?? not sure on that, I'm clutching a straws a bit here, but I'm surprised you can't highlight the whol column and just click a magic button to convert them all. Thanks for your hel The time is the initial setup of the lookup table, once this is done, the rows could be corrected in seconds, put the formula in the first cell copy go to the bottom row, slecting the range and press paste. Thats all their is to it, it can not take longer than editing each cell, once the vlookup table is set up Regards Dav SignatureDav OK, I've now completly setup the vlookup table using the acl files in m profile. It all works great and save a lot of time. The formula itsel is put into a text label in the crystal report I'm using so when th report is exported into excel I can just run the formula and it give me all the values I need. Just one more question before I leave you alone..... There are blank lines in this exported excel sheet which seperat sections and because of that, when the formaula is placed at the to and dragged to the bottom, the blank lines just produce '0' because o there being no data in the cells. Is there something that can be added to the formula so that the blan cells do not populate with a '0' ? They look like this once done.... V10 Login Problem v10 Citrix V10 Login Problem V10 General CAS Issue V10 Latency v10 Citrix V10 Error V10 General CAS Issue 0 0 V10 General CAS Issue CAS Software V10 General CAS Issue V10 General CAS Issue Citrix Issue 0 0 Citrix Issue V10 Login Problem V10 General CAS Issue It's all part of a huge reporting automation process we're trying t achieve and the closer I can get to not doing anything at all, th better Maybe........... =IF(ISNA(VLOOKUP(A1,$C$1:$D$1000,2,FALSE)),"",VLOOKUP(A1,$C$1:$D$1000,2,FALSE)) Gord Dibben MS Excel MVP >OK, I've now completly setup the vlookup table using the acl files in my >profile. It all works great and save a lot of time. The formula itself [quoted text clipped - 36 lines] >achieve and the closer I can get to not doing anything at all, the >better. Well, I've changed the formula a little, it currently looks like =IF(ISNA(VLOOKUP(M5,'X:\Reports\Closed Magic calls\[Autocorrect.xls]Sheet1'!$A$1:$B$50,2,FALSE)),M5,VLOOKUP(M5,'X:\Reports\Closed Magic calls\[Autocorrect.xls]Sheet1'!$A$1:$B$50,2,FALSE)) If I add that bit in the middle like you mentioned so it looks like this.... =IF(ISNA(VLOOKUP(M5,'X:\Reports\Closed Magic calls\[Autocorrect.xls]Sheet1'!$A$1:$B$50,2,FALSE)),"",M5,VLOOKUP(M5,'X:\Reports\Closed Magic calls\[Autocorrect.xls]Sheet1'!$A$1:$B$50,2,FALSE)) then I get error saying "You've entered too many arguments for this parameter" Signaturepork1977 well you have entered too many arguements an if statement is if(criteria, result if true, result if false) If the value isna you wish it to be "" otherwise the result of th lookup =IF(ISNA(VLOOKUP(M5,'X:\Reports\Closed Magi calls\[Autocorrect.xls]Sheet1'!$A$1:$B$50,2,FALSE)),"",VLOOKUP(M5,'X:\Reports\Close Magic calls\[Autocorrect.xls]Sheet1'!$A$1:$B$50,2,FALSE) Regards Da excel ammended it slightly to =IF(ISBLANK(M5),"",IF(ISNA(VLOOKUP(M5,'X:\Reports\Closed Magic calls\[Autocorrect.xls]Sheet1'!$A$1:$B$50,2,FALSE)),"",VLOOKUP(M5,'X:\Reports\Closed Magic calls\[Autocorrect.xls]Sheet1'!$A$1:$B$50,2,FALSE))) but it works a treat, thanks for all your help. Paul Signaturepork1977 I am glad u perservered after your comments earlier in the thread about it being no quicker than the old method! Its always good to learn Regards Dav SignatureDav I am glad u perservered after your comments earlier in the thread about it being no quicker than the old method! Its always good to learn. Its also nice to get feedback when thing work! Regards Dav SignatureDav LOL... I've learned a few new things along the line with Excel, infact I'm impressed with it linking to another spreadsheet, I never knew you could do that with formula's. It's still not quite as 'automated' as I'd like it to be, but to be honest I don't think I can chop it down anymore. You've given me some great tips that's made my 'work' life a bit easier and the real test will be at the end of the month when I run all these damn reports! They're all in Crystal and I can now get the formulas to export as just text fields then I can just run them once in Excel. Don't worry, I'll remember you're name and be in contact upon my next problem!!!! Kind regards, Paul Signaturepork1977 |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.