I have a list of data that is not in alphabetical or numerical order. I want to sort a second list of the same date to match the first list. I cannot change the order of the data. My goal is to paste additional data from the second set back into the first data set.
我有一个不按字母或数字顺序排列的数据列表。我想要排序第二个相同日期的列表以匹配第一个列表。我不能改变数据的顺序。我的目标是将第二个数据集的额外数据粘贴到第一个数据集。
**DATA SET A** **DATA SET B**
22350 ____ BH160 100
22355 ____ 22350 125
BH160 ____ BH190 200
BH190 ____ 22355 150
I would like to get the numerical value from column 2 of DATA SET B to show up in a new column of DATA SET A. For example, I want 125 to show up in line 1, column 2 of DATA SET 1.
我想从数据集B的第2列得到数值，显示在数据集a的新列中。例如，我想要125显示在数据集1的第1行第2列中。
My list contains about 200 unique numbers and I need to update it every week.
我的列表包含大约200个唯一数字，我需要每周更新一次。
Can someone help me? Maybe with a macro that I could use each week?
有人能帮助我吗?也许用一个我每周都能用的宏?
3 个解决方案
#1
9
You could also use INDEX MATCH, which is more "powerful" than vlookup. This would give you exactly what you are looking for:
您还可以使用索引MATCH，它比vlookup更“强大”。这会让你得到你想要的:
#2
4
You can use VLOOKUP.
您可以使用VLOOKUP。
Assuming those are in columns A and B in Sheet1 and Sheet2 each, 22350 is in cell A2 of Sheet1, you can use:
假设它们分别在Sheet1和Sheet2的A和B列中，22350在Sheet1的cell A2中，您可以使用:
=VLOOKUP(A2, Sheet2!A:B, 2, 0)
This will return you #N/A if there are no matches. Drag/Fill/Copy&Paste the formula to the bottom of your table and that should do it.
如果没有匹配，这将返回#N/A。拖/填充/复制-粘贴公式到表格的底部，这样就可以了。
#3
0
You could also simply link both cells, and have an =Cell formula in each column like, =Sheet2!A2 in Sheet 1 A2 and =Sheet2!B2 in Sheet 1 B2, and drag it down, and then sort those two columns the way you want.
您还可以简单地链接这两个单元格，并在每个列中有一个=Cell公式，如=Sheet2!表1中的A2和=Sheet2!B2在表格1 B2中，拖下来，然后按照你想要的方式对这两列进行排序。
- If they don't sort the way you want, put the order you want to sort them in another column and sort all three columns by that.
- 如果它们不是你想要的排序方式，把你想要对它们排序的顺序放到另一列中，然后再对这三列进行排序。
- If you drag it down further and get zeros you can edit the =Cell formula to show "" IF there is nothing. =(if(cell="","",cell)
- 如果你把它拖得更远，得到0，你可以编辑=Cell公式来显示“如果没有任何东西”。=(如果(细胞=“”、“”,细胞)
- Cutting, pasting, deleting, and inserting rows is something to be weary of. #REF! errors could occur.
- 剪切、粘贴、删除和插入行是令人厌倦的事情。#裁判!可能发生的错误。
This would be better if your unique items change also, then all you would do is sort and be done.
如果您的惟一项也发生了更改，那么这将会更好，那么您所要做的就是排序和完成。
#1
9
You could also use INDEX MATCH, which is more "powerful" than vlookup. This would give you exactly what you are looking for:
您还可以使用索引MATCH，它比vlookup更“强大”。这会让你得到你想要的:
#2
4
You can use VLOOKUP.
您可以使用VLOOKUP。
Assuming those are in columns A and B in Sheet1 and Sheet2 each, 22350 is in cell A2 of Sheet1, you can use:
假设它们分别在Sheet1和Sheet2的A和B列中，22350在Sheet1的cell A2中，您可以使用:
=VLOOKUP(A2, Sheet2!A:B, 2, 0)
This will return you #N/A if there are no matches. Drag/Fill/Copy&Paste the formula to the bottom of your table and that should do it.
如果没有匹配，这将返回#N/A。拖/填充/复制-粘贴公式到表格的底部，这样就可以了。
#3
0
You could also simply link both cells, and have an =Cell formula in each column like, =Sheet2!A2 in Sheet 1 A2 and =Sheet2!B2 in Sheet 1 B2, and drag it down, and then sort those two columns the way you want.
您还可以简单地链接这两个单元格，并在每个列中有一个=Cell公式，如=Sheet2!表1中的A2和=Sheet2!B2在表格1 B2中，拖下来，然后按照你想要的方式对这两列进行排序。
- If they don't sort the way you want, put the order you want to sort them in another column and sort all three columns by that.
- 如果它们不是你想要的排序方式，把你想要对它们排序的顺序放到另一列中，然后再对这三列进行排序。
- If you drag it down further and get zeros you can edit the =Cell formula to show "" IF there is nothing. =(if(cell="","",cell)
- 如果你把它拖得更远，得到0，你可以编辑=Cell公式来显示“如果没有任何东西”。=(如果(细胞=“”、“”,细胞)
- Cutting, pasting, deleting, and inserting rows is something to be weary of. #REF! errors could occur.
- 剪切、粘贴、删除和插入行是令人厌倦的事情。#裁判!可能发生的错误。
This would be better if your unique items change also, then all you would do is sort and be done.
如果您的惟一项也发生了更改，那么这将会更好，那么您所要做的就是排序和完成。