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

O

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

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sort by date Sort by votes
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.
 
Upvote 0
Peter_SSs said:
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.
Click to expand...
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.
 
Upvote 0
orange12345 said:
when I typed filter function, it shows this function is not valid.
Click to expand...
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)))
 
Upvote 0
Hi Peter
Peter_SSs said:
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)))
Click to expand...
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.
 
Upvote 0
orange12345 said:
Hi Peter, thanks a lot !! The formula works like a charm.
Click to expand...
Good news. Glad it helped. :)


orange12345 said:
Could you explain a bit ?
Click to expand...
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:
Peter_SSs said:
** 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’)
Click to expand...
 
Upvote 0
Peter_SSs said:
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:
Click to expand...
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!!
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Solved
Copy/paste a row from within a loop, before that loop moves on to next cell
Replies
5
Views
227
Joe4
Joe4
S
  • Solved
Create table finding last table row with data not in first column
Replies
3
Views
425
sspatriots
S
M
  • Question
VBA search for specific file name in folders and their subfolders
Replies
7
Views
779
Candyman8019
C
P
  • Question
How to find a word then cross reference to see if match
Replies
0
Views
283
Patriot2879
P
S
  • Solved
Copy from one table to the end of another using structured references
Replies
4
Views
542
sspatriots
S

Forum statistics

Threads
1,214,646
Messages
6,120,715
Members
448,985
Latest member
chocbudda

Share this page

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
Back
Top