Search code examples
sqlexcelgoogle-sheetsscriptingtextjoin

Take data from a range on another sheet and return a list of column headers thus generating a type effectiveness table for Pokemon in my collection

I apologize if for any mistakes, I am not a trained programmer, just a hobbyist.

This is the spreadsheet I am working from.

It is my character sheet for a game of an old homebrew Pokémon RPG. It's a kludge, for sure.

I am working in the "Collection" tab. In the columns Strong, Weak, Immune. I would like to automatically populate these cells with a list of the appropriate types. Replicating the "Type Effectiveness" chart functionality from bulbapedia.

An example:

Type Effectiveness for Woobat, a Flying/Psychic Pokemon

Our dataset is located in a normally hidden tab at 'Misc Data'!A37:R55

So, what I need to do is take the types in columns F and G (ignoring "none") and find their corresponding rows in the dataset('Misc Data'!A37:R55) and multiply those two lines together.

Then for each cell in that row add(concatenate?) that cell's column header (currently an image, but can be edited to be text) to a list which is then displayed in the cell.

I think I need to use some combination of the functions concatenate, query, vlookup, and or clookup but I have spent a week banging my head on this and I have not been able to put together the right piece of code.

Solution

  • assuming your type table is correct (which is not) use in L3:

    =INDEX(TEXTJOIN(", ", 1, IF(FILTER('Misc Data'!$B$38:$R$54, 
 REGEXMATCH('Misc Data'!$A$38:$A$54, JOIN("|", $F3, $G3)))=0.5, 
 TRANSPOSE('Misc Data'!$A$38:$A$54), )))

    M3:

    =INDEX(TEXTJOIN(", ", 1, IF(FILTER('Misc Data'!$B$38:$R$54, 
 REGEXMATCH('Misc Data'!$A$38:$A$54, JOIN("|", $F3, $G3)))=2, 
 TRANSPOSE('Misc Data'!$A$38:$A$54), )))

    N3:

    =INDEX(TEXTJOIN(", ", 1, IF(FILTER('Misc Data'!$B$38:$R$54, 
 REGEXMATCH('Misc Data'!$A$38:$A$54, JOIN("|", $F3, $G3)))=0, 
 TRANSPOSE('Misc Data'!$A$38:$A$54), )))

    and drag down

    enter image description here

    update for M3:

    =INDEX(TEXTJOIN(", ", 1, IF(
 (FILTER('Misc Data'!$B$38:$R$54, 
 REGEXMATCH('Misc Data'!$A$38:$A$54, JOIN("|", $F3, $G3)))=2)+ 
 (FILTER('Misc Data'!$B$38:$R$54, 
 REGEXMATCH('Misc Data'!$A$38:$A$54, JOIN("|", $F3, $G3)))=4), 
 TRANSPOSE('Misc Data'!$A$38:$A$54), )))