I've got a spreadsheet full of names and peoples' roles, like the one below:
我有一个满是人名和人们角色的电子表格，如下所示:
Role Name Change
1 A Yes
2 A No
5 A N/Ap
1 B Yes
3 B No
2 C Yes
4 C No
I have to come up with a spreadsheet like the one below:
我必须想出一个像下面这样的电子表格:
1 2 3 4 5 6
A Yes
B
C
Basically, it should retrieve the information from the first spreadsheet and be layed out clearly on the second one.
基本上，它应该从第一个电子表格中检索信息，并在第二个电子表格中清楚地显示出来。
There are way too many names and roles to do it manually. VLMOVE won't work and I've tried MATCH and INDEX.
有太多的名称和角色需要手工完成。VLMOVE不起作用，我试过匹配和索引。
5 个解决方案
#1
2
Alternative to @RocketDonkey (but thanks for more complete desired result!) could be to string together Role and Name (say in a column inserted between B & C in Sheet1 [because I think OP wants a separate sheet for the results]):
@ rocket驴友的另一种选择(感谢更完整的期望结果!)可以是将角色和名称串在一起(比如在Sheet1中B和C之间插入的一列中[因为我认为OP需要一个单独的结果表]):
C2=A1&B2 copied down as required
then use a lookup in Sheet2!B2:
然后在Sheet2中使用查找!
=IFERROR(VLOOKUP(B$1&$A2,Sheet1!$C$2:$D$8,2,FALSE),"")
copied across and down as required.
按要求复制并向下复制。
This assumes the grid for the results (as in question) has been constructed (and that there are 7 rows with data - adjust $8 as necessary otherwise.)
这假设结果的网格(如问题所示)已经构建好了(并且有7行数据——根据需要调整$8)。
#2
2
Agree with @Melanie that if you can force your data into a structure that can be interpreted as numbers (1 being yes, 0 being false, for example), Pivot tables are far and away the easiest way (since they will display numbers as the values - not the text). *(see below)
同意@Melanie的观点，如果你能将你的数据强制到一个可以被解释为数字的结构中(例如，1是yes, 0是false)，那么数据透视表无疑是最简单的方法(因为它们会显示数字作为值，而不是文本)。*(见下文)
If you want to display arbitrary text, you could try this:
如果你想显示任意文本，你可以试试:
=IF(
SUMPRODUCT(--($A$2:$A$8=F$1),--($B$2:$B$8=$E2),ROW($A$2:$A$8))=0,"",
INDEX(
$A$1:$C$8,
SUMPRODUCT(--($A$2:$A$8=F$1),--($B$2:$B$8=$E2),ROW($A$2:$A$8)),
3))
This checks to see if the SUMPRODUCT of the three columns totals 0 (which will happen when no combo of x/y is matched (like Name: C, Role: 5, for instance), and if so, it returns "". Otherwise, it returns the value in column Value.
这将检查三列的SUMPRODUCT是否总计为0(如果不匹配x/y的组合(比如名称:C、Role: 5)，那么它将返回“”。否则，它返回列值中的值。
*A ‘pivot table option’ would be to represent the Change as a number (eg as formula in D2 copied down). Then create a pivot table from (in the example) A1:D8, with fields as shown. Copy the pivot table to a different sheet with Paste Special/Values (though shown in F11:K15 of same sheet in example). Then in that other sheet select row starting with Name A and as far down as required, Replace -1 with No, 1 with Yes and 0 with N/Ap.
*“数据透视表选项”将把变化表示为一个数字(如D2复制的公式)。然后从(本例中)A1:D8创建一个pivot表，其中包含如下所示的字段。将pivot表复制到另一个具有粘贴特殊/值的表中(例如，相同表的F11:K15)。然后在另一个表中，选择以名称A开头的行，并根据需要将-1替换为No, 1替换为Yes, 0替换为N/Ap。
#3
1
AMENDED
修改
You can use array formulas to reorganize your table, without having to change the its structure. Assuming the data is in the range A2:C8 on Sheet1 and the result table is to be in range A1:G4 on Sheet2, the following formula would be the first entry (role 1 and name A) in the result table.
您可以使用数组公式重新组织表，而不必更改表的结构。假设数据在Sheet1上的A2:C8范围内，结果表在Sheet2上的A1:G4范围内，下面的公式将是结果表中的第一个条目(角色1和名字A)。
=IFERROR(INDEX(Sheet1!$A$2:$C$8,MATCH(B$1&$A2,Sheet1!$A$2:$A$8&Sheet1!$B$2:$B$8,0),3),"-")
The MATCH formula returns the row number in which the role/name combination 1A occurs. The INDEX function returns the contents of the cell at the row number found by the MATCH formula and the column number 3, i.e., the Change column of your data table. The IFERROR returns "-" if the role/name combination is not in the data table.
MATCH公式返回发生角色/名称组合1A的行号。INDEX函数返回匹配公式和列号3找到的行号上单元格的内容，即。，您的数据表的Change列。如果角色/名称组合不在数据表中，则IFERROR返回“-”。
Be sure to enter the formula using the Control-Shift-Enter key combination. Then copy the formula to the remaining cells of the result table.
一定要使用控件- shift - enter组合键输入公式。然后将公式复制到结果表的其余单元格中。
The data table on Sheet1:
Sheet1上的数据表:
The result table on Sheet2:
Sheet2的结果表:
#4
1
Well since there's Excel-VBA tag, thought it would complete the solutions types by adding one in VBA :) The following code is not elegant, in any case you need to use code base, give it a try :)
既然有了Excel-VBA标记，那么可以通过在VBA:中添加一个来完成解决方案类型)下面的代码并不优雅，无论如何您都需要使用代码库，试试看:
Code:
代码:
Option Explicit
Public Sub sortAndPivot()
Dim d As Object
Dim ws As Worksheet
Dim sourceArray As Variant, pvtArray As Variant, v As Variant
Dim maxRole As Long
Dim i, j, k, m As Integer
Set d = CreateObject("Scripting.Dictionary")
Set ws = Worksheets("Sheet3") '-- set according to your sheet
'-- you could enhance by using an input box to select the range
sourceArray = Application.WorksheetFunction.Transpose(ws.Range("B3:D9").Value)
'-- max role number
maxRole = Application.WorksheetFunction.Max(ws.Range("B3:B9"))
'-- find unique name list
For i = LBound(sourceArray, 2) To UBound(sourceArray, 2)
If Not d.exists(sourceArray(2, i)) Then
d.Add sourceArray(2, i), i
End If
Next i
ReDim pvtArray(d.Count, maxRole)
pvtArray(0, 0) = "Name"
'-- add unique names from dictionary
j = 1
For Each v In d.keys
pvtArray(j, 0) = v
j = j + 1
Next
'-- add unique Role number list
For i = UBound(pvtArray, 2) To LBound(pvtArray) + 1 Step -1
pvtArray(0, i) = i
Next i
'-- sort into the correct positions
For k = LBound(pvtArray, 1) + 1 To UBound(pvtArray, 1)
For m = LBound(pvtArray, 2) + 1 To UBound(pvtArray, 2)
For i = LBound(sourceArray, 2) To UBound(sourceArray, 2)
If pvtArray(k, 0) = sourceArray(2, i) Then
If pvtArray(0, m) = sourceArray(1, i) Then
pvtArray(k, m) = sourceArray(3, i)
End If
End If
Next i
Next m
Next k
'Output the processed array into the Sheet in pivot view.
Range("F2").Resize(UBound(pvtArray) + 1, _
UBound(Application.Transpose(pvtArray))) = pvtArray
Set d = Nothing
End Sub
Results:
结果:
#5
1
There is another way to go about it without VBA. If you create another column that concatenates the first two in the first spreadsheet, like so:
没有VBA还有另一种方法。如果您创建另一个列，将第一个电子表格中的前两个列连接起来，如下所示:
Role Name Change CheckColumn
1 A Yes 1A
2 A No 2A
5 A N/Ap 5A
1 B Yes 1B
3 B No 3B
2 C Yes 2C
4 C No 4C
Then you can use the Offset and Match functions together to find the change in the 2nd sheet. So assuming your data is laid from cell A1, the formula in cell B2 would be:
然后您可以使用偏移量和匹配函数一起查找第2张表中的更改。假设你的数据来自A1细胞，那么B2细胞的公式是:
=iferror(offset(Sheet1!$A$1,match(B$1&$A2,sheet1!$D:$D,0),2),"")
= iferror(抵消(Sheet1 ! $ 1美元,匹配(B & $ 1美元A2,Sheet1 ! $ D:$ D,0),2)," ")
Alternatively, if you put the concatenated column in sheet1 before the role column, you can use vlookup in sheet2, with the formula being:
或者，如果将连接列放在角色列之前的sheet1中，可以在sheet2中使用vlookup，公式为:
=iferror(vlookup(B$1&$A2,sheet1!$A:$D,4,false),"")
1美元= iferror(vlookup(B & $ A2,sheet1 ! A:美元美元D,4,false)," ")
#1
2
Alternative to @RocketDonkey (but thanks for more complete desired result!) could be to string together Role and Name (say in a column inserted between B & C in Sheet1 [because I think OP wants a separate sheet for the results]):
@ rocket驴友的另一种选择(感谢更完整的期望结果!)可以是将角色和名称串在一起(比如在Sheet1中B和C之间插入的一列中[因为我认为OP需要一个单独的结果表]):
C2=A1&B2 copied down as required
then use a lookup in Sheet2!B2:
然后在Sheet2中使用查找!
=IFERROR(VLOOKUP(B$1&$A2,Sheet1!$C$2:$D$8,2,FALSE),"")
copied across and down as required.
按要求复制并向下复制。
This assumes the grid for the results (as in question) has been constructed (and that there are 7 rows with data - adjust $8 as necessary otherwise.)
这假设结果的网格(如问题所示)已经构建好了(并且有7行数据——根据需要调整$8)。
#2
2
Agree with @Melanie that if you can force your data into a structure that can be interpreted as numbers (1 being yes, 0 being false, for example), Pivot tables are far and away the easiest way (since they will display numbers as the values - not the text). *(see below)
同意@Melanie的观点，如果你能将你的数据强制到一个可以被解释为数字的结构中(例如，1是yes, 0是false)，那么数据透视表无疑是最简单的方法(因为它们会显示数字作为值，而不是文本)。*(见下文)
If you want to display arbitrary text, you could try this:
如果你想显示任意文本，你可以试试:
=IF(
SUMPRODUCT(--($A$2:$A$8=F$1),--($B$2:$B$8=$E2),ROW($A$2:$A$8))=0,"",
INDEX(
$A$1:$C$8,
SUMPRODUCT(--($A$2:$A$8=F$1),--($B$2:$B$8=$E2),ROW($A$2:$A$8)),
3))
This checks to see if the SUMPRODUCT of the three columns totals 0 (which will happen when no combo of x/y is matched (like Name: C, Role: 5, for instance), and if so, it returns "". Otherwise, it returns the value in column Value.
这将检查三列的SUMPRODUCT是否总计为0(如果不匹配x/y的组合(比如名称:C、Role: 5)，那么它将返回“”。否则，它返回列值中的值。
*A ‘pivot table option’ would be to represent the Change as a number (eg as formula in D2 copied down). Then create a pivot table from (in the example) A1:D8, with fields as shown. Copy the pivot table to a different sheet with Paste Special/Values (though shown in F11:K15 of same sheet in example). Then in that other sheet select row starting with Name A and as far down as required, Replace -1 with No, 1 with Yes and 0 with N/Ap.
*“数据透视表选项”将把变化表示为一个数字(如D2复制的公式)。然后从(本例中)A1:D8创建一个pivot表，其中包含如下所示的字段。将pivot表复制到另一个具有粘贴特殊/值的表中(例如，相同表的F11:K15)。然后在另一个表中，选择以名称A开头的行，并根据需要将-1替换为No, 1替换为Yes, 0替换为N/Ap。
#3
1
AMENDED
修改
You can use array formulas to reorganize your table, without having to change the its structure. Assuming the data is in the range A2:C8 on Sheet1 and the result table is to be in range A1:G4 on Sheet2, the following formula would be the first entry (role 1 and name A) in the result table.
您可以使用数组公式重新组织表，而不必更改表的结构。假设数据在Sheet1上的A2:C8范围内，结果表在Sheet2上的A1:G4范围内，下面的公式将是结果表中的第一个条目(角色1和名字A)。
=IFERROR(INDEX(Sheet1!$A$2:$C$8,MATCH(B$1&$A2,Sheet1!$A$2:$A$8&Sheet1!$B$2:$B$8,0),3),"-")
The MATCH formula returns the row number in which the role/name combination 1A occurs. The INDEX function returns the contents of the cell at the row number found by the MATCH formula and the column number 3, i.e., the Change column of your data table. The IFERROR returns "-" if the role/name combination is not in the data table.
MATCH公式返回发生角色/名称组合1A的行号。INDEX函数返回匹配公式和列号3找到的行号上单元格的内容，即。，您的数据表的Change列。如果角色/名称组合不在数据表中，则IFERROR返回“-”。
Be sure to enter the formula using the Control-Shift-Enter key combination. Then copy the formula to the remaining cells of the result table.
一定要使用控件- shift - enter组合键输入公式。然后将公式复制到结果表的其余单元格中。
The data table on Sheet1:
Sheet1上的数据表:
The result table on Sheet2:
Sheet2的结果表:
#4
1
Well since there's Excel-VBA tag, thought it would complete the solutions types by adding one in VBA :) The following code is not elegant, in any case you need to use code base, give it a try :)
既然有了Excel-VBA标记，那么可以通过在VBA:中添加一个来完成解决方案类型)下面的代码并不优雅，无论如何您都需要使用代码库，试试看:
Code:
代码:
Option Explicit
Public Sub sortAndPivot()
Dim d As Object
Dim ws As Worksheet
Dim sourceArray As Variant, pvtArray As Variant, v As Variant
Dim maxRole As Long
Dim i, j, k, m As Integer
Set d = CreateObject("Scripting.Dictionary")
Set ws = Worksheets("Sheet3") '-- set according to your sheet
'-- you could enhance by using an input box to select the range
sourceArray = Application.WorksheetFunction.Transpose(ws.Range("B3:D9").Value)
'-- max role number
maxRole = Application.WorksheetFunction.Max(ws.Range("B3:B9"))
'-- find unique name list
For i = LBound(sourceArray, 2) To UBound(sourceArray, 2)
If Not d.exists(sourceArray(2, i)) Then
d.Add sourceArray(2, i), i
End If
Next i
ReDim pvtArray(d.Count, maxRole)
pvtArray(0, 0) = "Name"
'-- add unique names from dictionary
j = 1
For Each v In d.keys
pvtArray(j, 0) = v
j = j + 1
Next
'-- add unique Role number list
For i = UBound(pvtArray, 2) To LBound(pvtArray) + 1 Step -1
pvtArray(0, i) = i
Next i
'-- sort into the correct positions
For k = LBound(pvtArray, 1) + 1 To UBound(pvtArray, 1)
For m = LBound(pvtArray, 2) + 1 To UBound(pvtArray, 2)
For i = LBound(sourceArray, 2) To UBound(sourceArray, 2)
If pvtArray(k, 0) = sourceArray(2, i) Then
If pvtArray(0, m) = sourceArray(1, i) Then
pvtArray(k, m) = sourceArray(3, i)
End If
End If
Next i
Next m
Next k
'Output the processed array into the Sheet in pivot view.
Range("F2").Resize(UBound(pvtArray) + 1, _
UBound(Application.Transpose(pvtArray))) = pvtArray
Set d = Nothing
End Sub
Results:
结果:
#5
1
There is another way to go about it without VBA. If you create another column that concatenates the first two in the first spreadsheet, like so:
没有VBA还有另一种方法。如果您创建另一个列，将第一个电子表格中的前两个列连接起来，如下所示:
Role Name Change CheckColumn
1 A Yes 1A
2 A No 2A
5 A N/Ap 5A
1 B Yes 1B
3 B No 3B
2 C Yes 2C
4 C No 4C
Then you can use the Offset and Match functions together to find the change in the 2nd sheet. So assuming your data is laid from cell A1, the formula in cell B2 would be:
然后您可以使用偏移量和匹配函数一起查找第2张表中的更改。假设你的数据来自A1细胞，那么B2细胞的公式是:
=iferror(offset(Sheet1!$A$1,match(B$1&$A2,sheet1!$D:$D,0),2),"")
= iferror(抵消(Sheet1 ! $ 1美元,匹配(B & $ 1美元A2,Sheet1 ! $ D:$ D,0),2)," ")
Alternatively, if you put the concatenated column in sheet1 before the role column, you can use vlookup in sheet2, with the formula being:
或者，如果将连接列放在角色列之前的sheet1中，可以在sheet2中使用vlookup，公式为:
=iferror(vlookup(B$1&$A2,sheet1!$A:$D,4,false),"")
1美元= iferror(vlookup(B & $ A2,sheet1 ! A:美元美元D,4,false)," ")