How to organize and filter data with multiple categories and subcategories

Welcome to the forum
Access is generally a better tool than Excel for this type of thing - but Excel is perfectly adequate for smaller databases
It sounds like you are wanting to use Excel? Is that correct?

To avoid anyone having to re-rcreate your data etc, please attach a workbook to get things moving
- illustrate your needs with clear examples of what you are trying to do
- your examples should comprise the more complex query types
- make text anonymous but still typical of your data

Anyone helping you will need to understand
- how the data looks
- how it interrelates
- how complex queries may be
- how sorting should work
- how the output should look
etc

Is it your intention that the database holds all the information or acts more as an index (with hyperlinks etc) to other files\intranet pages?

Where will the database reside?
- local server?
- SharePoint or similar?
(there may be different VBA functionality issues to consider)

Estimate of ultimate dimensions of database?
- how many categories\sub-categories\rows of data etc
- after initial set-up, how frequently will database be updated?

To attach a workbook:
- click on GoAdvanced
- look below and click on ManageAttachments
- follow screen instructions

I am asking these questions to kick this off, not because I can necessarily provide you with a solution - that depends on how complicated it all looks

For some reason the antivirus program on my Laptop is screaming "KeepOut" of the forum - so I am staying away until that has been resolved. It is unlikely to be a serious issue but better safe than sorry. I use McAfee - so if it is screaming at me, it will be screaming at lots of others too! I am currently accessing via a specific tablet which is not connected to anything important! (Just in case )

I have looked at your reply and workbook - and your request looks totally do-able

I will test my ideas tomorrow:
- the values by line for Subject, Region, Level and Project to be held in 4 columns (one each)
- with each cell value being comma delimited (exactly what you requested)
(ie Project column value = A,B,C,D if all projects relevant instead of current "Yes" in 4 columns)
- a cell can hold over 30,000 characters which is more than sufficient for subject and region categories

Data Filter cannot handle your requirements, but I expect Advanced Filter to cope with everything you want
- filtering the values is straightforward
- BUT sorting them will require a specific VBA procedure
- filtering selection can be fronted in a way similar to slicers for your users

It would be better if my test database started looking as similar as possible to what you ultimately want, especially the 4 filter columns
- (rather than me create something random) could you reply with a small list of valid "subjects" (20 to 30)
- the other columns I am ok with

I aim to keep VBA to a minimum, and make Excel's basic functionality do as much as possible
- Excel is faster than VBA
- there are a few VBA issues with Sharepoint
- there are also some Excel issues with Sharepoint to bear in mind

Let's get the filtering nailed first before moving onto sorting the output

