Copy row if criteria is met macro

Closed
Ram - Aug 12, 2016 at 01:21 PM
 Ram - Aug 16, 2016 at 12:47 AM
Hello,

I am looking to make a macro that will copy the entire line from sheet 1 to sheet 2 if criteria from column H is met.

Essentially if the cell in Sheet 1 Column H says "Awardee", I'd like all the information from that row to be copied into Sheet 2.
Thanks.

2 replies

fdibbins Posts 33 Registration date Sunday June 19, 2016 Status Contributor Last seen November 20, 2016 1
Aug 14, 2016 at 08:08 PM
Does this have to be VBA? You could do this with an ARRAY formula or with a helper column.

msg back if this a direction you would be interested in?
0
I actually originally was using an IF formula at first but the problem was that it would just keep re entering the first row that met the criteria, "awardee".
0
fdibbins Posts 33 Registration date Sunday June 19, 2016 Status Contributor Last seen November 20, 2016 1
Aug 15, 2016 at 01:59 AM
Option 1			aaaaa	
Head1 Head2 Head1 Head2
aaaaa 1 aaaaa 1
bbbbb 2 aaaaa 4
ccccc 3
aaaaa 4
bbbbb 5
ccccc 6

D3=IFERROR(INDEX(A:A,SMALL(IF($A$3:$A$8=$D$1,ROW($A$3:$A$8)),ROWS($A$1:A1))),"")
ARRAY entered, using CTRL SHIFT ENTER, not just enter
Then copy down and across as needed

Option 2 using a helper column (which you can hide if needed)
Option 2				aaaaa	
Head1 Head2 Head1 Head2
aaaaa 1 aaaaa 1 aaaaa 1
bbbbb 2 bbbbb 1 aaaaa 4
ccccc 3 ccccc 1
aaaaa 4 aaaaa 2
bbbbb 5 bbbbb 2
ccccc 6 ccccc 2

C3=A3&" "&COUNTIFS($A$3:A3,A3)
copied down
This will create a unique ID for each duplicate that can then be used to extract it

E3=IFERROR(INDEX(A:A,MATCH($E$1&" "&ROWS($A$1:A1),$C:$C,0)),"")
regular formula, copied down and across as needed

Hope that helps?
0
Sorry I'm rather new to this level of complexity in excel. Anyway you could give an explanation for what each of these options would do?? And what part of the formula has the actual criteria needed to copy a row? And last question is there a way to set it to only copy specific columns (i.e. A, D, F) in that row if the criteria is met in column L?
Thank you.
0