Complex Match problem

[ServiceChris]

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

 

[Patrick Molloy]

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

 

[ServiceChris]

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

 

[ServiceChris]

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