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

  • Thread starter Thread starter jeannie v
  • Start date Start date
J

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