VBA to find the first row that matches criteria and copy the a fixed size range and paste to another sheet

orange12345

New Member
Joined
Jan 25, 2016
Messages
40
Office Version
  1. 365
Platform
  1. Windows
Hi there,

Hope all is staying well and healthy.
I have a worksheet A and worksheet B. Worksheet B is the source excel file where customer comments are saved. customer Name, date, category and their comments. Examples are below. For each quarter, each customer will have only 5 rows.
NameDateProduct 1Product 2Product 3Product 4Product 5
Jean
2020 Q1​
like itbadbadgreatgreat
Jean
2020 Q1​
goodgoodgoodgoodgood
Jean 2020 Q1badbadbadbadbad
Jean2020 Q1goodgoodgoodgoodgood
Jean2020 Q1badbadbadbadbad
Worksheet A is a display file, if an user selects name and Date, in a fixed range (B1: G5), the information of that customer for that quarter should show like below.
like itbadbadgreatgreat
goodgoodgoodgoodgood
badbadbadbadbad
goodgoodgoodgoodgood
badbadbadbadbad



I want the vba to search the worksheet B and if it finds the row where the Name and Date matches the user selection on Worksheet A, it will copy the 5 column next to it and also the four rows below.

Thanks for all the help in advance and happy to explain.
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,582
Office Version
  1. 365
Platform
  1. Windows
Bit hard to tell your exact layout & requirement but I am wondering if you have Excel 365** with the FILTER function & therefore may not need vba?

** I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also better if you can provide sample data & expected results with XL2BB

So, on the off-chance, could something like this be any use?

orange12345 1.xlsm
ABCDEFG
1NameDateProduct 1Product 2Product 3Product 4Product 5
2Tom2020 Q1poorbadbadbadpoor
3Tom2020 Q1goodbadpoorbadpoor
4Tom2020 Q1badbadpoorgoodpoor
5Tom2020 Q1goodgoodpoorbadbad
6Tom2020 Q1goodbadbadbadbad
7Jean2020 Q1like itbadbadgreatgreat
8Jean2020 Q1goodgoodgoodgoodgood
9Jean2020 Q1badbadbadbadbad
10Jean2020 Q1goodgoodgoodgoodgood
11Jean2020 Q1badbadbadbadbad
A



Formula needs to be entered in top-left cell only.

orange12345 1.xlsm
ABCDEFG
1NameJeanlike itbadbadgreatgreat
2Date2020 Q1goodgoodgoodgoodgood
3badbadbadbadbad
4goodgoodgoodgoodgood
5badbadbadbadbad
B
Cell Formulas
RangeFormula
C1:G5C1=FILTER(A!C2:G1001,(A!A2:A1001=B1)*(A!B2:B1001=B2),"")
Dynamic array formulas.
 

orange12345

New Member
Joined
Jan 25, 2016
Messages
40
Office Version
  1. 365
Platform
  1. Windows
Bit hard to tell your exact layout & requirement but I am wondering if you have Excel 365** with the FILTER function & therefore may not need vba?

** I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also better if you can provide sample data & expected results with XL2BB

So, on the off-chance, could something like this be any use?

orange12345 1.xlsm
ABCDEFG
1NameDateProduct 1Product 2Product 3Product 4Product 5
2Tom2020 Q1poorbadbadbadpoor
3Tom2020 Q1goodbadpoorbadpoor
4Tom2020 Q1badbadpoorgoodpoor
5Tom2020 Q1goodgoodpoorbadbad
6Tom2020 Q1goodbadbadbadbad
7Jean2020 Q1like itbadbadgreatgreat
8Jean2020 Q1goodgoodgoodgoodgood
9Jean2020 Q1badbadbadbadbad
10Jean2020 Q1goodgoodgoodgoodgood
11Jean2020 Q1badbadbadbadbad
A



Formula needs to be entered in top-left cell only.

orange12345 1.xlsm
ABCDEFG
1NameJeanlike itbadbadgreatgreat
2Date2020 Q1goodgoodgoodgoodgood
3badbadbadbadbad
4goodgoodgoodgoodgood
5badbadbadbadbad
B
Cell Formulas
RangeFormula
C1:G5C1=FILTER(A!C2:G1001,(A!A2:A1001=B1)*(A!B2:B1001=B2),"")
Dynamic array formulas.
Hi Peter, thanks for your quick reply. Wow this seems like a cool feature. I'm using 365 Pro however when I typed filter function, it shows this function is not valid. But again, thanks a lot for your answer.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,582
Office Version
  1. 365
Platform
  1. Windows
when I typed filter function, it shows this function is not valid.
Pity, that would make it easier. Not everybody on Excel 365 has the new array functions yet. Shouldn't be too ling though I hope. 🤞

Until you get FILTER, try this, copied down to row 5 and across.

orange12345 1.xlsm
ABCDEF
1NameJeanlike itbadbadgreat
2Date2020 Q1goodgoodgoodgood
3badbadbadbad
4goodgoodgoodgood
5badbadbadbad
6
B
Cell Formulas
RangeFormula
C1:F5C1=INDEX(A!C:C,AGGREGATE(15,6,ROW(A!C$1:C$1001)/((A!$A$1:$A$1001=$B$1)*(A!$B$1:$B$1001=$B$2)),ROWS(C$1:C1)))
 

orange12345

New Member
Joined
Jan 25, 2016
Messages
40
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Hi Peter
Pity, that would make it easier. Not everybody on Excel 365 has the new array functions yet. Shouldn't be too ling though I hope. 🤞

Until you get FILTER, try this, copied down to row 5 and across.

