Copy Row if Column A matches between different worksheets

[Kcope8302]

The macro that I am looking for compares column A between 2 different
worksheets(PTR and Reference data). If the Macro finds a match it takes that
row from Reference data worksheet and copies it to the PTR worksheet starting
at column X. There is information from a macro ran before this that
populates PTR. After the initial information I want the information from the
Reference data worksheet to be placed into the PTR worksheet for reporting
purposes.

Example:
Row 6 in PTR and Row 3 in Reference data match(both contain ‘CR239492’).
Once the match is found the macro takes all of row 3 from Reference data and
pastes it to Row 6 of PTR starting at column X.
Please do provide me as much assistance as possible

Thanks

-Keith-

 

[smartin]

The macro that I am looking for compares column A between 2 different
worksheets(PTR and Reference data). If the Macro finds a match it takes that
row from Reference data worksheet and copies it to the PTR worksheet starting
at column X. There is information from a macro ran before this that
populates PTR. After the initial information I want the information from the
Reference data worksheet to be placed into the PTR worksheet for reporting
purposes.

Example:
Row 6 in PTR and Row 3 in Reference data match(both contain ‘CR239492’).
Once the match is found the macro takes all of row 3 from Reference data and
pastes it to Row 6 of PTR starting at column X.
Please do provide me as much assistance as possible

Thanks

-Keith-

Do you need a macro? You are essentially describing a VLOOKUP performed
in PTR and fetching data from Reference. E.g., in PTR!X2:

=VLOOKUP(PTR!$A2,Reference!$A$2:$Z$999,1,false)

will return the value from the first column of Reference where a match
is found. #N/A will be returned if no match.

Assuming #N/A errors are not desirable, you can modify the above to
return blanks instead:


=IF(COUNTIF(Reference!$A:$A,PTR!$A2)>0,VLOOKUP(PTR!$A2,Reference!$A$2:$Z$999,1,false),"")

To return subsequent columns as you fill right, increase the
col_index_num parameter of VLOOKUP to 2, 3, 4, etc. If there are a lot
of columns a shortcut will be more expedient. This is now the formula
for PTR!X2:

=IF(COUNTIF(Reference!$A:$A,PTR!$A2)>0,VLOOKUP(PTR!$A2,Reference!$A$2:$Z$999,COLUMNS($X:X),false),"")

Fill this right and down as far as needed.