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