Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cross reference two worksheets to identify matches | Excel Worksheet Functions | |||
Count cells w/values in column if the data in column a matches cri | Excel Worksheet Functions | |||
copy if data matches | Excel Worksheet Functions | |||
How to copy Excel worksheets with row and column headings into Wor | Excel Discussion (Misc queries) | |||
copy formats column width & row heights between Excel WORKSHEETS? | Excel Discussion (Misc queries) |