Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 44
Default Copy Row if Column A matches between different worksheets

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-

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 915
Default Copy Row if Column A matches between different worksheets

Kcope8302 wrote:
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.
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Cross reference two worksheets to identify matches Christie Excel Worksheet Functions 4 January 14th 09 09:38 PM
Count cells w/values in column if the data in column a matches cri mdcgpw Excel Worksheet Functions 4 January 13th 09 12:55 AM
copy if data matches [email protected] Excel Worksheet Functions 3 April 17th 08 11:47 PM
How to copy Excel worksheets with row and column headings into Wor lab Excel Discussion (Misc queries) 1 March 29th 07 08:53 PM
copy formats column width & row heights between Excel WORKSHEETS? tccc1219 Excel Discussion (Misc queries) 5 November 8th 05 05:22 PM


All times are GMT +1. The time now is 04:34 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"