Autocorrect Question?

[pork1977]

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.

[Dav]

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

if you did this in say column b, then copied it down to the end of the data, then copy column b paste b as values on top of itsself and then delete column A

Regards

Dav

[pork1977]

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!

[Dav]

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

[pork1977]

Dav, 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

[Dav]

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, selecting the range and press paste. Thats all there is to it, it can not take longer than editing each cell, once the vlookup table is set up

There is also a file with a ACL filetype that may be able to be imported into excel so save having to retype the autocorrect list. However as I am at work and they disable access to the c:\drive I can not check this out If this is so it will undoubtably be quicker



Regards

Dav

[pork1977]

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 is put into a text label in the crystal report I'm using so when the report is exported into excel I can just run the formula and it gives 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 seperate sections and because of that, when the formaula is placed at the top and dragged to the bottom, the blank lines just produce '0' because of there being no data in the cells.

Is there something that can be added to the formula so that the blank 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 to achieve and the closer I can get to not doing anything at all, the better.

[Gord Dibben]

Maybe...........

=IF(ISNA(VLOOKUP(A1,$C$1:$D$1000,2,FALSE)),"",VLOOKUP(A1,$C$1:$D$1000,2,FALSE))


Gord Dibben MS Excel MVP


On Thu, 20 Jul 2006 12:57:17 -0400, pork1977
<[email protected]> wrote:

>
>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
>is put into a text label in the crystal report I'm using so when the
>report is exported into excel I can just run the formula and it gives
>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 seperate
>sections and because of that, when the formaula is placed at the top
>and dragged to the bottom, the blank lines just produce '0' because of
>there being no data in the cells.
>
>Is there something that can be added to the formula so that the blank
>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 to
>achieve and the closer I can get to not doing anything at all, the
>better.

[pork1977]

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"

[Dav]

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 the lookup
=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 you do usually get #n/a rather than 0 if the lookup fails

it the above does not work
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))

may work

Regards

Dav

[pork1977]

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

[Dav]

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

[pork1977]

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