Bulk adding keywords to list of titles
May 30, 2017 4:11 AM   Subscribe

I have a list of hundreds of guidelines for my workplace which are currently only searchable through the title. I would like to add keywords to these, including synonyms of words in the titles, abbreviations, categories, etc.

The list is currently in an excel spreadsheet. I need a way to add keywords as efficiently as possible.

E.g.: if the title column contains "labour", add "birth place delivery suite labour ward"to the keywords column

I don't think that I can use a simple search and replace or filter because there are multiple keywords in each title which spawn several new keywords.

Any ideas for how to do this efficiently in Excel? Any suggestions for other tools?
posted by snoogles to Computers & Internet (2 answers total) 1 user marked this as a favorite
 
In my experience Excel doesn't handle keywords very gracefully. It is awkward having one column hold all the keywords. You can get around this by having a column for each keyword, but that is awkward for a bunch of other reasons. (It's not just a question of adding them: it's also a question of using them once they're added.) I will be watching this thread to see if indeed there's some good solution out there.
posted by Winnie the Proust at 5:52 AM on May 30, 2017


I programmed for 20 years but it's been too long to be able to give you proper Excel syntax. This is a brute force ugly solution, but I've done it before, so I know it works. A simple example follows the explanation.

First column contains Title

Columns 2nd through as N ( as many as you need) contain the following formula, with substitutions. Using your example with plain english instead of code:
=IIF(col 1 contains 'labour', put 'birth place delivery suite labour ward ' in this column, otherwise leave blank). Depending on how you will use these keywords, you may want to put commas between them. Add a trailing space (shown above after 'ward' so you don't mash 2 keywords together during concatenation (explained below).

In general, substitute for A and B
=IIF(col 1 contains A, put B in this column, otherwise leave blank)

Column N+1 has a formula to concatenate columns 2 through N. Result is all the keywords in a single column.

Column N+2. Copy column N+1 then use Paste Special to populate column N+2. This gives the result of column N+1 independent of the concatenation formula. Save the spreadsheet with a new name. Back it up just to be sure. Delete all but columns 1 and N+2.

A simple example (where Key = keyword and Con = concatenated, col = column(s))
Before deleting columns
col 1 ---- col 2 --------- col N ---------- col N+1 --------- col N+2
title 1 --- IIF on key 1 -- IIF on key 2 -- Con col 2 to N -- Paste Special of col N+1
title 2 --- IIF on key 1 -- IIF on key 2 -- Con col 2 to N -- Paste Special of col N+1

In the copy with the new name after deleting columns
col 1 -- col 2
title 1 - keywords
title 2 - keywords

If there are more keywords that now can be generated from the contents of col 2, repeat the process using col 2 as you used col 1 above.

MID() is the text function to do the 'contains' test, e.g. =IIF(col 1 contains A ...
CONCATENATE() is obvious
posted by Homer42 at 7:12 AM on May 30, 2017 [1 favorite]


« Older Friendship algebra   |   Job interview question: Why are you leaving your... Newer »
This thread is closed to new comments.