Excel VBA - If, then, delete

  • Thread starter Thread starter StylinEric
  • Start date Start date
S

StylinEric

Hey everyone, thanks agian for all your great help!

This one might be hard to explain:

I have a sheet with the following headings in order
CompanyName
FullName
Title
Phone
Address
City
State

I am trying to limit my sheet to only those contacts with particular
titles. However I don't want more than one contact for each company
with the same address. In other words I have an order of priority for
each company.

For example if there is no President listed, search for vice president,
if no vice president, search for LIKE "*Secretary".
Eventually if it can't find anyone in my criteria, just give me
everyone. I will have to go back and manually look at these

The tricky part here is only getting one contact for each company with
the same address. I have the sheet sorted by company right now. maybe
there is a way to keep checking for contacts until the companyname
and/or address changes.

O.k. well this one has been stumping me for quite some time. Any help
you might be able to gather would be awesome! Let me know if you have
any questions.

Thanks,

Stylin
 
Eric,

I think the fastest way to grab this without a lot
of programming is to add a column (H) with a concatenated
set of partial fields. For example the first 5 letters
of the company name + the first two letters of the state
+ the first 5 of the city + 5 of address and finally
4 of title. Do a sort of all your data using this
column as the key - copy and paste special-> Values
back into H; then in col I, use a formula like:
=IF(ISERR(FIND(LEFT(H15,13),H16,1)),"","Y") to
identify when two rows are almost the same.

Try and let me know how close it gets you.

jeff
 
Back
Top