Small Excel Question Not Worth Its Own Thread

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread

Count Thrashula: Jun 1, 2003; Death is nothing compared to vindication.; Buglord

Not sure how "small" this question is, but I have the following situation.

I'm keeping track of a game I play, based on scenarios for it, and which game pieces are used in which scenario. Since I don't own all the expansions and whatnot, I want to be able to selectively color or filter out scenarios I can't play with what I have.

So, on one sheet, just to be simple, I have, for instance:

code:

Scenario ID | Maps Required
1           | 21, 24
2           | 1
3           | 3, 4, 21

I'm trying to figure out how to filter out the scenarios, for example, "I only have maps 21 and 24, I want to color all of the scenarios that use OTHER maps red" or something. Should I use VBA to split that column into an array and then use that array to selectively color the rows?

# ¿ Aug 19, 2016 18:08

Adbot: ADBOT LOVES YOU

# ¿ May 3, 2024 08:06

Count Thrashula: Jun 1, 2003; Death is nothing compared to vindication.; Buglord

Bob Morales posted:

=SUMPRODUCT(--ISNUMBER(SEARCH({"21","24"},B10)))>0

It will return 1 if it matches 1, 2 if it matches 2....you could color it on that

Almost perfect, but matching single number is a problem. I guess I could use leading 0s.

(i.e. SEARCH({"1"}, B10) is matching 21, 31, 41, etc.)

edit-- Is there a way I can save the array in a hidden cell and refer to it? Having {"1", "2", "3"} in cell AA2, and then using SEARCH(AA2, B10) isn't working.

Count Thrashula fucked around with this message at 18:59 on Aug 19, 2016

# ¿ Aug 19, 2016 18:48

Count Thrashula: Jun 1, 2003; Death is nothing compared to vindication.; Buglord

I have a dumb niggling issue I can't figure out.

I have a lookup table in a second sheet, say Sheet2!A:A, that's full of short strings each on a single line, so for example:
1
5
10a
LCP
20z
Etc...

I have a column in my main worksheet that I want to check the value against the lookup table. If everything in the cell (delimited somehow, maybe comma) is in the lookup table, color green, else color red. So in the example above:
1,10a would color green
5,7,AT,10a would color red

I know I can split on commas, but iterating through a list like that is stumping me.

# ¿ Mar 20, 2024 08:15

Count Thrashula: Jun 1, 2003; Death is nothing compared to vindication.; Buglord

TheLastManStanding posted:

code:
=COUNTA(TEXTSPLIT([@[Test String]],","))=SUM(SIGN(COUNTIF(LookupTable[LookupColumn],TEXTSPLIT([@[Test String]],","))))
The SIGN(...) is to deal with duplicates in the lookup table.

This worked perfectly! Thanks!

# ¿ Mar 20, 2024 13:43

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread