Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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!

share|improve this question
    
I've never seen a tag solution in excel, but that's not saying there isn't one. Maybe check out evernote –  Raystafarian Mar 27 '12 at 12:59

1 Answer 1

up vote 6 down vote accepted

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.

enter image description here

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)),"")

enter image description here

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),...).

share|improve this answer
    
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! –  nusantara Mar 29 '12 at 16:07
    
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 Mar 29 '12 at 16:38
    
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. –  Excellll Mar 29 '12 at 16:40
    
It's possible without VBA? That would be better, I think. If it's not too inconvenient, yes, it would be awesome if you could include the method you mentioned. It would be so helpful to anyone searching for this topic! Thanks for explaining "summarization". I tried concatenation too but, yeah, couldn't use it the way I wanted it to. Now I know it can't behave like an argument. –  nusantara Mar 31 '12 at 10:04
    
Yes, it's possible. I'll try to post the solution here in a few minutes. Sorry for the delay! –  Excellll Apr 5 '12 at 13:57

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.