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/
.