Copy Data from Worksheet1 to Worksheet2 IF Worksheet1 Column D = Y

[jeannie v]

Hi Experts:

What I would like to do is copy the data from Worksheet1 titled "JanObs" and
paste it to Worksheet2 titled "HOA Forms" IF Column D on "JanObs" = Y

How can I do this either with formula or Macro?

Any help would be appreciated.

 

[Gord Dibben]

Formula entered in A1 of HOA Forms sheet.

=IF(JanObs!D1="Y",JanObs!D1,"")

Drag/copy down as far as you wish.


Gord Dibben MS Excel MVP

 

[Max]

One way ..

Assume data starts in row2 down in JanObs,

In HOA Forms,
Put in A2: =IF(JanObs!D2="Y",ROW(),"")
Leave A1 blank

Put in B2:
=IF(ROWS($1:1)>COUNT($A:$A),"",INDEX(JanObs!A:A,SMALL($A:$A,ROWS($1:1))))
Copy B2 across by as many cols as there is data in JanObs to bring over, eg
across to K2. Then select A2:K2, copy down to cover the max extent that data
is expected in JanObs. Minimize/hide away col A. Cols B to K will return the
required results with all lines neatly bunched at the top.

 

[jeannie v]

Hi Max:

I should have told you that the Columns are not in the same sequence on both
Worksheets headings that cannot be changed....How would I get it to pop the
correct data to Worksheet2 for all records on Worksheet1 Column D= Y.

So This is Worksheet1 (JanObs) Columns are headed as:
COLUMN HEADER
A #
B Name
C Score Deleted
D Deleted HS
E Rep
F Site
G Date Deleted
H Date Scored
I Reason
J Deleted by
K Per

Worksheet2 (HOA Forms)

A Name
B Blank
C Rep
D Site
E Date Deleted
F Date Scored
G Reason
H Deleted by

I would appreciate any help you can provide......I can use the formulas that
you provided for another document though, so thank you for that.

 

[jeannie v]

Hi Gord:

I left out some important information! Would you please take a look at my
response to Max's solution and see if you can help me further?

Thank you for your expertise.

 

[Max]

.. Columns are not in the same sequence

Quite ok here, think you just need to tweak it a little
so that the returned cols will sync in the destination sheet,
as shown in this sample:
http://www.freefilehosting.net/download/3b1i9
Conditionally extract lines to another sht.xls

In HOA Forms,

In A2: =IF(JanObs!D2="Y",ROW(),"")
Leave A1 blank

In B2:
=IF(ROWS($1:1)>COUNT($A:$A),"",INDEX(JanObs!B:B,SMALL($A:$A,ROWS($1:1))))

Leave C2 blank, since you indicate the col header in C1 is "Blank"

In D2:
=IF(ROWS($1:1)>COUNT($A:$A),"",INDEX(JanObs!E:E,SMALL($A:$A,ROWS($1:1))))
Copy D2 to I2. Then select A2:I2, copy down to cover the max expected extent
of data in JanObs, say down to I500.

 

[jeannie v]

Good Morning, Max:

Thank you so much....This works....Once I began to think it out, it makes
perfect sense and is so easy. I can use this method over and over again.

I appreciate your expertise!

 

[Max]

Welcome, Jeannie
Glad to hear that