google sheets/excel/me is like my OT3, feel free to ignore me
If you're in Google Sheets, then you can skip step 5 if you type the formula into B1 like this:
=ArrayFormula(if(A:A="","",COUNTif(A:A,A:A)))
It'll do the count for you in every B cell, so long as there's actually something in the corresponding A cell.
And I don't know if this helps, but you can also use import html to import the fandom and pairings into a single cell (which you can then use unique on?) Unfortunately, it takes a bit of work, because you need to grab the top-level lists and the number of them changes as fandoms are added during noms.
Will import all of the fandoms and ships in anime, books, and cartoons. I'm not really sure it's helpful given what you're doing, but it's a cool feature? to find the right list number, start at 10 (anime/manga), and then add the number of anime fandoms plus 1 for books, then add the number of book fandoms to that +1 to get the list # for cartoons.
And you can separate the fandoms into one column and the ships into another if you use split and arrayformula (ctrl+shift+enter automatically inserts your formula into an arrayformula)
=arrayformula(split(A:A,"↓ ↑",false))
(when your imported HTML is in the A column)
I believe you can import the fandom and pairings using importxml, too, but I...am super not good at websites. And Xpath is really confusing. The closest I could get is
![[personal profile]](https://www.dreamwidth.org/img/silk/identity/user.png)
timeasmymeasure
no subject
Date: 2017-08-10 03:52 am (UTC)google sheets/excel/me is like my OT3, feel free to ignore me
If you're in Google Sheets, then you can skip step 5 if you type the formula into B1 like this:
=ArrayFormula(if(A:A="","",COUNTif(A:A,A:A)))
It'll do the count for you in every B cell, so long as there's actually something in the corresponding A cell.
And I don't know if this helps, but you can also use import html to import the fandom and pairings into a single cell (which you can then use unique on?) Unfortunately, it takes a bit of work, because you need to grab the top-level lists and the number of them changes as fandoms are added during noms.
For example, for the femslashex tagset
={IMPORTHTML("http://archiveofourown.org/tag_sets/1926","list",10);IMPORTHTML("http://archiveofourown.org/tag_sets/1926","list",82);IMPORTHTML("http://archiveofourown.org/tag_sets/1926","list",160)}
Will import all of the fandoms and ships in anime, books, and cartoons. I'm not really sure it's helpful given what you're doing, but it's a cool feature? to find the right list number, start at 10 (anime/manga), and then add the number of anime fandoms plus 1 for books, then add the number of book fandoms to that +1 to get the list # for cartoons.
And you can separate the fandoms into one column and the ships into another if you use split and arrayformula (ctrl+shift+enter automatically inserts your formula into an arrayformula)
=arrayformula(split(A:A,"↓ ↑",false))
(when your imported HTML is in the A column)
I believe you can import the fandom and pairings using importxml, too, but I...am super not good at websites. And Xpath is really confusing. The closest I could get is
=IMPORTXML("http://archiveofourown.org/tag_sets/1926", "//li/ol")
which certainly gets you relevant data, but the order makes no sense to me. But maybe you're better than I am at that kind of thing?
(sorry, this got really big /o\)