通过匹配第1列中的值按行将数据从sheet1复制/粘贴到sheet2 [英] Copy/paste data from sheet1 to sheet2 by matching rows by value in column 1

问题描述

您好，



我遇到了一个问题，即找到最佳算法和VBA程序将数据从Sheet1复制到Sheet2中，并将规则粘贴到正确的行列中地方，标准在第1栏中的价值。



我没有在VBA代码方面取得任何进展，因为我不知道从哪里开始：（



我会给你一些表格的例子以及复制/粘贴功能后它应该是什么：

这是Sheet1

Hello,

I am having an issue with finding the best algorithm and VBA procedure to copy data from Sheet1 into Sheet2 with the rule to paste rows into the correct place, where criteria will be value in column 1.

I have not made any progress with VBA code because I do not know where to start from :(

I will give you example of the Sheets and what it should be after copy/paste function:
this is Sheet1

Name	Time	Role
Person1	12-30	Admin
Person2	12-30	Admin
Person3	13-00	User
Person4	15-00	Worm
Person5	13-30	User

这是Sheet2：

this is Sheet2:

Name	Time	Role	Next
Person1	12-30	Admin	User
Person2	12-30	Admin	User
Person3	13-00	User	Worm
Person4	15-00	Worm	User
Person5	13-30	User	Admin

这是复制/粘贴程序后的Sheet1：

This is Sheet1 after copy/paste procedure:

Name	Time	Role	Time	Role	Next
Person1	12-30	Admin	12-30	Admin	User
Person2	12-30	Admin	12-30	Admin	User
Person3	13-00	User	13-00	User	Worm
Person4	15-00	Worm	15-00	Worm	User
Person5	13-30	User	13-30	User	Admin

基本上它会从Sheet2复制到Sheet1所有列，除了第一个一，它应该按照每行中第1列中的值匹配源行的方式粘贴行。



提前感谢您提供任何帮助。



我尝试了什么：



除了提出一些想法之外，我没有尝试过任何东西关于如何。

从选择要复制的范围开始，然后将其粘贴到第一页。粘贴数据后，它应该开始排序行以匹配发布数据中的第一列与目标表中的第一列。

Basically it will copy from Sheet2 into Sheet1 all columns except first one and it should paste rows in the way that source row match by value in column 1 for each row.

Thanks in advance for any kind of help.

What I have tried:

I have not tried anything except making some idea about how to.
Starting from selecting the range to be copied and then pasting it into sheet one. After pasting the data it should start sorting rows to match the first column from posted data with the first column in destination sheet.

推荐答案

您不需要复制，简单的查找公式将完成你想要的工作。



打破它：

1.公共密钥是名称列 - Sheet1是参考

2.在名称列中找到此人的匹配行（索引）

3.然后为每个要映射的列，获取匹配的单元格



1. 匹配<的公式/ b>是：

You don't need to copy, simple lookup formulas will do the job that you want.

Breaking it down:
1. the common key is the Name column - Sheet1 is the reference
2. find the Matching row (Index) of the person in the Name column
3. then for each column to be mapped, get the cell for the match

1. The formula for the Match is:

=MATCH(A2,Sheet2!A

2：A

6）

其中A2（sheet1）包含要查找的名称和 Sheet2！A

where A2 (sheet1) contains the name to find and Sheet2!A

这篇关于通过匹配第1列中的值按行将数据从sheet1复制/粘贴到sheet2的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！