+ Reply to Thread
Results 1 to 5 of 5

Creating a MiniDatabase of Companies- more than one attribute

  1. #1
    Registered User
    Join Date
    01-25-2010
    Location
    ny
    MS-Off Ver
    Excel 2003
    Posts
    4

    Creating a MiniDatabase of Companies- more than one attribute

    Hey guys,

    I was wondering if any of you Excel experts can help me out.
    I am interning for a company and am compiling a database of more than a hundred companies. I would like to create an easily searchable database based on their attributes, such as their research methods, sectors covered, instruments covered, etc.
    The autofiltering function would be the best to use in this case but I've come across a problem: I don't know how to group together various elements for each attribute. For example, if a company covers both Healthcare and Technology in their sectors, I have trouble in getting the autofilter drop down menu to correctly and neatly filter out the appropriate companies.

    It's a bit hard to describe but the problem should be very clear once looking at the file I attached. Click Sector Covered on the dropdown menu, then Automotives and you'll see how Excel can't fully detect the correct company. Of course, I can do all the various combinations required to get the right company, but I would have to do that for 110 companies and it would be far too much work.
    I've looked into using Microsoft Access but I'm unfamiliar with SQL. If there are any ways to bypass this through Excel, that would be ideal. Any links or advice would be much appreciated.

    Thank you in advance.
    Attached Files Attached Files

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Creating a MiniDatabase of Companies- more than one attribute

    Hi,

    If I've understood your data correctly you need to complete the list of company names in Column A. i.e. Clear Forest should appear in A4:A5, Connotate Technologies in A7:A9, Dominios in A11, etc.

    With standard Data filter you can then use the Custom option and select two of the entries in a particular attribute column using either the 'And' or 'Or' conditions.

    Of course if you want to select on more than two entries you'll need to build a separate criteria range and use this in an Advanced Data Filter. And you'd probably then want to write a few lines of macro code to do that automatically in response to entries in the criteria range.

    HTH
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    01-25-2010
    Location
    ny
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Creating a MiniDatabase of Companies- more than one attribute

    Quote Originally Posted by Richard Buttrey View Post
    Hi,

    With standard Data filter you can then use the Custom option and select two of the entries in a particular attribute column using either the 'And' or 'Or' conditions.

    Of course if you want to select on more than two entries you'll need to build a separate criteria range and use this in an Advanced Data Filter. And you'd probably then want to write a few lines of macro code to do that automatically in response to entries in the criteria range.

    HTH
    Thank you so much for the help.

    I can see now that the OR condition should be applied to solve my problem.

    Unfortunately, I am not well-studied in Excel functions. I've tried to use the Function OR by clicking on the two cells but it seems that it only returns a #VALUE. I've also looked at the Advanced Data Filter but am unsure how to fill in the criteria ranges.

    Can you (or anyone) help out a beginner by giving me brief instructions on how to incorporate this function? Perhaps by taking a look at the attached file and letting me know exactly what to press to make the OR function apply to the appropriate ranges. Once I know how to do one example, I'm sure I'll be able to apply them to the rest of the document.

    Thank you. Please excuse my lack of knowledge in Excel.

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Creating a MiniDatabase of Companies- more than one attribute

    Hi,

    See the attached pictures. The 1st shows how to use the Custom filter with OR and the second shows you the result.

    HTH
    Attached Images Attached Images

  5. #5
    Registered User
    Join Date
    01-25-2010
    Location
    ny
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Creating a MiniDatabase of Companies- more than one attribute

    Hi,

    Richard, thank you so much for your help. While very much appreciated, I'm unsure if that is really what I want to do. I mentioned before that I need to do this for 100+ companies and some have more than 8 entries for just one attribute (sector covered), let alone as many as other entries for the other attributes. The combinations required would be very tedious and too numerous.

    I've bought an introductory guide to Excel and it mentions PivotTables. It is still difficult for me to wrap my head around it, but I'm wondering if this is the direction I should go in.

    I need to create this database for a fellow worker. Simply put, from my sample data file, if she were to choose Healthcare in the Sector Covered, I would want all the descriptions of Clear Forest (including the descriptions of saying it were Secondary and Net Expert) with Connotate Technology (that also lists the other various sectors it is involved in). As I search for the solution, it seems more and more that Microsoft Access is the more appropriate program for a clean searchable database.

    I feel like I might be able to still do this in Excel if I were to create various worksheets with each attribute connected to the appropriate company, but this is driving my head nuts.

    Sorry- I'm only a college intern starting out in the business world, but I need to start learning somehow. Once again, I appreciate all the input very much. I hope to help out forum members in the future if I ever get a better grasp of this program's mechanics.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1