Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9,101
Default Replacing Values in a Spreadsheet

You probably want 3 worksheets: master,table, replace.

Master worksheet in row 1 starting at column A
Alabama Shoes Formal Black Patent

Your table of 274 descriptors would be on worksheet table in the range A1:B274


formula in worksheet replace cell A1
=vlookup(master!a1,table!A1:B274,2,false)
where
master!a1 is descriptor on worksheet master that yu want replaced
table!A1:B274 the cells containing the descriptors
2 return the value in the 2nd coolumn of the table which is column B
false needed because your table is not sorted.


"In need of enlightenment" wrote:

I'm fairly sure that this is just a LOOKUP function, but I cannot understand
the Excel help file on VLOOKUP.

I have about 14,000 records in Column A. Columns B through F each contain
verbal descriptors about the information in A. If you combine B through F in
every possible way, there are 274 possible combinations. Obviously it's
mathematically possible to have many more than 274, but there are 274
discreet combinations that occur for this particular set of descriptors.

Meanwhile, each descriptor has been assigned a code that a data extraction
program will use. I want to use an Excel function that will see the TEXT
term, find the CODE that goes with that TEXT and replace the TEXT with the
CODE - on a column-by-column basis.

Example of TEXT:

Alabama Shoes Formal Black Patent
Texas Suits Men's Blue Pinstripe

Associated CODE:

Alabama 0A1
Shoes 1A2
Formal 9B3
Black 3A6
Patent 4L7
Texas 0B9
Suits 1Z4
Men's L4Z
Blue 9C1
Pinstripe 44Z

Each verbal descriptor has been assigned a unique CODE; all 274 descriptors.

Can someone explain the syntax to be used in telling Excel to look for the
descriptor Alabama and if it "sees" Alabama, to substitue the corresponding
3-digit CODE? If this is a VLOOKUP, does Excel over-write Alabama or is the
new table being built on another worksheet?

If you don't have the time or desire to teach basic LOOKUP, I understand.
If that turns out to be the case, could you refer me to an Excel help website
that would break out the procedure into its most basic steps?

Many thanks....

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
replacing codes with actual values Arjen Excel Discussion (Misc queries) 1 July 11th 06 05:10 PM
Replacing Linked Cell Values w/ Current Values TomCat Excel Worksheet Functions 6 April 10th 06 12:20 PM
Replacing missing values? Robert Lundqvist Excel Worksheet Functions 2 March 28th 06 09:05 AM
Replacing zero values with dashes Jonibenj Excel Discussion (Misc queries) 6 September 9th 05 08:53 AM
Filtering and replacing values through a macro vic Excel Worksheet Functions 1 January 27th 05 09:53 PM


All times are GMT +1. The time now is 10:57 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"