Copying rows from another sheet based on specific criteria

[jamesbrown008]

Firstly I just want to thank all of you who have been replying to my other posts. I am building out a pretty cool project here and I will share the completed results once I am done (this should be the final step).

Ok so here is what I am trying to do:

I have two sheets - one with a list of unique names and dates (the destination list) and one with a names, dates and additional fields (the scource list).

What I need is some code that loops through each row in the source list and matches the values in column B and C of the source sheet to a corresponding row in column B or C in the destination list.

If it finds a match it should then copy columns C-O from the source sheet to the destination sheet.

There shouldn't be any instances where is finds no match but perhaps it could trigger a msg box if it does fail to find one.

If the above doesn't make sense - please let me know and I will post some examples.

Thanks

James

 

[venkat1926]

sheet "destination" has got only two fields that is column A and B
but you said
" corresponding row in column B or C in the destination list."

where is the column C in sheet "destination"

post a very small extract of the two sheets and the result

 

[jamesbrown008]

sheet "destination" has got only two fields that is column A and B
but you said
" corresponding row in column B or C in the destination list."

where is the column C in sheet "destination"

post a very small extract of the two sheets and the result

You raise a very valid point Venkat and you have also made me re-think my design. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I will try to explain better below - what I am looking for.<o></o>
<o></o>
There are three columns in my destination sheet.<o></o>
<o></o>
Column A is blank and will later be used to store meta data (the VBA code does not need to touch this column)<o></o>
Column B contains a date <o></o>
Column C contains a user name<o></o>
<o></o>
Below is an example of the Destination Sheet (Lets call this Dest_Sheet)<o></o>
<o></o>
------------ Col A-------------- Col B--------------Col C<o></o>
R 1---------------------- 4/3/2011--------James B<o></o>
R 2---------------------- 4/3/2011--------Steve V<o></o>
R 3---------------------- 4/4/2011--------James B<o></o>
R 4---------------------- 4/4/2011--------Adam S<o></o>
R 5---------------------- 4/5/2011--------Steve V<o></o>
<o></o>
For each row being processed in the destination sheet I want to do a lookup on the source sheet (SRC_Sheet) and find a row where the following three conditions are met:<o></o>
Column A contains the value: "Online Conversions" <o></o>
Column B matches the value from column B in the current row of the dest sheet<o></o>
Column C matches the value from column C in the current row of the dest sheet<o></o>
<o></o>
Below is a sample of the data from the SRC sheet:<o></o>
<o></o>
------------------ Col A------------------------ Col B--------Col C------ Col D----Col E---- Col F<o></o>
R 1---------Online Conversions--------------- 4/3/2011------ James B---- $1,000--- 795------97%<o></o>
R 2---------Agent Conversions--------------- 4/3/2011------ James B---- $1,200--- 700------89%<o></o>
R 3---------Online Conversions--------------- 4/3/2011------ Steve V---- $1,900--- 695------89% <o></o>
R 4---------Agent Conversions--------------- 4/3/2011------ Steve V---- $1,450--- 465------87% <o></o>
R 5---------Agent Conversions--------------- 4/4/2011------ James B---- $1,780--- 235------67% <o></o>
R 6---------Online Conversions--------------- 4/4/2011------ James B---- $1,900--- 565------57% <o></o>
R 7---------Agent Conversions--------------- 4/4/2011------ James B---- $1,230--- 785------90% <o></o>
R 8---------Online Conversions--------------- 4/4/2011------ Adam S---- $1,120--- 786------22% <o></o>
R 9---------Online Conversions--------------- 4/5/2011------ Steve V---- $1,670--- 234------89% <o></o>
R 10--------Agent Conversions----------------4/5/2011------Adam S----- $1,870---567------88%<o></o>
<o></o>
<o></o>
When the script finds a matching row in the Source sheet it should copy specific cells (let's say Columns D-F) to specific cells in the current row of the Destination sheet (again - lets say D-F)<o></o>
<o></o>
So in my example we would be left with the following data in the Dest Sheet:<o></o>
<o></o>
---- Col A--------Col B------ Col C--------Col D---- Col E-- Col F<o></o>
R1---------- 4/3/2011-- James B----$1,000-- 795------97%<o></o>
R2---------- 4/3/2011-- Steve V----$1,900-- 695------89%<o></o>
R3---------- 4/4/2011-- James B----$1,900-- 565------57%<o></o>
R4---------- 4/4/2011-- Adam S---- $1,120-- 786------22%<o></o>
R5---------- 4/5/2011-- Steve V----$1,670-- 234------89%<o></o>
<o></o>
Does this make a little more sense?<o></o>
<o> </o>
<o></o>

 

[jamesbrown008]

Sorry I had formatting issues - here is the same reply (a little more readable)

You raise a very valid point Venkat and you have also made me re-think my design. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I will try to explain better below - what I am looking for.<o></o>
<o></o>
There are three columns in my destination sheet.<o></o>
<o></o>
Column A is blank and will later be used to store meta data (the VBA code does not need to touch this column)<o></o>
Column B contains a date <o></o>
Column C contains a user name<o></o>
<o></o>
Below is an example of the Destination Sheet (Lets call this Dest_Sheet)<o></o>
<o></o>
------------ Col A-------------- Col B----------Col C<o></o>
R 1-------------------------- 4/3/2011--------James B<o></o>
R 2-------------------------- 4/3/2011--------Steve V<o></o>
R 3-------------------------- 4/4/2011--------James B<o></o>
R 4-------------------------- 4/4/2011--------Adam S<o></o>
R 5-------------------------- 4/5/2011--------Steve V<o></o>
<o></o>
For each row being processed in the destination sheet I want to do a lookup on the source sheet (SRC_Sheet) and find a row where the following three conditions are met:<o></o>
Column A contains the value: "Online Conversions" <o></o>
Column B matches the value from column B in the current row of the dest sheet<o></o>
Column C matches the value from column C in the current row of the dest sheet<o></o>
<o></o>
Below is a sample of the data from the SRC sheet:<o></o>
<o></o>
------------------ Col A------------------------ Col B--------Col C------ Col D----Col E---- Col F<o></o>
R 1---------Online Conversions--------------- 4/3/2011------ James B---- $1,000--- 795------97%<o></o>
R 2---------Agent Conversions--------------- 4/3/2011------ James B---- $1,200--- 700------89%<o></o>
R 3---------Online Conversions--------------- 4/3/2011------ Steve V---- $1,900--- 695------89% <o></o>
R 4---------Agent Conversions--------------- 4/3/2011------ Steve V---- $1,450--- 465------87% <o></o>
R 5---------Agent Conversions--------------- 4/4/2011------ James B---- $1,780--- 235------67% <o></o>
R 6---------Online Conversions--------------- 4/4/2011------ James B---- $1,900--- 565------57% <o></o>
R 7---------Agent Conversions--------------- 4/4/2011------ James B---- $1,230--- 785------90% <o></o>
R 8---------Online Conversions--------------- 4/4/2011------ Adam S---- $1,120--- 786------22% <o></o>
R 9---------Online Conversions--------------- 4/5/2011------ Steve V---- $1,670--- 234------89% <o></o>
R 10--------Agent Conversions----------------4/5/2011------Adam S----- $1,870---567------88%<o></o>
<o></o>
<o></o>
When the script finds a matching row in the Source sheet it should copy specific cells (let's say Columns D-F) to specific cells in the current row of the Destination sheet (again - lets say D-F)<o></o>
<o></o>
So in my example we would be left with the following data in the Dest Sheet:<o></o>
<o></o>
---- Col A--------Col B------ Col C--------Col D---- Col E-- Col F<o></o>
R1------------ 4/3/2011---- James B------$1,000--- 795------97%<o></o>
R2------------ 4/3/2011---- Steve V------$1,900--- 695------89%<o></o>
R3------------ 4/4/2011---- James B------$1,900--- 565------57%<o></o>
R4------------ 4/4/2011---- Adam S------ $1,120--- 786------22%<o></o>
R5------------ 4/5/2011---- Steve V------$1,670--- 234------89%<o></o>
<o></o>
Does this make a little more sense?<o></o>
<o></o>

 

[venkat1926]

in destination sheet in C1`type this formula

=INDEX(source!D$1:D$10,MATCH(1,(source!$B$1:$B$10=destination!$A1)*(source!$C$1:$C$10=destination!$B1),0),1)
invoke this formula with control shift enter

copy C! to right and down. when copying(after invoking) select C1 and edit copy (or control C) and select D1 and E1 and paste(control V)
similar select C1 to E1 edit copy and
select C2 down and e

remember that index match function will find only first occurrence of the combination. for e.g the row no. 3 in destination sheet 4/4/2011 and James B the first occurrence in source sheet is
<table width="200" border="0" cellpadding="0" cellspacing="0"><colgroup><col width="72"><col span="2" width="64"></colgroup><tbody><tr height="17"> <td class="xl23" style="height:12.75pt;width:54pt" width="72" height="17"> $1,780.00 </td> <td style="width:48pt" width="64" align="right">235</td> <td class="xl22" style="width:48pt" width="64" align="right">67%</td> </tr></tbody></table>and not
<table width="200" border="0" cellpadding="0" cellspacing="0"><colgroup><col width="72"><col span="2" width="64"></colgroup><tbody><tr height="17"> <td class="xl25" style="height:12.75pt;width:54pt" width="72" height="17"> $1,900.00 </td> <td style="width:48pt" width="64" align="right">565</td> <td class="xl24" style="width:48pt" width="64" align="right">57%</td> </tr></tbody></table>which is second occurrence.

 

[jamesbrown008]

Thanks for this - I will try it now.

I am pretty sure this will work but this is something I need to repeat many times on a daily basis so I was hoping to have it asa VBA module that I could add to my code package.

Anyone know how to do it in vba?

Thanks again Venkat - I appreciate all yor help.

 

[venkat1926]

try this and let me know your comments and then we can find whether the formula itself can be used repeatedly or vba necessary.

 

[jamesbrown008]

in destination sheet in C1`type this formula

=INDEX(source!D$1:D$10,MATCH(1,(source!$B$1:$B$10=destination!$A1)*(source!$C$1:$C$10=destination!$B1),0),1)
invoke this formula with control shift enter

copy C! to right and down. when copying(after invoking) select C1 and edit copy (or control C) and select D1 and E1 and paste(control V)
similar select C1 to E1 edit copy and
select C2 down and e

remember that index match function will find only first occurrence of the combination. for e.g the row no. 3 in destination sheet 4/4/2011 and James B the first occurrence in source sheet is
<TABLE border=0 cellSpacing=0 cellPadding=0 width=200><COLGROUP><COL width=72><COL span=2 width=64></COLGROUP><TBODY><TR height=17><TD style="WIDTH: 54pt; HEIGHT: 12.75pt" class=xl23 height=17 width=72>$1,780.00 </TD><TD style="WIDTH: 48pt" width=64 align=right>235</TD><TD style="WIDTH: 48pt" class=xl22 width=64 align=right>67%</TD></TR></TBODY></TABLE>and not
<TABLE border=0 cellSpacing=0 cellPadding=0 width=200><COLGROUP><COL width=72><COL span=2 width=64></COLGROUP><TBODY><TR height=17><TD style="WIDTH: 54pt; HEIGHT: 12.75pt" class=xl25 height=17 width=72>$1,900.00 </TD><TD style="WIDTH: 48pt" width=64 align=right>565</TD><TD style="WIDTH: 48pt" class=xl24 width=64 align=right>57%</TD></TR></TBODY></TABLE>which is second occurrence.

I am not sure I understand your instructions. You say put the formula into cell C1 in the dest sheet. But the C column has the user names in that make up part of the macthing criteria.

Did you mean D1?

Also - it tires to open a file when I post this formula in - is this normal?

Sorry I am just a little new to this function.

 

[venkat1926]

so;rry yes in D1. when you get opening file hit esc key. I think this is quirk of excel. do you get what you want.

I shall again look into that opening the file window;.