PDA

View Full Version : Is it possible to do this in Excel?



Kame
25-06-2003, 01:07 PM
I have a sheet called OUTPUT which lists a number of client details. What I want to do is take out only the required input I need and put it on another sheet called SHEET2. The data I need though is next to the words Name: and Fax:, e.g Name:<tab>Client and Fax:<tab>Fax Number.

Is there anyway to do a formula that looks for specific text like Name: and then can grab the Client which is next to it?

Or another possibility, remove everything that's not Name: and next to it as well as Fax: and next to that?

I really don't know how to go about this but maybe there's other ways you can suggest if my ways don't sound possible at all.

godfather
25-06-2003, 01:19 PM
Try VLOOKUP.

Set the input data in an array on OUTPUT, including all the relevant fields.
On the sheet2, have a duplicate list of client names, and collect the data testing each name with VLOOKUP, and each fax no. etc with VLOOKUP.
The parameters allow you to match a cell, and in the source array specify which of the other parameters in the vertical array will be chosen

parry
25-06-2003, 02:01 PM
Hi, I presume all this info is in one cell per client separated into different rows. You can use LEFT,RIGHT,MID functions to extract a particular part of the text into different cells. For example, you can look for the word Name: and return text to the right of that word. You can combine other functions such as Find to achieve quite a bit more. See http://www.cpearson.com/excel/FirstLast.htm for examples.

hth

Kame
25-06-2003, 03:34 PM
Sorry GodFather and Parry,

It seemed too complicated and I would have spent too much time figuring it out that I decided I'd remove the rows that didn't have info I need then cells and moving them in line and then words that weren't important and the end results I got all the client names in one column and the appropriate fax numbers next to them in another column now it's just simply importing them into my fax program.

Although VLOOKUP was doing it, I still had to manually keep doing the code as selecting the table_array was the only doubt as the details weren't even spaced out so it made it quite difficult to guess where the table should start and not start.

Cheers for the help anyway.