Excel Question #46

[Kevy Baby]

Originally Posted by Alex

If the second column is all in those format with an 11 digit date you can use a formula to trim the value down to just the first 11 digits. So if in A1 you had:

Doh! Why didn't I think of this simple solution. Thanks for pointing out what should have been obvious.

Originally Posted by Alex

=DATEVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTI TUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(S UBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTIT UTE(G1,"Jan ","01/"),"Feb ","02/"),"Mar ","03/"),"Apr ","04/"),"May ","05/"),"Jun ","06/"),"Jul ","07/"),"Aug ","08/"),"Sep ","09/"),"Oct ","10/"),"Nov ","11/"),"Dec ","12/")," ","/"))

I wonder if the time to create the above formula is less than just doing 12 search-and-replaces? Thanks for the tip though.
______________________

On a different Excel question:

I am a keyboard fanatic, especially when it comes to Excel: I prefer to use my keyboard for as much as possible (and NOT using the mouse). If I am navigating a spreadsheet to a cell that already has data in it, is it possible to "click" in a cell (with information in it already) using just the keyboard. Obviously, I could just start typing, but that would overwrite what is already in the cell. Right now, the only way I know how to do this is to click with the mouse.

Am I stuck using the mouse for this one detail? For as much as I live in Excel, this would certainly help my day.

[Ghoulish Delight]

F2

ETA: F2 also serves a similar purpose in Windows explorer. If you have a folder or file selected, F2 will bring you into filename editing mode (instead of having to do that click-and-wait thing I hate so much).

ETA more: ctrl-u on a Mac

[Kevy Baby]

Originally Posted by Ghoulish Delight

F2

Thank you - you are my hero!!!

Originally Posted by Ghoulish Delight

ETA more: ctrl-u on a Mac

Thank you for this. It is for work, where I work on a PC. Excel on the Mac sucks weenie

[Alex]

Originally Posted by Kevy Baby

I wonder if the time to create the above formula is less than just doing 12 search-and-replaces? Thanks for the tip though.

Well, it would be 13 search-and-replaces and then still one formula and column copy-and-paste (to convert the text to a date). And it took me about 90 seconds to do the formula.

The advantage of the formula is that you are less likely to make a mechanical error than doing a repetitive task 13 times.

But mostly it was with an eye towards this being something done more than once. If it is a one off then search and replace isn't much less efficient.

[mousepod]

Originally Posted by Kevy Baby

Excel on the Mac sucks weenie

My copy doesn't. Is there a special key I need to be hitting? And would it be cheating?

[Kevy Baby]

Is there a way to enter a manual line break in a concatenate formula?

For example, if:

Cell A1 = "Name"
Cell B1 = "Address"
Cell C1 = "City, ST Zip"

What would I need to enter in the concatenate formula so that cell A2 looked like this (regardless of column width of course):

Name
Address
City, ST Zip

Obviously, the starting formula is:
=CONCATENATE(A1,B1,C1)

I tried looking through the help menu but couldn't find anything




ETA: Never mind. I tried out this really cool thing called "Google" and it helped me find the answer: CHAR(10)

The formula would be:
=CONCATENATE(A1,CHAR(10),B1,CHAR(10),C1)

[Betty]

Originally Posted by Kevy Baby

Is there a way to enter a manual line break in a concatenate formula?

For example, if:

Cell A1 = "Name"
Cell B1 = "Address"
Cell C1 = "City, ST Zip"

What would I need to enter in the concatenate formula so that cell A2 looked like this (regardless of column width of course):

Obviously, the starting formula is:
=CONCATENATE(A1,B1,C1)

I tried looking through the help menu but couldn't find anything




ETA: Never mind. I tried out this really cool thing called "Google" and it helped me find the answer: CHAR(10)

The formula would be:
=CONCATENATE(A1,CHAR(10),B1,CHAR(10),C1)

Hey - that a pretty neat trick. I learned something new today!

[Kevy Baby]

Okay, these two questions are a little different: is there a better way to accomplish the two tasks below?

1. Get a list of all entries in a column (in other words, if I have 50,000 records and column H has a listing by state, I want a list of the states covered in this list [obviously not all 50 states]).

1. Sort by Column H
2. Highlight Column H and use the Count Subtotal (Data > Subtotals).
3. Click on the "2" in the upper left corner to collapse the selection to just the counts (when you use the Count Subtotal, a small "1", "2", and "3" appear in the upper left corner of the sheet - this may have a name, but I don't know what that name is).
4. Highlight all of the Count Summary Sub-heads (again, this may have a name, but I am not sure what it is: it is the titles that appear in a new column that is created when the "Sub Total by Count" is run).
5. Select just the visible cells in that column (alt-;) and copy. Be sure to not select the last entry, which is the "Grand Count".
6. In a separate worksheet (or somewhere else), Edit > Paste Special > Values. Highlight all of those cells and using the Edit > Replace command, get rid of " Count" from each entry

2. Create a list of records with birthdays in a certain month (in this case, April).

1. Convert the Birth Date column to text. Note that this data came to me with the date already in a text format listed at YYYYMMDD (no dashes or slashes).
2. Create two new columns. In the first column, use the RIGHT function to reduce the date down to just the month and year (the last four characters): =LEFT(G2,4). In the second column, use the LEFT function to reduce the date down to just the month: =RIGHT(H2,2).
3. Copy/paste special this last column as values and then convert Text to Numbers. This leaves with numerical data of 1-12 in this column.
4. Auto-Filter the spreadsheet and then select for this column any number that isn't a 4.
5. Select all of the numbers remaining visible in this column (everything but the 4's) and then highlight only the visibles (alt-;). Delete (with the only option being the entire rows.
6. Under the Data > Filter menu, choose "Show All"

[Alex]

For the first one.

Look up pivot tables.

As a simple example:

1. Make sure your state column has a column header (Such as "State")
2. Select Column H
3. Data > Pivot Table and Pivot Chart Report
4. Leave "Microsoft Office Excel list or database" and ""PivotTable" selected.
5. Click Next
6. PivotTable and Pivot Chart Wizard - Step 2 of 3 should show your Column H in the Range field ("$H:$H").
7. Click Next
8. Provide a location where the data can go. Probably you should leave "New worksheet" selected.
9. Click "Layout"
10. Drag "State" into the Row section of the window that pops up. Then drag it into the Data section.
11. Click Ok.
12. Click Finish.

On a new worksheet you should have a list of all the states in the column as well as the count in a separate row. If you want the data free from the pivot table, just copy the cells in the list and paste them to a new location.

[Alex]

For the second one:

Assuming all you need is the original records but limited by date.

1. Where you would have put the =LEFT(G2,4) -- presumabely H2 -- put this formula instead:

=IF(MID(G2,5,2)="04", "AAAAA", "ZZZZZ")

2. This formula says if the 5th and 6th character of G2 equals "04" populate the cell with "AAAAA" otherwise populate the cell with "ZZZZZ". Obviously the fill can be anything but AAAAA and ZZZZZ are likely unique making searching feasible.

3. Drag the formula down the entire column.

4. Sort the spreadsheet in ascending order by column H.

5. Search "ZZZZZ" to find the first non April row.

6. Select all the rows below that and delete them.