[bedderz]

Hi i will try and explain this as clearly as I can but please bear with me

I have 3 columns of data (A, B, C) they need to stay in there current order (A1 needs to be with B1 and with C1 so on)

In column F i have a list of data which is in the same format at in B (eg Telephone Nums)

I need to pull out Columns A B and C where the data in B equals what is in F

Any help would be greatly appreciated

Thanks
Mike Bedford
MCP | MCDST

 

[OBP]

Hello Mike, when you say "Pull out" what do you actually mean?
What do you want to do with the data, Export it as in the title?
If so, export it to what and where?

 

[bedderz]

Thanks for your reply.

Ideally I'd like to extract the relevant data to another sheet.

Mike

 

[The Villan]

Have a look at the spreadsheet I have uploaded.

I have used Data, Filter, Advanced Filter

In the advanced filter dialogue box notice the following

Filter the list in place and Copy to another location.

If you select the first option you will see the rows in your spreadsheet that match the data in Column F

If you select the second option you will see the rows in your spreadsheet that match the data in Column F copied to columns I, J & K.

Notice the three ranges -

List Range (database columns A, B, c)
Criteria Range F1 to F3 (all the telephone numbers you require)
Copy To (only applicable if you want to copy to another area) I1 to K1

Make your choice and then click on OK


Note - all filed headings in the 3 ranges need to be identical

 

[OBP]

Mike, is the "other sheet" in the same workbook?
How many rows normally in column "F"?
are the matching values on the same rows, or do you have to search all rows in B for each in F?
Any chance of posting an Example?

 

[OBP]

Sorry Villan, I didn't see you post.

 

[The Villan]

However the above will not allow you to get the result on another sheet. However once you have the result, you can select the results and copy and paste to another sheet.

 

[The Villan]

Thats OK OBP we crossed Don't know if you have used the advanced filter before. In a way its a throw off from Lotus 123. I guess Microsoft had to satisfy people moving over from Lotus. That initially was a big problem.

The advanced data filter definately does the job, but won't allow you to have the end range in anotehr sheet. However a nice little macro will overcome that

 

[OBP]

That is what I was aiming for, but if there are a lot of rows in column "F" I would proably resort to a Loop.

 

[The Villan]

Agreed

 

[bedderz]

Thanks for your help

I have added a sample sheet to better explain what I want

Mike

 

[The Villan]

Refer to post #4 post above. and load the spreadsheet up that I have provided and follow the instructions and you will see how to do it.

 

[bedderz]

Thats got it!!

Thanks for your help guys

 

[Zack Barresse]

AdvancedFilter can in fact be used from another sheet, as long as you start from the other sheet.