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

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

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

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

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

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

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
------------------------------------------------------------------------
pork1977's Profile: http://www.excelforum.com/member.php...o&userid=36353
View this thread: http://www.excelforum.com/showthread...hreadid=561375

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

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


Gord Dibben MS Excel MVP


On Thu, 20 Jul 2006 12:57:17 -0400, pork1977
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.

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:\Rep orts\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"


--
pork1977
------------------------------------------------------------------------
pork1977's Profile: http://www.excelforum.com/member.php...o&userid=36353
View this thread: http://www.excelforum.com/showthread...hreadid=561375

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:\Rep orts\Closed
Magic calls\[Autocorrect.xls]Sheet1'!$A$1:$B$50,2,FALSE)

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

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:\Rep orts\Closed
Magic calls\[Autocorrect.xls]Sheet1'!$A$1:$B$50,2,FALSE)))

but it works a treat, thanks for all your help.

Paul


--
pork1977
------------------------------------------------------------------------
pork1977's Profile: http://www.excelforum.com/member.php...o&userid=36353
View this thread: http://www.excelforum.com/showthread...hreadid=561375

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


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=561375

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


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=561375

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


--
pork1977
------------------------------------------------------------------------
pork1977's Profile: http://www.excelforum.com/member.php...o&userid=36353
View this thread: http://www.excelforum.com/showthread...hreadid=561375