orange12345 1.xlsm
ABCDEF
1NameJeanlike itbadbadgreat
2Date2020 Q1goodgoodgoodgood
3badbadbadbad
4goodgoodgoodgood
5badbadbadbad
6
B
Cell Formulas
RangeFormula
C1:F5C1=INDEX(A!C:C,AGGREGATE(15,6,ROW(A!C$1:C$1001)/((A!$A$1:$A$1001=$B$1)*(A!$B$1:$B$1001=$B$2)),ROWS(C$1:C1)))
Hi Peter, thanks a lot !! The formula works like a charm. I did a bit research on the aggregate function still not sure about the function it does here. Could you explain a bit ? Thank you again!!!! I feel really grateful.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,582
Office Version
  1. 365
Platform
  1. Windows
Hi Peter, thanks a lot !! The formula works like a charm.
Good news. Glad it helped. :)


Could you explain a bit ?
Let's take this simpler example on a single sheet

20 09 03.xlsm
ABCDEF
1NameDateProduct 1Jeanlike it
2Jean2020 Q1like it2020 Q1abcdefg
3Tom2020 Q1poorgood
4Tom2020 Q1goodbad
5Jean2020 Q1abcdefgbad
6Tom2020 Q1good
7Jean2020 Q1good
8Jean2020 Q1bad
9Tom2020 Q1good
10Tom2020 Q1bad
11Jean2020 Q1bad
Sample
Cell Formulas
RangeFormula
F1:F5F1=INDEX(C:C,AGGREGATE(15,6,ROW(C$1:C$11)/(($A$1:$A$11=$E$1)*($B$1:$B$11=$E$2)),ROWS(F$1:F1)))


We'll use the formula from cell F2:
=INDEX(C:C,AGGREGATE(15,6,ROW(C$1:C$11)/(($A$1:$A$11=$E$1)*($B$1:$B$11=$E$2)),ROWS(F$1:F2)))

AGGREGATE(15, 6 .. means we are using the 'SMALL' feature of AGGREGATE and ignore errors.
ROW(C$1:C$11) makes the arrray of numbers
{1,2,3,4,5,6,7,8,9,10,11}

(($A$1:$A$11=$E$1)*($B$1:$B$11=$E$2)) is two arrays of True/False multiplied togethert
(($A$1:$A$11="Jean")*($B$1:$B$11="2020 Q1"))
({F,T,F,F,T,F,T,T,F,F,T}*{F,T,T,T,T,T,T,T,T,T,T})
{F,T,F,F,T,F,T,T,F,F,T}

So we have the division
{1,2,3,4,5,6,7,8,9,10,11}/{F,T,F,F,T,F,T,T,F,F,T}
{#DIV/0!;2;#DIV/0!;#DIV/0!;5;#DIV/0!;7;8;#DIV/0!;#DIV/0!;11}


ROWS(F$1:F2) = 2

So we are looking for the 2nd smallest number, ignoring errors, from the last array
That gives 5

INDEX(C:C,5) = "abcdefg"

Hope that makes sense.

BTW, a reminder to help with any further questions you might have:
** I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
 

orange12345

New Member
Joined
Jan 25, 2016
Messages
40
Office Version
  1. 365
Platform
  1. Windows
Good news. Glad it helped. :)


Let's take this simpler example on a single sheet

20 09 03.xlsm
ABCDEF
1NameDateProduct 1Jeanlike it
2Jean2020 Q1like it2020 Q1abcdefg
3Tom2020 Q1poorgood
4Tom2020 Q1goodbad
5Jean2020 Q1abcdefgbad
6Tom2020 Q1good
7Jean2020 Q1good
8Jean2020 Q1bad
9Tom2020 Q1good
10Tom2020 Q1bad
11Jean2020 Q1bad
Sample
Cell Formulas
RangeFormula
F1:F5F1=INDEX(C:C,AGGREGATE(15,6,ROW(C$1:C$11)/(($A$1:$A$11=$E$1)*($B$1:$B$11=$E$2)),ROWS(F$1:F1)))


We'll use the formula from cell F2:
=INDEX(C:C,AGGREGATE(15,6,ROW(C$1:C$11)/(($A$1:$A$11=$E$1)*($B$1:$B$11=$E$2)),ROWS(F$1:F2)))

AGGREGATE(15, 6 .. means we are using the 'SMALL' feature of AGGREGATE and ignore errors.
ROW(C$1:C$11) makes the arrray of numbers
{1,2,3,4,5,6,7,8,9,10,11}

(($A$1:$A$11=$E$1)*($B$1:$B$11=$E$2)) is two arrays of True/False multiplied togethert
(($A$1:$A$11="Jean")*($B$1:$B$11="2020 Q1"))
({F,T,F,F,T,F,T,T,F,F,T}*{F,T,T,T,T,T,T,T,T,T,T})
{F,T,F,F,T,F,T,T,F,F,T}

So we have the division
{1,2,3,4,5,6,7,8,9,10,11}/{F,T,F,F,T,F,T,T,F,F,T}
{#DIV/0!;2;#DIV/0!;#DIV/0!;5;#DIV/0!;7;8;#DIV/0!;#DIV/0!;11}


ROWS(F$1:F2) = 2

So we are looking for the 2nd smallest number, ignoring errors, from the last array
That gives 5

INDEX(C:C,5) = "abcdefg"

Hope that makes sense.

BTW, a reminder to help with any further questions you might have:
Hi Peter, thank you so much for your help on this. I'm always amazed at how people here are so supportive and again feel grateful. Thank yoU!!
 

Watch MrExcel Video

Forum statistics

Threads
1,130,317
Messages
5,641,486
Members
417,211
Latest member
loadius

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top