Excel help

Mark M · 10 Aug 2011 at 13:12

Is there anyway I can compile a list based on an IF statment?

Ive got a list of clients and I want to pull out onto a seperate worksheet any that contain "ltd" or "limited"

If having 2 variables is a problem I can change all the ltd's to limited with a find and replace.

Thanks

Participant · 10 Aug 2011 at 13:28

I'd probably do this:

...then on the other sheet do =IF(Sheet1!B1=1,Sheet1!A1,""). Though there's probably an easier / cleverer way of doing it.

There will be gaps in the list initially but you can just sort it.

Mockingbird · 10 Aug 2011 at 14:01

Use the built-in filter system. You can have it copy all records with "limited" or "ltd" in to a separate sheet without affecting the source list, and it'll take about 2 minutes to set up the first time you do it.

Look up "advanced filter" in the excel help for a step-by-step, including the use of wildcards. If you need more than that to go on, I can probably give it a go, but TBH with those keywords (excel advanced filter) and some googling you should be golden.

Let us know how you get on.

Mark M · 10 Aug 2011 at 14:52

Wow that works perfectly thanks! Never used an advanced filter before but it was pretty easy to do once I worked out you had to put *limited* in the criteria.

The only bit I cant seem to work out is whether its possible to put *limited* OR *ltd*

Any ideas? If not I can just find and replace and Ltd's

EDIT - Ive cracked it! You can add another criteria underneath the first one and just change the criteria range to include the new addition.

Participant · 10 Aug 2011 at 14:54

Not used that advanced filter before, that's something else to bank for a later date. Very good.

Mockingbird · 19 Aug 2011 at 11:50

Yep, criteria in the same row are "and", different rows are "oR", if that makes sense.