Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CREATE DATA FORM FOR DEPENDANT DROPDOWN LIST IN EXCEL | Excel Worksheet Functions | |||
create a list dependant on info in a column | Excel Worksheet Functions | |||
copy values generated by conditional formula in one sheet to the other work sheet as values | Excel Worksheet Functions | |||
Concatenating cell values to create sheet names | Excel Discussion (Misc queries) | |||
Date dependant values. | Excel Discussion (Misc queries) |