If you want to test the comma delimited idea, you can do that (in a limited way) with Data Filter
- create a column with some text values that are comma delimited
- click on the fitter arrow
- select Text Filter
- select Text Contains
- type a (different) value equal to one of the delimited values in each box
- the second condition = "contains" (it's the last item in dropdown list)
AND = both conditions satisfied for item
OR = either condition satisfied for item
- Data Filter can handle 2 conditions in one column
- Advanced Filter is much more impressive in what it can handle

This is deliberately unsophisticated
- We need to be sure that advanced filter can give you the results you need
- I have made no attempt at this stage to make it easy for the user (that will not be an issue later)
- sorting has also been ignored until later

Advanced filter alows the user to be sophisticated with filters but Excel requires the filter criteria tabulated a specific way., and is fussy with search strings etc.
Everything in the attached is raw to reveal the nuts and bolts.

How it works:
In sheet "Criteria" :
- a usable search string is formula generated in row 4 (based on rows 2 & 3 values)
- the operator can be selected separately for each column
- "Add to Criteria" copies the data below
- several crieria rows can be accumulated into the single data filter
- each criteria row is independent of other rows
(ie the results = everything on row 8 PLUS everything on row 9 etc)
- "Filter Data" takes you to the filtered data

To test:
- have a look at the data first and then (in sheet "Criteria") select appropriate values from dropdowns
- click "Add to Criteria" when the whole row is what you want
- everything in the row must be satisfied for something to appear in the results
- to delete a criteria row(below row 7), select any cell in the row
- click on "Filter Data" to see your results (in sheet Data)
- to show all data again on sheet "data" take menu item Data\Clear
- cell B3 allows user to enter freeform text or select from dropdown

Let me know how you get on

(In case you are puzzled...) the items that appear to be in A8 and A9 beow are notes I added on to the image

AdvancedFilter.jpg

The delay was not an issue - I can still remember where I was heading with your database! I am pleased that you can see this method working well for you.

We need to take account of how the database is going to be maintained as well as searched
- consistency of approach in "tagging" is the key to better quality search results (cybersecurity may not be found if user is searching for cyber-security etc)
- we can do something to alleviate this (search for both even if user only enters 1)
- data maintenance input tools need to mirror data searching tools

It would be best to try allow for your future plans during testing
- 2 more dimensions will be added (I will invent something)
- these can then be switched off until you are ready to use them
- are you considering adding a "year" field to allow user to exclude outdated resorces?

It is crucial that you test everything on Sharepoint as we proceed
- Sharepoint is a great tool but a few Excel features do not work and any such issues arising are best identified sooner rather than later .

It could be useful to line up a user to do some quick ongoing "usability" testing with you
- it is easy to miss something when too closely involved in the "build"
- the user quickly notices if something is not intuitive etc

What is the earliest version of Excel to allow for?

I aim to have an updated version posted within the next 48 hours -more user firiendly and with basic sorting capabilities added
We'll need to bounce the test file back and forth for a few days, trying out a few different ideas etc. There are a several options available - I would be astounded if we get it right first time!

It should be easy to design a simple input form to make life easy for the user
- and use data validation to control the user's use of only only authorised tags
(Only "trusted" individuals should be authorised to create new tags - they will be key players!)
- VBA can sort the tags alphabetically and put the commas in there
I would not trust a user with inserting commas or putting words in sequence
- half of them probably can't remember the alphabet
- and the others are guaranteed to land you with you with a bagatelle of spaces, dashes, colons, full stops, commas etc
Searching variants can be handled - should only really arise if searching with free text
- eg searching for "cyber" rather than tags "cybersecurity" or "cyberfraud"
- tags should be unique - if someone sets up 2 tags that are too similar then can use VBA to remove one of them and convert the data to the chosen tag (problem removed for ever)

I have been rather busy today, and so have had little time to spend on your thread
Attached workbook includes a potential user-friendly dimension selector for you to consider

- click on cell once and it turns green (= I want this)
- click on cell again and it turns red (= exclude this)
- click on cell again to remove color
- to clear all selected items in a column click on blue header cell

VBA is behind the sheet and is simple
- it comprises one short Selection Change Event macro
- here are no complicated buttons or checkboxes to maintain

It is not hooked into the database at this stage, but a click of a button would drive creation of filter criteria based on user selections

Any thoughts?

Updating the Database (see attached workbook)
- demonstrates comma separators being introduced automatically
- user forced to use approved tags only
- some columns restrict user to only one tag

I wanted you to have a look at how database tags could be updated (similar to yesterday's search)
- it needs a bit of tidying up (see below) - but gives a good indication of how things might work
- not fully tested so you may spot something I haven't

To amend current tag values in Sheet "Data":
- click any cell (except columns A,B & I)
- takes you automatically to Sheet "Lists"
- the current tags against selected document are highlighted in green
- (as before) clicking on a cell toggles the cell colour
- (as before) clicking on the blue header cell allows user to clear all tags in that columns (after message pop-up)
- clicking back on "Data" without clicking on "Update Tag Values" aborts any changes made
- clicking on "Update Tag Values" overwrites values in "Data"

To add another "paper" to sheet data
- type the name into cell in next row in column A
- ignore column B for the moment
- select cell in column C and everything is as above

Tidying up: (tomorrow!)
- the VBA would be simplified if the column matching in both sheets was built into the workbook
(lookups are currently done via VBA - I have changed my mind!!)
- cell Q1 in sheet "Lists" holds the row number of the item currently being edited
- probably better to add a hidden worksheet to hold some values that VBA can simply refer to
- the user may find it useful to see both the "end result" and what was there originally before clicking "Update Tag Values" (see question below)
- procedures ViewCurrentTags and Update tags share overlapping requirements which are currently duplicated in the code
- I spotted one minor thing that the code is doing wrongly when identifying tag matches (let's see if you spot it too!!)
- column I is not integrated into anything just yet (see question below)

Questions
- HOW ARE YOUR DOCUMENTS NUMBERED? Do you want to autonumber?
- rather than change cell colour (which loses original status) would it be better to use strikethrough font for proposed deletions and red font(?) for proposed additions?
- Column I is another one of my random thoughts(??) - would it be useful to have a field where users could include a reference to other documents that are linked in some way?

- are we still heading the the correct direction?