Copy/paste data from sheet1 to sheet2 by matching rows by value in column 1

Question

0.00/5 (No votes)

See more:

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

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

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

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.

Posted 11-Sep-17 8:26am

dusant

Updated 11-Sep-17 13:26pm

Add a Solution

Comments

Richard MacCutchan 11-Sep-17 15:13pm

Try searching no Google for "VBA copy".

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Graeme_Grant · Accepted Answer · 2017-09-11T13:26:00

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)

where A2 (sheet1) contains the name to find and Sheet2!A$2:A$6 holds the range (array) of names to search.

2. Then to pull the value from sheet 2 into sheet 1, we do a lookup of the index in each destination column's cell:

=INDEX(Sheet2!B$2:B$6,MATCH(A2,Sheet2!A$2:A$6))
=INDEX(Sheet2!C$2:C$6,MATCH(A2,Sheet2!A$2:A$6))
=INDEX(Sheet2!D$2:D$6,MATCH(A2,Sheet2!A$2:A$6))