Showing how many times a word appears in a column only when a different word is in a different column

Strokavich · February 10, 2021, 12:56am

So I have multiple sheets labeled 2021, 2020, 2019… ect going back to 2011. I am trying to make a sheet where I can have AZM in cell A2 and Starlight Kid in B2. Then have C2 show me how many times whats in A2 appears in sheet2020 I2:I1000 only when what’s in B2 is also in sheet2020 J2:J1000.

The problem is that sometimes there are multiple names in the cells I am searching, and AZM isn’t always the first name in the cell. I cannot for the life of me figure out how to do this.

I only want them to be in the same row though. So if AZM shows up in Sheet2020 I37, I only want it to count if Starlight Kid shows up in Sheet 2020 J37.

Is this possible?

SM_Riga · February 10, 2021, 8:03pm

Do I understand correct, that some cells can contain more than one name, like Starlight Kid,AZM ASUKA;Kagetsu in one cell I37 and something similar in J37?

Strokavich · February 11, 2021, 4:02am

Yes there are multiple names in the cells I want to pull from. I37 would have the winners, so AZM, Momo Watanabe, Utami Hayashishita and J37 would have the losers Mayu Iwatani, Starlight Kid, Saya Iida.

SM_Riga · February 12, 2021, 9:34am

So one more question then. If you have multiple entries in one cell, does appearance order matter? I mean, shall the entry’s position in the list be taken into account? If so, how the data string is structured and is the structure consistent all over the cells? Even if you don’t care about order and it is just enough to know if the name is found in the cell, it would be nice to know data structure to prevent false positive hits when searching for very short text string (azm can be a substring of a longer name)

Strokavich · February 12, 2021, 10:09am

The name appearing in the Winners section and losers section does not matter order wise. There are some names that might conflict like Hanan and Hana Kimura, but every name is separated by commas, and are consistent across the entire sheet. So Hana Kimura always appears as Hana Kimura. And AZM, will always have the word AZM in their name.

Basically I have in g37 AZM, Momo Watanabe, Utami Hayashishita vs Tam Nakano, Unagi Sayaka, Mina Shirakawa then in i37 I have the winners, which would be listed AZM, Momo Watanabe, Utami Hayashishita. in J37 I have the losers which would be Tam Nakano, Unagi Sayaka, Mina Shirakawa.

In K37 and L37 I have the person who personally won, so Just Momo Watanabe. and the persona who personally lost so Mina Shirakawa.
|
None of them are order reliant except the G column where I put them in, in the order they came out to the ring. That doesn’t really matter though for what im trying to do here I belive.