通过条件提取唯一值 [英] Extract Unique Value by Criteria
问题描述
我想按用户获得独特的团队
I would like to Get the Unique Teams by User
我需要获取并显示用户过去和现在的团队.
I need to grab and display what teams a user was and is on.
Team Name | Email Address
BTA | user1@domain.com
YRE | user2@domain.com
BTR | user3@domain.com
TAS | user1@domain.com
GGS | user2@domain.com
TAS | user4@domain.com
GGS | user3@domain.com
搜索"user1@domain.com"时，它将显示
When searching for "user1@domain.com" it would display
Team Name
BTA
TAS
它不能使用shift + ctrl + enter
it cant use shift+ctrl+enter
我能够与团队合作，并使用以下代码向下拖动，但是如何向其中添加条件?
I'm able to do it with teams and drag down using the below code, but how do I add criteria to it?
IFERROR(INDEX(Table1[Team Name],MATCH(0,INDEX(COUNTIF($F$1:F1,Table1[Team Name]),),0)),"")
并拖动作品
F1在所选单元格上方.
F1 is above the selected cell.
设计结果: https://i.stack.imgur.com/oNPCv.png
推荐答案
以下是示例:
我在E2中包含了一个离线数组公式:
I included an offline array formula in E2:
=IFERROR(INDEX($A$1:$A$8,SMALL(IF($B$2:$B$8=$D$2,ROW($B$2:$B$8),""),ROW(A1))),"")
和F2中的在线版本:
=IFERROR(INDEX($A$1:$A$8,AGGREGATE(15,3,($B$2:$B$8=$D$2)*(ROW($B$2:$B$8)),COUNTIF($B$2:$B$8,"<>"&$D$2)+ROW(A1))),"")
通过在新列中添加新条件，您可以扩展($B$2:$B$8=$D$2)*(ROW($B$2:$B$8)的逻辑并将COUNTIF替换为COUNTIFS
With new criteria in new columns, you can extend the logic of ($B$2:$B$8=$D$2)*(ROW($B$2:$B$8) and replace COUNTIF with COUNTIFS
这篇关于通过条件提取唯一值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
