Create a new sheet dependant on values

[edwgolz]

I have a large file containing data from a golf tournament I run.
First row contains player name.
Second row shows where they finished.
Columns show the data for each year.
Sheet has over 1000 names (2000 rows)
I'd like to create a new sheet showing only the players and corresponding
data that have competed in the last 5 years (ie any value in the last 5
columns). Is there any function or can I create a macro that will search the
entire sheet and copy to a new sheet column 'A' (name and finish) and both
rows of data for those specific players?

 

[Bernie Deitrick]

edwgolz,

Let's say that your first name is in row2, and the 5 years are in column A through E of row 3. Then
in F2, use the formula

=COUNTA(A3:E3) = 5

and in F3, use

=F2

Then copy F2:F3, and paste in F4:F2000. Use data filters based on column F to show only TRUE
values, then Select your data, copy, and paste in a new, blank worksheet.


The key is to create a formula that returns TRUE when you criteria is met....

HTH,
Bernie
MS Excel MVP