CODEWITHSUNDEEP
google-sheetsduplicatesfilteringgoogle-sheets-formula
F I R E
F I R E

Reputation: 27

Filter all values except a specific set, remove duplicates, remove another specific value then join the result in one cell

Trying to join together neatly unique specific values which are randomly generated while excluding duplicates and ignoring a specific value I already have.

Sample

The result of the selected cell in the picture above should be "Melee Or Ranged Or SPEL". This should be drawn from the "Affixes" section above, and ignores the value below "Base Scaling Type," while excluding all other values

This should be achieved ideally without the addition of extra rows, columns or values.

From various posts I've tried formulas like:

=iferror(join(" Or ",(Filter(Unique(E8:J8),E8:J8<>L10,E8:J8<>"",E8:J8="Melee",E8:J8="SPEL",E8:J8="Ranged")),L10),L10)

However, their issues were specifically removing duplicates or specifically filtering a value, and I couldn't find one where they combined both into one formula.

Upvotes: 1

Views: 101

Answers (1)

player0
player0

Reputation: 1

=ARRAYFORMULA(JOIN(" OR ", TRANSPOSE(UNIQUE(FILTER(
 QUERY(TRANSPOSE(TO_TEXT(E7:J8)), 
 "select Col2 where Col1 contains 'Scaling'", 0), NOT(COUNTIF(L10, 
 QUERY(TRANSPOSE(TO_TEXT(E7:J8)), 
 "select Col2 where Col1 contains 'Scaling'", 0))))))))

0

=JOIN(" OR ", UNIQUE(TRANSPOSE(FILTER(E8:J8, NOT(COUNTIF(L10, E8:J8))))))

0

Upvotes: 0

Related Questions