Search and replace 9000 times?

[jago25_98]

(this is also posted in general as I'm guessing that VBA isn't the only
option)

There are 2 columns of data, A, and B. A contains numbers and B
contains what these numbers stand for. These columns contain about 9000
rows of data.

How do I replace the data in column A with that in column B?

One method is to use find and replace, going down column A and
replacing them by hand for all 9000 rows:-

For example:

1 = Avon
2 = Devon
3 = UK
4 = USA

^ press ctrl+H, type in 1 in the find box, and Avon in the replace box.
Run and repeat replacing all instances in the database 9000 times.

Is VBA the only option, and if so how may I do so?

TIA if you can!

 

[RWN]

If it's not too onerous, create a name table consisting of the # in one
column and the location (Avon etc) in the second.
This can be on the same sheet or a different one.
Select *both* columns of the newly created) table.
Insert->Name->Define
Give the table a name ("Locations", for example)
OK
Then, in a column on the sheet where the keys are (the numbers of the
locations)
(Assume Col A has the numbers and the list starts in row 1.)
=Vlookup($a1,Locations,2,False)
<Enter>
Grab the fill handle of the cell and drag to the bottom of the list and
release.

You may want to now sort your new column and check for any "N/A" values
whichs means the lookup could not find a value for the key (location #). If
this is the case then update your "Location" table, if you are adding a
value insert it into the table before the original last line.

When all is well - Select the newly created name column, copy and "Paste
Special-> Values" in the 1st row of the list of numbers (this will replace
the numbers with the value of the lookup (vs the formula).
Delete the lookup column.

--
Regards;
Rob

Please reply to the NG, I'm already up to my eyeballs in Nigerian/South
African get rich letters
as well as "Microsoft Critical Updates" et al.

 

[jago25_98]

at first it didn't seem to work but now it does )

is there a way I can refer to a seporate worksheet though?

=VLOOKUP(lookupworksheet($A1),locations,2,FALSE)

or something?

Thank you so much for your help!

 

[jago25_98]

ah, correction. It seemed to be working but now I relise that it isn't
doing what I expected - all it's doing is copying the values instead of
replacing them.

Here's a small(ish) example file with what Im trying to do :

'example file,180kb' (http://www.termisoc.org/~j/files/example.xls)

and the original database I'm working on:

'original database, about 2 megabytes'
(http://www.termisoc.org/~j/files/SWdata.xls)

 

[RWN]

As to the ability to do a lookup on a different sheet, if you name the table
then the table can be on any sheet in the same workbook you don't have to
tell it where it's located (that's the "beauty" of naming a table).

If you want to maintain your "database" (table) in another book then the
syntax would be;

VLOOKUP($AwhateverRow,TableBook.xls!TestTbl,2,FALSE)

Notice the Book ("TableBook.xls!") is followed by an exclamation mark which,
in turn, is followed by the table name in that book.

Because you've told XL where to look it means that you could have an
identically name table in the recipient book as well.
(Handy for testing - when you're happy with it, just point the lookup to the
central book.)

Having your table in a separate book is a good way to do it as it separates
your table and allows for a separate maintenance procedure as far as
updating the data is concerned (ie you'd have one source for any application
and, if it has to be updated, then it only has to be changed in one place).

Now that I've bored you to death(!) I'll try and answer the second question.

I guess I didn't understand your original query. If you're wanting to
replace the value in your "Key" cell (the # that's used in your lookup
argument ("$AwhateverRow") ) the answer is that you can't, unless you use
VBA which may be beyond the scope here.
Now, having said that, I should offer the disclaimer that I'm not an expert
compared to some of the individuals here, but I doubt if it can be done
using the functions on a spreadsheet.
That is why I went into detail about the copy/paste-special after the lookup
is done. ie once the lookup retrieves the values, you can copy the results
and paste the values over the Key values, replacing them.

Hope this helps

--
Regards;
Rob

Please reply to the NG, I'm already up to my eyeballs in Nigerian/South
African get rich letters
as well as "Microsoft Critical Updates" et al.