excel - 根据多个条件连接值
问题描述
我有以下数据集
Key ID Status 1 Status 2 Order ID
1 A1 FALSE TRUE 1234-USF-0025
1 A1 FALSE TRUE 1234-USF-0026
1 A1 FALSE TRUE 1234-USF-0027
2 A1 TRUE TRUE 1234-USF-0025
2 A1 TRUE TRUE 1234-USF-0026
2 A1 TRUE TRUE 1234-USF-0027
3 A1 FALSE TRUE 1234-USF-0025
3 A1 FALSE TRUE 1234-USF-0026
3 A1 FALSE TRUE 1234-USF-0027
4 A2 TRUE TRUE 1234-USF-0028
4 A2 TRUE TRUE 1234-USF-0029
4 A2 TRUE TRUE 1234-USF-0030
5 A3 TRUE TRUE 1234-USF-0031
5 A3 TRUE TRUE 1234-USF-0032
5 A3 TRUE TRUE 1234-USF-0033
6 A4 TRUE TRUE 1234-USF-0034
6 A4 TRUE TRUE 1234-USF-0035
6 A4 TRUE TRUE 1234-USF-0036
我需要以下
Order ID ID TRUE FALSE
1234-USF-0025 A1 2 1,3
1234-USF-0026 A1 2 1,3
1234-USF-0027 A1 2 1,3
1234-USF-0028 A2 4
1234-USF-0029 A2 4
1234-USF-0030 A2 4
1234-USF-0031 A3 5
1234-USF-0032 A3 5
1234-USF-0033 A3 5
1234-USF-0034 A4 6
1234-USF-0035 A4 6
1234-USF-0036 A4 6
在第二个表(我需要的那个)中,每个表Order ID
都列在相应的ID
. 虽然 在原始数据集中列出了 9 次,但总共A1
只有 3 个唯一的. 但是,也与 3 个不同的 Keys 相关联。Order IDs
A1
A1
目标是连接Keys
for each Order ID
andID
组合,其中Status 1
andStatus 2
都TRUE
在,并在列中列出它们TRUE
。对于至少一个是的那些Order ID
和组合,应该在列下列出。ID
Status
FALSE
Keys
FALSE
我试过的
- 我尝试仅从
TRUE
列开始,使用 INDEX-MATCH 作为数组公式,虽然我知道下面的公式不适用于我想要的最终目标,但我试图从小处着手并以公式为基础。不幸的是,我对数组的了解有限,我不确定如何继续,因为我不明白为什么它会返回它所做的事情或如何从这一点上达到我的目标。
=INDEX($C$2:$C$19,MATCH(1,($H2 = $B$2:$B$19) * ($G2 = $E$2:$E$19)))
- 接下来,我尝试将原始数据集中的各个部分分开,但被困在如何继续。我认为这是更简单的解决方案,但我不知道如何根据所需的标准进行连接。
真的:=IF(AND($C2=TRUE,$D2=TRUE),$A2,"")
错误的:=IF(OR($C2<>TRUE,$D2<>TRUE),$A2,"")
笔记:
- An
ID
至少与一个相关联Key
,但可以有更多 Order ID
可以重复相同,ID
但只能重复不同Keys
。ID
我也对 . 或基于 的解决方案持开放VBA
态度Python
,R
但不确定如何为此任务启动脚本,所以我一直专注于Excel
.
解决方案
这是一个冗长的解决方案,并假设您的数据与您发布的完全一样(以及在 sheet1 上),但它有效(我认为)。您还需要为输出数据创建第二张工作表。如果您不确定在哪里发布此代码/如何运行它,请告诉我。
Sub DoStuff()
'Initialize the output sheet
Sheet2.Cells.Clear
Sheet2.Cells(1, 1) = "Order ID"
Sheet2.Cells(1, 2) = "ID"
Sheet2.Cells(1, 3) = "TRUE"
Sheet2.Cells(1, 4) = "FALSE"
newRow = 2
'Loop through the first sheet and remove duplicates
lastRow = Sheet1.Range("E:E").Cells.SpecialCells(xlCellTypeConstants).Count
For i = 2 To lastRow
exists = False
For j = 2 To newRow
If Sheet1.Cells(i, 5).Value = Sheet2.Cells(j, 1).Value Then
exists = True
Exit For
End If
Next
If exists = False Then
Sheet2.Cells(newRow, 1) = Sheet1.Cells(i, 5).Value
Sheet2.Cells(newRow, 2) = Sheet1.Cells(i, 2).Value
'Populate the true and false columns
For k = 2 To lastRow
If Sheet1.Cells(k, 5).Value = Sheet1.Cells(i, 5).Value Then
If Sheet1.Cells(k, 3).Value = True And Sheet1.Cells(k, 4).Value = True Then
Sheet2.Cells(newRow, 3) = Sheet2.Cells(newRow, 3).Value & Sheet1.Cells(k, 1).Value & ", "
Else
Sheet2.Cells(newRow, 4) = Sheet2.Cells(newRow, 4).Value & Sheet1.Cells(k, 1).Value & ", "
End If
End If
Next
'Remove extra characters, if there are any
If Sheet2.Cells(newRow, 3).Value <> "" Then
Sheet2.Cells(newRow, 3).Value = Left(Sheet2.Cells(newRow, 3).Value, Len(Sheet2.Cells(newRow, 3).Value) - 2)
End If
If Sheet2.Cells(newRow, 4).Value <> "" Then
Sheet2.Cells(newRow, 4).Value = Left(Sheet2.Cells(newRow, 4).Value, Len(Sheet2.Cells(newRow, 4).Value) - 2)
End If
newRow = newRow + 1
End If
Next
End Sub
使用您发布的数据的结果:
推荐阅读
- python-2.7 - 解码其他语言
- php - tm 额外产品选项选择更改 woocommerce 数量
- c++ - 如何将事件循环中的事件分派给订阅者?
- javascript - 将加载的 3D 对象分配给变量 - 返回未定义
- spring-data-mongodb - Spring data - MongoDB:聚合管道更新不起作用
- python - Python索引值作为元素值
- html - 使用复选框将 Mailchimp-Form 的订阅者划分为不同的受众
- ruby-on-rails - 分配父母 child_id 的表单
- kubernetes-helm - 如何跨图表重用 Helm 模板?
- ios - 如果通过 cocoapod 使用最新版本的 Firebase 安装 googleApis,iOS 应用程序会崩溃