Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Autocorrect 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. -- pork1977 ------------------------------------------------------------------------ pork1977's Profile: http://www.excelforum.com/member.php...o&userid=36353 View this thread: http://www.excelforum.com/showthread...hreadid=561375 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Autocorrect Question?
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,VLOO KUP(A1,$C$1:$D$1000,2,FALSE)) where your autocorrect list in c1:c1000 Regards Dav -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=561375 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Autocorrect Question?
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! -- pork1977 ------------------------------------------------------------------------ pork1977's Profile: http://www.excelforum.com/member.php...o&userid=36353 View this thread: http://www.excelforum.com/showthread...hreadid=561375 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Autocorrect Question?
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 -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=561375 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Autocorrect Question?
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 this 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 F2 then ENTER all the way down. There has to be an easier way to do it, can you have the auto corrected list in another worksheet or a macro? I did try creating a macro where I just recorded F2 and ENTER a million times but when running it on another worksheet, it run ok but just pasted the orrigional values from the existing sheet over the new codes and they didn't tally up with each other. Perhaps a vb script might work?? not sure on that, I'm clutching at straws a bit here, but I'm surprised you can't highlight the whole column and just click a magic button to convert them all. Thanks for your help -- pork1977 ------------------------------------------------------------------------ pork1977's Profile: http://www.excelforum.com/member.php...o&userid=36353 View this thread: http://www.excelforum.com/showthread...hreadid=561375 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Autocorrect Question?
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 -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=561375 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Possibly a loaded question, but I think | Excel Discussion (Misc queries) | |||
How do I find and replace a question mark in Excel? | Excel Discussion (Misc queries) | |||
Newbie With A Question | Excel Worksheet Functions | |||
Anybody Help with previous question | Excel Discussion (Misc queries) | |||
Hints And Tips For New Posters In The Excel Newsgroups | Excel Worksheet Functions |