Google Sheets技术咨询：如何自动关联物品与玩家至SR表B列

Hey there! Let's tackle this auto-fill problem for your Google Sheets setup. You want column B in the SR sheet to automatically list all players who selected the corresponding item (from column A) in either the Item #1 or Item #2 columns of the FormdataFilter sheet. Here are a couple of reliable solutions, depending on whether you want to keep duplicate player entries or not:

Option 1: Include Duplicate Player Entries #

If you want to show a player multiple times if they selected the same item more than once, use this formula in cell B2 of the SR sheet, then drag it down to apply to all rows:

=TEXTJOIN(", ", TRUE, FILTER(FormdataFilter!B:B, FormdataFilter!C:C=A2), FILTER(FormdataFilter!B:B, FormdataFilter!D:D=A2))

How it works: #

• FILTER(FormdataFilter!B:B, FormdataFilter!C:C=A2) grabs all players who picked the item in column A2 as their Item #1
• The second FILTER does the same for Item #2
• TEXTJOIN(", ", TRUE, ...) combines these lists into a single comma-separated string, ignoring any empty values

Option 2: Remove Duplicate Player Entries #

If you only want each player to appear once, even if they selected the same item multiple times, use this modified formula (also starting in B2):

=TEXTJOIN(", ", TRUE, UNIQUE({FILTER(FormdataFilter!B:B, FormdataFilter!C:C=A2); FILTER(FormdataFilter!B:B, FormdataFilter!D:D=A2)}))

How it works: #

• The curly braces {...; ...} combine the two filtered player lists into a single array
• UNIQUE() removes any duplicate names from the combined array
• TEXTJOIN then formats the unique list into a comma-separated string

Bonus: More Efficient Query-Based Approach (Great for Large Datasets) #

If you have a lot of entries, the QUERY function can be more efficient. Here's a version that also removes duplicates:

=TEXTJOIN(", ", TRUE, UNIQUE(QUERY(FormdataFilter!B:D, "SELECT B WHERE C = '"&A2&"' OR D = '"&A2&"'", 0)))

Note: #

If your item names contain single quotes, this formula will break. To fix that, use SUBSTITUTE to escape the quotes:

=TEXTJOIN(", ", TRUE, UNIQUE(QUERY(FormdataFilter!B:D, "SELECT B WHERE C = '"&SUBSTITUTE(A2, "'", "\'")&"' OR D = '"&SUBSTITUTE(A2, "'", "\'")&"'", 0)))

Quick Troubleshooting Tip: #

If matches aren't showing up correctly, check for extra spaces in your item names. Wrap TRIM() around the cell references to clean up whitespace:

=TEXTJOIN(", ", TRUE, UNIQUE({FILTER(FormdataFilter!B:B, TRIM(FormdataFilter!C:C)=TRIM(A2)); FILTER(FormdataFilter!B:B, TRIM(FormdataFilter!D:D)=TRIM(A2))}))

内容的提问来源于stack exchange，提问作者Kenneth Poulsen