Vlookup Help (Can this be done???)

[Johnny Anonymous]

I'm compiling a database record of sales for the company I work for. My boss wants to be able to type in a Unit Model, and have the program display all sales of the model. Is this possible with Vlookup? If not is this possible with Excel? If so, the preference would be to use a seperate

Currently I have the data inputed in the spreadsheet. 14 Columns of around 150 rows. The models are listed in column G (7th Column). Ideally I would have type the model number and have it display all lines containing that model number on sheet 2. Can anybody help me?

 

[Joe4]

Welcome to the Board!

All things being equal, I would personally prefer to use Microsoft Access, as that is a database program and is better equipped to handle database-type queries.

Using Excel, I would take a look at using Filters (see Filters and Advanced Filters in Excel's help files).

 

[Johnny Anonymous]

Yeah Joe, a friend suggested Access, I've just never used and already entered all the data on Excel

 

[Joe4]

Yeah Joe, a friend suggested Access, I've just never used and already entered all the data on Excel

Understood. Then take a look at filters. I think that may give you what you want.

 

[MikeDBMan]

As long as your data is filled out "like a table" where there are no columns with missing data in the first row, you should be able to just select from the Home Ribbon, in the Styles section, Format as Table. Then select a color scheme that you like and your data should have headers and filters on each column. From here you can easily filter away. Note, you do not have to format as table to get the filters but it adds some clarity to the data in my opinion. I would suggest playing with it before trying it on your "live" data.

 

[Johnny Anonymous]

Thanks again Joe, Filters is definitely what I was seeking. One more question though, if I'm making a custom filter parameter, how can I have it stay as an option in the pull down menu. I can achieve what I want by filtering . . . contain . . . . leter code of the model, but I can't seem to have that filter become a permanent option. Is this possible?

 

[MikeDBMan]

Extract that letter code into a separate column and then filter on that. In addition you can still do other filtering on other columns too. And the formula would be something like:

Full Model Letter
A12345 left(A1,1)

or

123A456 mid(A2,4,1)

or whatever...

 

[Johnny Anonymous]

Thanks Mike. Not sure if I explained myself properly. Essentially I have numerous sizes of makes and combination of unit sales. I would want to filter for everything that contains HB within the colum. I can do this by custom filtering. However, for the ease of the rest of the staff, I would like the custom "HB" filter to be included in the list. Unfortunately, the auto list has HB25, HB30, ..., H8040/HB25, H8040/HB25, ... etc. I'm just wondering if it is possible to have my text filter (Contains HB) as an option on the pull down menu.

 

[MikeDBMan]

You could use this formula on your new column:

This formula will return 0 if it does not contain HB and 1 if it does.
<table border="0" cellpadding="0" cellspacing="0" width="64"><col width="64"><tr height="17"> <td style="height:13.15pt;width:48pt" align="right" height="17" width="64">
</td></tr></table><table style="width: 500px; height: 36px;" border="0" cellpadding="0" cellspacing="0"><tr height="17"><td class="xl65" style="height:13.15pt;width:48pt" height="17" width="64">=IF(ISERROR((SEARCH("HB",K22))),0,(SEARCH("HB",K22)))


</td> </tr></table>