Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automating conform of cell contents so they MATCH
I have a list of countries in column 1 with corresponding values in column
2. I have a second spreadsheet with the same countries (usually spelled the same way) in column 1 and a new set of values in column 2. For example: SS1 cols 1,2: Afghanistan $50,000 Bangladesh $25,000 Bangladesh-Dhaka $10,000 SS2 cols 1,2: Afghanistan $100,000 Bangladesh $35,000 Bangladesh-Dhaka $20,000 I would like to automatically have SS1 result in the following in cols. 1,2,3: Afghanistan $50,000 $100,000 Bangladesh $25,000 $35,000 Bangladesh-Dhaka $10,000 $20,000 I use the functions to automate this as follows: =IF(ISNA(MATCH(A1,Sheet2!$A$1:$A$100,0)),"NotFound ",VLOOKUP(A1,Sheet2!$A$1:$B$100,2,0)) And is works ok. My question: any countries where the spelling is slightly different on each spreadsheet, is there a way to automate the correction process to create an exact match. For example, to auto correct Bangladesh - Dhaka to Bangladesh-Dhaka (without the spaces on either side of the hyphen which prevents a match). Or elimination of leading or trailing spaces which also prevents a match, or correction of Bangladesh (Dhaka) to Bangladesh-Dhaka to enable a match. I'm trying to avoid human intervention. There are probably only 5-6 variations (extra spaces; parenthesis to hyphens, etc.) which would have to be checked and corrected. Is there a macro or something which could accomplish this? Or can MATCH be changed so it finds the closest match even if not exact? Etc.? hopefully, some or all of the above is possible and can be automated. appreciate your help. thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automating conform of cell contents so they MATCH
normally this will work
in sheet1 C1 type =VLOOKUP(A1,Sheet2!A1:B3,2,FALSE) and copy the formula down. see wheteher this works if it does not work the error may be in cells having the country names you must have taken these from some web database so the cells where the names of the countries are, some non printable characters are added. this can be seen by pearson's add in view cell contents (the addin is called hexchars) these have to be removed by using the macro <trimall by Mcritchie in the following url http://www.mvps.org/dmcritchie/excel/join.htm. and then use the formula otherwise you may get #N/A if you are only having few country names, in one sheet retype the names and copy them in the other sheet.and then use the romula -- remove $$$ from email addresss to send email Tenacity wrote in message ... I have a list of countries in column 1 with corresponding values in column 2. I have a second spreadsheet with the same countries (usually spelled the same way) in column 1 and a new set of values in column 2. For example: SS1 cols 1,2: Afghanistan $50,000 Bangladesh $25,000 Bangladesh-Dhaka $10,000 SS2 cols 1,2: Afghanistan $100,000 Bangladesh $35,000 Bangladesh-Dhaka $20,000 I would like to automatically have SS1 result in the following in cols. 1,2,3: Afghanistan $50,000 $100,000 Bangladesh $25,000 $35,000 Bangladesh-Dhaka $10,000 $20,000 I use the functions to automate this as follows: =IF(ISNA(MATCH(A1,Sheet2!$A$1:$A$100,0)),"NotFound ",VLOOKUP(A1,Sheet2!$A$1:$ B$100,2,0)) And is works ok. My question: any countries where the spelling is slightly different on each spreadsheet, is there a way to automate the correction process to create an exact match. For example, to auto correct Bangladesh - Dhaka to Bangladesh-Dhaka (without the spaces on either side of the hyphen which prevents a match). Or elimination of leading or trailing spaces which also prevents a match, or correction of Bangladesh (Dhaka) to Bangladesh-Dhaka to enable a match. I'm trying to avoid human intervention. There are probably only 5-6 variations (extra spaces; parenthesis to hyphens, etc.) which would have to be checked and corrected. Is there a macro or something which could accomplish this? Or can MATCH be changed so it finds the closest match even if not exact? Etc.? hopefully, some or all of the above is possible and can be automated. appreciate your help. thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automating conform of cell contents so they MATCH
In your VLOOKUP formula, leave the zero out. For a description why
search Excel help for VLOOKUP and check out the range_lookup value. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to match Cell with table of contents | Excel Discussion (Misc queries) | |||
autofit cell height and width to match bitmap contents | Excel Worksheet Functions | |||
Edit macro to match entire cell contents | Excel Discussion (Misc queries) | |||
Conform a total to a list of results? | Excel Discussion (Misc queries) | |||
Using VB to change sheet name to match cell contents | Excel Programming |