How to Label / Tag Items in a Spreadsheet

Question

I'm a novice at spreadsheets, so I hope you'll forgive me if I'm asking a super obvious question.

Is it possible to use tags in Excel/Google Spreadsheet? I'm creating a spreadsheet to log all the articles and books I'm reading. Say I'm reading "I, Claudius". I want to give it these tags: history, fiction, biography, disability, politics, drama. Then, if I want to display all the articles/books tagged with "politics", I can maybe search/display/pivot with that tag.

Maybe preferably, the tags should all be in one cell with each word separated by a comma. If each word were in a cell of its own, it would make the table really messy, I think.

I am open to other labelling solutions too.

Thanks!

Solution 1

As far as I know there are no built-in features that can parse and summarize comma-separated tags in Excel. You can, of course, create your own solution with worksheet functions and a little VBA. Here's a quick solution for doing this.

Step 1: Press Alt+F11 to open the VBA editor pane in Excel. Insert a new module and paste in this code for a custom function.

Public Function CCARRAY(rr As Variant, sep As String)
'rr is the range or array of values you want to concatenate.  sep is the delimiter.
Dim rra() As Variant
Dim out As String
Dim i As Integer

On Error GoTo EH
rra = rr
out = ""
i = 1

Do While i <= UBound(rra, 1)
    If rra(i, 1) <> False Then
        out = out & rra(i, 1) & sep
    End If
    i = i + 1
Loop
out = Left(out, Len(out) - Len(sep))
CCARRAY = out
Exit Function

EH:
rra = rr.Value
Resume Next

End Function

This function will allow you to create comma-separated lists to summarize the tag data you have.

Step 2: In a worksheet, enter in a cell (H2 in the example below) the tag you want to search for. In the cell to the right, enter the following formula by pressing Ctrl+Shift+Enter.

=IFERROR(CCARRAY(IF(NOT(ISERROR(FIND(H2,$B$2:$B$6))),$A$2:$A$6),", "),"No matches found.")

By pressing Ctrl+Shift+Enter, you are entering the formula as an array formula. It will appear surrounded by {...} in the formula bar. Note that in the formula $B$2:$B$6 is the range that holds all the tags for the items listed in $A$2:$A$6.

EDIT:
If you don't mind your matches being listed in a column instead of in a list in one cell, you can return matches for tags using only worksheet functions.

Where your titles are in Column A, the tags are in Column B, and the tag you are searching for is in H2, you can use the following array formula in I2 and fill down as far as you need:

=IFERROR(INDEX($A$1:$A$6,SMALL(IF(NOT(ISERROR(FIND($H$2,$B$1:$B$6))),ROW($B$1:$B$6),2000000),ROW()-1)),"")

The formula works by first forming an array of numbers based on whether the tags in each row contains the search term. If a match is found, the row number is stored in the array. If it is not found, 2000000 is stored in the array. Next, the SMALL(<array>,ROW()-1) part of the formula returns the ROW()-1th smallest value from the array. Next, this value is passed as an index argument to the INDEX() function, where the value at that index in the array of titles is returned. If a number greater than the number of rows in the title array is passed to INDEX() as an argument, an error is returned. Since 2000000 is passed as the argument when no matches are found, an error is returned. The IFERROR() function then returns "" in this case.

It is important to grasp how ROW() is being used in this formula. If you want to display your list of results starting in a different row, you will need to adjust the second argument of the SMALL() function so that it returns the first smallest value from the array. E.g., if your list of results starts in Row 1 instead of Row 2, you would use SMALL(...,ROW()) instead of SMALL(...,ROW()-1).

Also, if your list of titles and tags does not start in Row 1, you will need to adjust the formula as well. The second argument of the IF() function must be adjusted so that a match in the first row of your data returns 1. E.g., if your list of titles starts in Row 2 instead of Row 1, you will need the formula to include IF(...,ROW($A$2:$A$7)-1,...) instead of IF(...,ROW($A$1:$A$6),...).

Solution 2

Another idea:

Use the inbuilt filter functionality. Filter by the Tags column, then you can search for rows containing a given tag.

A row with the tag list: comedy,horror,romance

would show up when searching for any of those three tags.

Solution 3

While doing this programmatically works well in some cases I found a manual approach worked for me. Using columns for tags you can easily tag a row item by entering a 0 into the column. You then have one row and enter 1s in each of the tag columns for that row (you can colour this row). Then when you sort by one of the tags the 1s (blue) row acts as a separator. Between your filtered results (0s) and everything else (_).

This has a few advantages. 1. You don't have to type your tag each time. 2. You can easily cross reference to check if you have duplicate or similar items that can be reduced to one.

Solution 4

My non-VBA approach is to list out tags in one column (let's say column H), separating items with commas. From there, I use a combination of "Text to Columns" and "Remove Duplicates" to get my list of tags. I copy past this into the header row of my main sheet (in this case, beginning in column L. Then, in each cell below the individual tag headers, enter the following:

=IF((ISNUMBER(SEARCH(L$1,$H2)))=TRUE,1,0)

This gives the best of both worlds - the tag column (H) is easy for people to read; the individual tag columns (with their 0s and 1s) are easy for the computer to read. It's dynamic if you remain within the given set of tags; if not, you need to add your new tag to the header row, then copy your formulas over.

From there, Pivot-Tables are your friend.

View more solutions

Comments

[Raystafarian]

I've never seen a tag solution in excel, but that's not saying there isn't one. Maybe check out evernote

[nusantara]

Wow, this works perfectly! I prefer it on Google Spreadsheet, though. But it uses JavaScript. I'll try and do something similar there though I am a complete and utter beginner with the language. Just two more questions: What does it mean to "summarize" data? What does your code in step 1 do? I'm asking this question in particular because I think I might have to do the same with JavaScript in Google Spreadsheet. Thanks so much for your solution!

[Excellll]

Great, I'm glad it's of use. By "summarize", I just mean creating a comma-separated list of matching titles. The VBA is required for this purpose. With built-in Excel functions, it's fairly straightforward to create an array formula that can pick out matching tags. However, it's not possible to return these results in one cell without VBA, because the CONCATENATE function in Excel cannot take an array as an argument (it only reads the first item from the array if you try it). The VBA function is made to create comma-separated lists from an array of values.

[Excellll]

After writing the above response, I realized that there is a way to do something similar without VBA. Instead of returning the list of matching titles in one cell, a column of matching titles can be returned. I can edit my post to include this method as well if you're interested.

[nusantara]

Hey, sorry to reply late. Thanks again! Funnily, the new solution works wonderfully in Google Spreadsheets but doesn't work with my Excel. I'm not sure what's wrong. Still figuring it out. I'm learning a ton just googling how to use the individual parts of your formula, though. So it's wonderful!

[Excellll]

Is it returning incorrect results or is it throwing an error? If it's the latter, the issue may be the IFERROR() function. It wasn't included before Excel 2007, so if you're using an earlier version, it will throw an error.

[Excellll]

Also, if this solution works for you, could you please click the check mark to the left of my answer to mark this question as resolved? :)

[nusantara]

It's just not showing at all, no error message shown. But it does work sometimes, which is odd. Once it does, it works for that file all the time. It's when I try to do it in a new file that the problem arises. I'm using the 2010 version. Anyway, yes, this issue is resolved. :)