How to eliminate rows of empty data with little user interaction
Posted by Connie on May 30, 2001 8:05 AM
I have a table where the user can select a customer and bring up their inventory of various products they use. In the adjacent columns are vendors who offer these products and their inventory. I would like to be able to eliminate the rows containing the products that the customer doesn't use. I know auto-filter could be used to eliminate the "zero" quantities, but most of the system users are not very experienced with PC's. Is there a macro or some other automated way to do this? Here is an example of the table:
Product CustomerA VendorA VendorB
Pencils 500 10,000 2,000
Staples 0 20,000 15,000
Pens 0 1,000 25,000
Erasers 125 750 1,000
So, how best to eliminate the Staples and Pens rows so the user doesn't have to see them within the table?
Posted by Aladin Akyurek on May 30, 2001 9:12 AM
Connie
A couple of questions:
Are these 500, 0, 0, 125 some kind of customer id? If so, what does it to mean to have an id of 0? If not, what are they?
You say "the user can select a customer": By which means or mechanism do they make a selection?
Aladin
Posted by Connie on May 30, 2001 9:57 AM
Aladin,
The 500,0,0,125 represents the quantity of the products that CustomerA has (i.e. 500 pencils,0 staples,etc). Sorry my sample table got squished together! The user selects the customer by means of a drop-down cell from a list (i.e. CustomerA, CustomerB, etc)and then the table uses VLOOKUP to find that particular customer's data which exists in a separate table on a different worksheet. So in my example, since the customer doesn't use staples or pens (quantity is zero), can we have those rows not display at all? Hope this makes it clearer.
Posted by Aladin Akyurek on May 30, 2001 3:08 PM
Connie
As I understand it, you have a table for each customer on his/her worksheet.
You have also a worksheet in which you listed all customers, vendors, etc.
On another worksheet you have a dropdown list whose "source" is the range where the list of customers is. The user selects a customer from this dropdown list and you feed the selected customer to a (set of) VLOOKUP formula(s) on the same sheet that pulls off the sample data you posted from that customer's table. Right?
Care to post that/those VLOOKUP formula(s)?
If it's easier for you, you might send me a qualifying snippet of your data.
Aladin
Posted by Connie on May 31, 2001 7:57 AM
Aladin,
There are 3 main worksheets in addition to the individual customer and vendor worksheet tables as you surmised. The first main worksheet, "Start" is where the user selects the customer and vendors from simple drop-down lists created by data validation.
The 2nd worksheet, "Data" contains all customers, vendors, products, and inventories. The data on this table is pulled in from the various individual customer and vendor worksheets such as, F2=Smith!$M$6, G3=ABC_Supply!$M$11.
On this table, names of customers and vendors reside in column A. In columns B thru CN are products.
The 3rd worksheet, "Inventory Check" lists all products in column A. In B8 is the name of the customer which was chosen by user on the Start worksheet. Formula for B8=Start!C4. Continuing down in column B is that customer's inventory which is drawn from the "Data" worksheet using VLOOKUP. For example,B9=VLOOKUP(B8,Data,14,FALSE).
So it's searching for customer Smith's inventory of Pencils, which appears in column 14 on the Data Table.
So what I was looking for was a way to eliminate display of the zero inventory items on the Inventory Check sheet.
As I've been writing this, I've realized maybe I need to focus on not pulling the zero values into the Inventory Check sheet - kind of eliminating them earlier in the process. Currently I'm using conditional formatting for zero values in column B to blacken them out.
I appreciate you sticking with this question. I'm sure there's an easier way to do the whole thing than what I've come up with, but I'm fairly new to working with Excel in this capacity, so I really appreciate your help.
Connie
Posted by Aladin Akyurek on May 31, 2001 2:59 PM
Connie
How about sending me a scaled down version of your workbook? I can now better visualize your data, but not enough to suggest a solution. Hence my request.
Aladin
