Displaying rows only if key data is in a master list

[AndrewTait]

Hi guys, hope you can help with this one, as I have been strggling with this for a few days now, and probably when I see the solution will want to hit myself very hard!

I have a list of products pulled from a SQLServer database, into excel, along with various other production related information.

(Production Date, Product Name, Number Ordered, Number Produced, Number Wasted, % Wasted)

This data contains information relating to products made for our own company, and also for an outside company.

Obviously, we do not want the outside company to see the information relating to our own products, so I need to filter this data.

I also have a master list of Product Names for this outside company, which is in a different sheet in the same workbook.

How can I drop any rows of data where the Product Name does not appear in the master list?

=======================================
You may think if it isn't broke, don't fix it. Engineers think that if it isn't broke, it doesn't have enough features yet!
======================================

 

[xlbo]

Hi Andrew - do you want to permanently delete the data or have it available but hidden ?

To "tag" the data with whose it is, I would suggest a vlookup will do nicely

If product name is in col B and your list of external product names is in sheet2 columnA rows 1:100 then

=if(isna(vlookup(B2,Sheet2!A1:A100,1,false)),"Ours","Theirs")

copying this formula down will tag your list with whether it is internal or external product.

After that, what you do with it is dependant on my 1st question



Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[AndrewTait]

Hi Geoff,

I want to delete it. This is to be e-mailed to the customer, and so I don't want him/her to be able to recover any data relating to our own products.

Cheers,

Andy

=======================================
You may think if it isn't broke, don't fix it. Engineers think that if it isn't broke, it doesn't have enough features yet!
======================================

 

[xlbo]

Ok - in that case, once you have tagged the data using the vlookup

=if(isna(vlookup(B2,Sheet2!$A$1:$A$100,1,false)),"Ours","Theirs")

(note - slight change as needs to have $ to ensure the lookup range doesn't change)

Copy the formulae and do an edit>paste special>values

Then apply an autofilter to the data and filter for "Ours"

Select all rows below the header and delete them

remove the autofilter et voila

You will need to amend B2,Sheet2!$A$1:$A$100 to reflect your data layout. B2 is the cell with the 1st product name in it and Sheet2!$A$1:$A$100 is the range where the list of products for the outside company is held


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[AndrewTait]

Thanks Geoff - Much appreciated.

=======================================
You may think if it isn't broke, don't fix it. Engineers think that if it isn't broke, it doesn't have enough features yet!
======================================

 

[yogia]

Hi AndrewTait:

I think using AdvancedFilter would be the way to go in this case ... that will facilitate filtering the data to your specification at another location (instead of filtering in place) and that would become your report that you can send out to the outside company.

I hope this helps.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com