Create a new sheet dependant on values
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? |
Create a new sheet dependant on values
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 "edwgolz" wrote in message ... 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? |
All times are GMT +1. The time now is 11:09 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com