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- |
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. |
All times are GMT +1. The time now is 02:01 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com