Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search and replace 9000 times?
(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! ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search and replace 9000 times?
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search and replace 9000 times?
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! ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search and replace 9000 times?
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) ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search and replace 9000 times?
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,FALS E) 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search for a value that repeats several times in another table | Excel Worksheet Functions | |||
Search and replace | Excel Discussion (Misc queries) | |||
copying formula without scrolling 9000 rows | Excel Worksheet Functions | |||
how do I hyperlink 9000 emails in an excel file, for bulk emailin | Excel Discussion (Misc queries) | |||
Search and Replace | New Users to Excel |