find a specific or several values in a massive datasheet

[Guest]

HI,
1. Although this maybe easier in ACCESS, I cannot introduce this solution to
my client.
2. I have an EXCEL 2003 customer datasheet with columns from A to AS and up
to 5000 row entries, headers are similar to: Name, contact address, postcode,
tel no and so on. I am interested to find for a specific 'search' entry the
name, the company details, address, postcode or otherwise to automate invoice
writing.

I would like to enter -say either- a comp name, or a contact name or a
postcode (or the first few characters of those) in a cell on a new page to
find and display the relevant row entries in the datasheet, e.g. D4, H4, L4,
O4. or with other words I like to find with one of those entries the right
row entries or values. If a companyname has two contact persons then I may
want to search for the specific name and company as well. (So, OR or AND?)
If the position (from 1 to 4999) in column A would be returned I can always
use Vlookup to display me the relevant other entries. This represents my
unique customer idenitifier- sorted by customer name.

I tried INdex, Match, Sumproduct and also the lookups but didi not find the
correct solution- yet. Can someone help? THX in advance.

 

[Aqib Rizvi]

Walli
Try Data, Filter, Advance Filter, that may resolve the problem.
AQIB RIZVI

 

[Guest]

hi Walli,

Adding to Aqib's suggetsion, some other options which may work include:
*using a pivot table (possibly in conjunction with a helper column in your
data table to allow for partial matches). See Debra's site for some
explanation:
http://www.contextures.com/tiptech.html

* Or using AutoFilter (possibly with macros as discussed in the below thread):
http://www.excelforum.com/showthread.php?t=622592
with an example file in the below post of the above thread:
http://www.excelforum.com/showpost.php?p=1852451&postcount=8


hth
Rob

__________________
Rob Brockett
NZ
Always learning & the best way to learn is to experience...