Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Go to the Last Date in a Column
I want to build a macro to tell Excel to keep only the latest MonthYr in a
column and to filter out everything else. The overall objective is to combine this instruction with previous macros I have built so that I 1)Add a left-hand rank column, 2) Filter all but the latest MonthYr, 3) Sort by Performance Threshold scores, 4) Rank the scores (using Biff's formula) and 5) Go to the cell containing the name of my organization. Hints are welcome! DOUG in Wichita |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Go to the Last Date in a Column
Doug,
Do you have dates in your column, or just month/year strings that are not true dates? HTH, Bernie MS Excel MVP "Douglas Eckert" wrote in message ... I want to build a macro to tell Excel to keep only the latest MonthYr in a column and to filter out everything else. The overall objective is to combine this instruction with previous macros I have built so that I 1)Add a left-hand rank column, 2) Filter all but the latest MonthYr, 3) Sort by Performance Threshold scores, 4) Rank the scores (using Biff's formula) and 5) Go to the cell containing the name of my organization. Hints are welcome! DOUG in Wichita |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Go to the Last Date in a Column
Bernie: I have Month Comma Space Year-(in four digits): Apr, 2007.
(Thanks for responding. I am out of the office for the rest of the afternoon, but hope to resume with this on Monday early). Doug "Bernie Deitrick" wrote: Doug, Do you have dates in your column, or just month/year strings that are not true dates? HTH, Bernie MS Excel MVP "Douglas Eckert" wrote in message ... I want to build a macro to tell Excel to keep only the latest MonthYr in a column and to filter out everything else. The overall objective is to combine this instruction with previous macros I have built so that I 1)Add a left-hand rank column, 2) Filter all but the latest MonthYr, 3) Sort by Performance Threshold scores, 4) Rank the scores (using Biff's formula) and 5) Go to the cell containing the name of my organization. Hints are welcome! DOUG in Wichita |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Go to the Last Date in a Column
Doug,
That doesn't actually answer my question, since Apr, 2007 could be a date that is formatted mmm, yyyy or it could be the string "Apr, 2007" To tell the difference, select one of the cells. If the formula bar shows the exact same string, then it is a string. If the formula bar show 4/1/2007 (or some other day in April) then the cell is a date. If it is a string, select the column, and press Ctrl-H to bring up the Replace dialog. Under "Find what:" enter a comma, and under "Replace with:" enter a space, the number 1, and a comma " 1," (without the parentheses) and then press OK. This will convert the strings to dates. Then press Ctrl-1 to bring up the format dialog: on the number tab, select "Custom" in the Category window, and in the box below Type: enter in mmm, yyyy and the dates will be formatted to look exactly as they did before. So now that you have dates (Or if they were dates to start) simply select the column, choose Data / Filter / Autofilter, and then click the drop down arrow that appears at the top of the column. Select "(Top 10)", and in the dialog that comes use, reduce the 10 to 1, and the data for the latest month/year combo will be the only visible data (all the rest will be hidden). You can record those actions to use in your macro.... HTH, Bernie MS Excel MVP "Douglas Eckert" wrote in message ... Bernie: I have Month Comma Space Year-(in four digits): Apr, 2007. (Thanks for responding. I am out of the office for the rest of the afternoon, but hope to resume with this on Monday early). Doug "Bernie Deitrick" wrote: Doug, Do you have dates in your column, or just month/year strings that are not true dates? HTH, Bernie MS Excel MVP "Douglas Eckert" wrote in message ... I want to build a macro to tell Excel to keep only the latest MonthYr in a column and to filter out everything else. The overall objective is to combine this instruction with previous macros I have built so that I 1)Add a left-hand rank column, 2) Filter all but the latest MonthYr, 3) Sort by Performance Threshold scores, 4) Rank the scores (using Biff's formula) and 5) Go to the cell containing the name of my organization. Hints are welcome! DOUG in Wichita |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Go to the Last Date in a Column
Bernie: Formatting is "General". I shall work on this a little bit later.
Thank you very much for your suggestions. I shall try them out soon. Doug "Bernie Deitrick" wrote: Doug, That doesn't actually answer my question, since Apr, 2007 could be a date that is formatted mmm, yyyy or it could be the string "Apr, 2007" To tell the difference, select one of the cells. If the formula bar shows the exact same string, then it is a string. If the formula bar show 4/1/2007 (or some other day in April) then the cell is a date. If it is a string, select the column, and press Ctrl-H to bring up the Replace dialog. Under "Find what:" enter a comma, and under "Replace with:" enter a space, the number 1, and a comma " 1," (without the parentheses) and then press OK. This will convert the strings to dates. Then press Ctrl-1 to bring up the format dialog: on the number tab, select "Custom" in the Category window, and in the box below Type: enter in mmm, yyyy and the dates will be formatted to look exactly as they did before. So now that you have dates (Or if they were dates to start) simply select the column, choose Data / Filter / Autofilter, and then click the drop down arrow that appears at the top of the column. Select "(Top 10)", and in the dialog that comes use, reduce the 10 to 1, and the data for the latest month/year combo will be the only visible data (all the rest will be hidden). You can record those actions to use in your macro.... HTH, Bernie MS Excel MVP "Douglas Eckert" wrote in message ... Bernie: I have Month Comma Space Year-(in four digits): Apr, 2007. (Thanks for responding. I am out of the office for the rest of the afternoon, but hope to resume with this on Monday early). Doug "Bernie Deitrick" wrote: Doug, Do you have dates in your column, or just month/year strings that are not true dates? HTH, Bernie MS Excel MVP "Douglas Eckert" wrote in message ... I want to build a macro to tell Excel to keep only the latest MonthYr in a column and to filter out everything else. The overall objective is to combine this instruction with previous macros I have built so that I 1)Add a left-hand rank column, 2) Filter all but the latest MonthYr, 3) Sort by Performance Threshold scores, 4) Rank the scores (using Biff's formula) and 5) Go to the cell containing the name of my organization. Hints are welcome! DOUG in Wichita |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNTIF: 2 criteria: Date Range Column & Text Column | Excel Worksheet Functions | |||
Need the formula or macro. If i enter today date in the cell (Row 1,Column 2) and on tab out, the column 1 cell should be filled with "corresponding Day" of the date | Excel Discussion (Misc queries) | |||
Need Formula or macro. If i enter today date in the cell (Row 1,Column 2) and on tab out, the column 1 cell should be filled with "corresponding Day" of the date | Excel Discussion (Misc queries) | |||
How to find the most recent date in a column based on other column | Excel Worksheet Functions | |||
If data in one column, take date, add 2 days, and turn the entire column a color... | Excel Discussion (Misc queries) |