Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complex Match problem
Help,
I tried to solve this problem using the Match & Offset functions, bu so far no luck. I'll try to give as much details as I can. In one workbook (on sheet 1), we have a list of al our sub branches plus the postcodes they serve. We have several different types of su business, so each postcode can appear more than one time, but only onc for each business type. One of the columns on the sheet has a code fo the business type, which can be used with AutoFilter to display th right businesses. The postcodes are all stored in one column, with each entry seperate by a comma. They are in English format, and slightly truncated. So, typical English postcode is LE7 4XU, and the XU part is truncated off as one business would alway serve all of LE7 4. So, a typical entr for the postcodes could be LE1 1, LE1 2, LE1 3, LE3 4, LE7 1, LE7 2 LE7 4, LE15 3, EN1 1 (all in one cell) The order I wrote the list is quite important. Within the cell, ther is no guarantee that the entries are in any particular order. If I want to find a business entry manually, I use AutoFilter to selec the business type, then simply use Control F, type in the text and awa we go. Simple. But trying to do this via functions or code is much harder, or so i seems. I've tried the Match function, but this does not really work as expected. Even if I cut & paste only the rows for the correct busines type into a separate sheet, then use something like Match "*LE7 4*", i does not find a match. I select the ,0 option on the end of Match t make sure it looks for a unique find. Sometimes it finds the righ records, sometimes the wrong one, and sometimes not at all. What I would really like is a macro (or if it can be done, som combined functions), that would let me run down a list of postcodes i another workbook (in the correct truncated format), and for each one retrieve 3 columns of the matching row in the address sheet. In the column beside the postcode in workbook 2 is the business typ code that is needed. So that I can understand the code, lets just assume that the busines type in worksheet 1 in in column C, the postcodes are in AA, and th columns to be returned are F, G & H. Workbook 2 has the postcodes in column D, and the business type in E. I do hope this all makes sense. If not, please post and I will try t clarify further. As a PS, if anyone can explain why Match is behaving like it is, tha would also satisfy my curiousity. Thanks to all of you that have read this far down the problem - an hopefully some of you can help Chris:confused -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complex Match problem
when you do your search do you expect to get only a
single result? I'm not clear when you start a search, what it is that you're searching for. Do you start with a business type or a post code? You could add a column next to the cells with the string of post codes and use the Find() function for a postcode = have it return a zero if not found. Then your could filter for the business type =IF(ISERROR(FIND(PostCode,C18)),0,1) in the formula PostCode is a range named cell with the post code that I'm checking and C18 is the cell with the list of codes. It will return a 1 if found or a 0 if not. HTH Patrick Molloy Microsoft Excel MVP -----Original Message----- Help, I tried to solve this problem using the Match & Offset functions, but so far no luck. I'll try to give as much details as I can. In one workbook (on sheet 1), we have a list of al our sub branches, plus the postcodes they serve. We have several different types of sub business, so each postcode can appear more than one time, but only once for each business type. One of the columns on the sheet has a code for the business type, which can be used with AutoFilter to display the right businesses. The postcodes are all stored in one column, with each entry seperated by a comma. They are in English format, and slightly truncated. So, a typical English postcode is LE7 4XU, and the XU part is truncated off, as one business would alway serve all of LE7 4. So, a typical entry for the postcodes could be LE1 1, LE1 2, LE1 3, LE3 4, LE7 1, LE7 2, LE7 4, LE15 3, EN1 1 (all in one cell) The order I wrote the list is quite important. Within the cell, there is no guarantee that the entries are in any particular order. If I want to find a business entry manually, I use AutoFilter to select the business type, then simply use Control F, type in the text and away we go. Simple. But trying to do this via functions or code is much harder, or so it seems. I've tried the Match function, but this does not really work as I expected. Even if I cut & paste only the rows for the correct business type into a separate sheet, then use something like Match "*LE7 4*", it does not find a match. I select the ,0 option on the end of Match to make sure it looks for a unique find. Sometimes it finds the right records, sometimes the wrong one, and sometimes not at all. What I would really like is a macro (or if it can be done, some combined functions), that would let me run down a list of postcodes in another workbook (in the correct truncated format), and for each one, retrieve 3 columns of the matching row in the address sheet. In the column beside the postcode in workbook 2 is the business type code that is needed. So that I can understand the code, lets just assume that the business type in worksheet 1 in in column C, the postcodes are in AA, and the columns to be returned are F, G & H. Workbook 2 has the postcodes in column D, and the business type in E. I do hope this all makes sense. If not, please post and I will try to clarify further. As a PS, if anyone can explain why Match is behaving like it is, that would also satisfy my curiousity. Thanks to all of you that have read this far down the problem - and hopefully some of you can help Chris --- Message posted from http://www.ExcelForum.com/ . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complex Match problem
Sorry, I guess I didn't actually explain what I was trying to do wit
the information. I have two different uses. 1. A customer phones up, and asks for the phone number of their loca business. I would like to be able to type the business code into on cell, the postcode into the next, and have the one unique match fo that combination found, and the 3 relevant columns returned (name address & phone number). 2. I get sent lists of work, with post codes on them. This mont there were over 100 items. For each item, I need to take the postcod & look up the local business. From there I can add a column to th work list with the business name. Once I've done that, I can use macro to split the list into worksheets per business and email the on. The master address book has many columns (40 or 50 at last count), an lots of rows (several hundred). So as you can imagine, manual searche are not the best way of doing things. Hope that makes sense. Chri -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complex Match problem
Does anyone have any ideas that might help? Or can anyone explain wh
the standard find utility (Control-F) can find the cells I want, but can't seem to code this in a function or VB? Please help, as I don't have much hair already, and I'd like to kee what I do have!! Thanks in advance. Chri -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Complex match | Excel Discussion (Misc queries) | |||
Complex Match | Excel Discussion (Misc queries) | |||
Complex Match | Excel Discussion (Misc queries) | |||
Complex Index Match Help (or at least complex to me) | Excel Discussion (Misc queries) | |||
Complex LookUp / Match Problem ?? | Excel Worksheet Functions |