Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old August 5th 09, 02:21 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2008
Posts: 44
Default Compare cells and copy columns after match

I am having an issue developing a simple macro that compares column A in one
worksheet(i.e. PTR) and another worksheet(i.e. Reference Data). If what is in
column A of Reference data matches column A in PTR; I want 4 cells(L.M,N,Q)
from that line in Reference Data copied and placed in PTR(W,X,Y,Z).

What i want is to be able to run the macro from PTR and have it populate
cells W,X,Y,Z with the correct project information from the Reference Data
worksheet.

Please inform me if there is any other information would be needed to
complete.

Thanks

  #2   Report Post  
Old August 5th 09, 03:38 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2008
Posts: 3,346
Default Compare cells and copy columns after match

Hi,

One way, not requiring macros is to use VLOOKUP or MATCH. In either case
you could record a macro which enters the formulas and then converts them to
values.

Alternatively you could write a macro that would be something like:

For I = 1 to n
IF Cells(I,1) =Sheets("Reference Data").Cells(I,1) then
Sheets("Reference Data").Range("L" & I) =Range("W" & I)
...
End if
Next I

Since you are copying non-adjacent cell, I think I should ask which cells go
into which cell L -- Z...

There are many ways to simplify this, but this might be a starting point
--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Kcope8302" wrote:

I am having an issue developing a simple macro that compares column A in one
worksheet(i.e. PTR) and another worksheet(i.e. Reference Data). If what is in
column A of Reference data matches column A in PTR; I want 4 cells(L.M,N,Q)
from that line in Reference Data copied and placed in PTR(W,X,Y,Z).

What i want is to be able to run the macro from PTR and have it populate
cells W,X,Y,Z with the correct project information from the Reference Data
worksheet.

Please inform me if there is any other information would be needed to
complete.

Thanks

  #3   Report Post  
Old August 5th 09, 05:37 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2008
Posts: 44
Default Compare cells and copy columns after match

The Vlookup function wouldnt be the best option as the number of rows in cell
A varies. So the macro would take out the need to copy and paste that
function to the exact amount of rows.

I am still new to writing macros so this is still above my level of
understanding. Can you assist me with comleting the Macro. As stated it just
copies specific cells from the Reference data worksheet to the PTR worksheet
after verifiying that column A matches.

Thanks

"Shane Devenshire" wrote:

Hi,

One way, not requiring macros is to use VLOOKUP or MATCH. In either case
you could record a macro which enters the formulas and then converts them to
values.

Alternatively you could write a macro that would be something like:

For I = 1 to n
IF Cells(I,1) =Sheets("Reference Data").Cells(I,1) then
Sheets("Reference Data").Range("L" & I) =Range("W" & I)
...
End if
Next I

Since you are copying non-adjacent cell, I think I should ask which cells go
into which cell L -- Z...

There are many ways to simplify this, but this might be a starting point
--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Kcope8302" wrote:

I am having an issue developing a simple macro that compares column A in one
worksheet(i.e. PTR) and another worksheet(i.e. Reference Data). If what is in
column A of Reference data matches column A in PTR; I want 4 cells(L.M,N,Q)
from that line in Reference Data copied and placed in PTR(W,X,Y,Z).

What i want is to be able to run the macro from PTR and have it populate
cells W,X,Y,Z with the correct project information from the Reference Data
worksheet.

Please inform me if there is any other information would be needed to
complete.

Thanks



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
How to compare two columns of data to make sure they match Len Excel Worksheet Functions 0 June 24th 08 04:07 PM
Compare Col A and Col M, if Match, Copy Col N to Col E ryguy7272 Excel Worksheet Functions 1 March 21st 08 05:57 PM
Compare col and match then copy and paste saman110 via OfficeKB.com Excel Discussion (Misc queries) 2 February 21st 08 01:28 AM
Compare 4 columns and copy saman110 via OfficeKB.com Excel Discussion (Misc queries) 2 August 10th 07 05:49 PM
Want to compare two columns and then fill with data from the match Needy C Excel Worksheet Functions 1 January 11th 06 05:52 AM


All times are GMT +1. The time now is 03:57 PM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017