Excel Question #46

[Kevy Baby]

Is there a way to tell Excel to stop turning email addresses in Excel databases into hyperlinks? We keep (small) databases in Excel and when we enter an email address, it automatically turns it into a hyperlink.

Previously, I just ignored this, but ran into an issue today where someone who I gave the database to so that they could email things for me was just clicking on the hyperlinked email address to address the email. The problem is that on a few records, when an email address was changed (usually because of personnel change for that record), the hyperlink did not automatically update.

I was able to do a mass change to ensure that the problem does not occur for the list as it exists right now, but I am searching for a long term solution.

[Ghoulish Delight]

It's in the AutoCorrect settings in Tools. Deselect "Internet and network paths with hyperlinks" box on the "AutoFormat As You Type" tab.

[Alex]

That is the way to do it, but keep in mind that is an application level setting, not document. So everybody who uses the doc will have to do that change, and once done will apply to all your documents.

[Gemini Cricket]

It's in the AutoCorrect settings in Tools. Deselect "Internet and network paths with hyperlinks" box on the "AutoFormat As You Type" tab.
Awesome. I had the same prob. It can also be done in Word as well. Yay!

[Kevy Baby]

It's in the AutoCorrect settings in Tools. Deselect "Internet and network paths with hyperlinks" box on the "AutoFormat As You Type" tab.

That is the way to do it, but keep in mind that is an application level setting, not document. So everybody who uses the doc will have to do that change, and once done will apply to all your documents.Thank you. I looked everywhere except in Autocorrect settings. I couldn't find anything in help either.

[Alex]

Pretty much any and all formatting that Word/Excel do automatically can be controlled through AutoCorrect.

AutoCorrect is a feature much reviled by light users of the application (and justifiably so for some of the default settings) but is among one of the most powerful tools for advanced users. Just a mild example but currently I have reason to type this phrase a lot: HL/CL Statement Suppression Q1 2009 - Project #XXXXX. And it has to be in that form. Because of AutoCorrect and a little bit of acclimation I just type /hl and it automatically gets transformed into the correct phrase. I have another one for my full signature block. I know some people who have turned Word into a shorthand machine their AutoCorrect list is so extensive and their comfort in typing that way so practiced.

[Kevy Baby]

I have a few things I have "programmed" in Word to do that. I agree that it is a great tool!

[Kevy Baby]

Rather than starting yet another Excel help thread, I drudged up an old one.

If I have a series of cells that are calculation results, is there a way to get a count of just the cells with a result value of greater than zero (or conversely, those cells with result value of zero)

I tried using DCountA, but that didn't work (unless I wrote the formula incorrectly).

Any ideas


ETA: I found my own answer: a CountIF function

[Alex]

Dammit, how can I look brilliant if you go and answer your own questions?

[Kevy Baby]

Dammit, how can I look brilliant if you go and answer your own questions?It took me 45 minutes of searching before I found it: how long would it have taken for you to provide the answer?

[Bornieo: Fully Loaded]

Its been a while but I "think" you can highlight the column and the right click to get to the box where you can make the changes or "Remove all hyperlinks".

[Alex]

It took me 45 minutes of searching before I found it: how long would it have taken for you to provide the answer?

That one was an answer I'd have known off the top of my head, so as long as it took to quickly test to make sure I wasn't misremembering.

[Kevy Baby]

That one was an answer I'd have known off the top of my head, so as long as it took to quickly test to make sure I wasn't misremembering.See; you ARE brilliant!

[Kevy Baby]

Rather than starting another Excel Help thread...

I know that there are probably more elegant ways to achieve the end goal, but this project has sort of evolved to needing to ask this question.

I have a spreadsheet with several tabs. The first tab is the "master" where I can enter information needed for all of the other tabs. There are then individual tabs for each output. The output tabs reference back to the master: sometimes to information that is static to all output tabs and some information that is variable.

I actually have questions on two levels, though they are interconnected

Duplicating tabs
This is a 'one time' question. Currently, there are 13 output tabs and I need to increase to 27 (this will be my maximum needed for this project). Is there a way to have the new tabs reference the correct cell on the master tab for the variable data.

Simplifying: If

Cell A1 in Output Tab 1 references Cell B3 on the master tab (=Master!B3)
Cell A1 in Output Tab 2 references Cell B4 on the master tab (=Master!B4)
Cell A1 in Output Tab 3 references Cell B5 on the master tab (=Master!B5)How can I create Output Tab 4 to have Cell A1 reference Cell B6 on the master tab (=Master!B6)?

Master and Slaves

I am sure there is some name to what I am looking for and if it does exist, it may trump the first question above. And no, I am not inquiring about B & D.

Can I set up a system where if I need to make a global change to the output tabs, I can make it on one tab and all of the other tabs will automatically update?

For example, if I need the calculation in cell D14 to change from "=sum(A1 * A2)" to be "=sum(A1 / A2)", is there a way to set this up to change automatically on each output tab? Or is the only way to accomplish this beb to make the change in cell D14 in each Output tab (which I know I could accomplish via copy and paste, but there is always the danger or missing something).

OK Alex: prove your mettle!

[Betty]

I think asking about B&D would have been more fun.

I have no help for you on the excel question - but it did give me an idea for a spreadsheet of my own. Thanks.

[Ghoulish Delight]

I assume you want a solution that doesn't involve learning VBA?

[Alex]

I'm not sure I understand the first question. Do you mean you want it, when you create a new worksheet that it will automatically know that cell A1 should have a certain formula in it?

But yeah, I may just be fried (it has been a rough day) but I can't think of any way of doing either half that doesn't involve macros.

[Ghoulish Delight]

The quick and dirty way I can think of answering question 1 does involve a small amount of VBA knowledge, but not a lot. Depending on how large each sheet is, it could take some tedious initial setup.

But the basic steps are:
1) start recording a macro
2) copy a sheet to a new sheet
3) manually change everything you want to change
4) stop recording the macro
5) (this is the tricky part, and I'd need to do some learning myself to get it right) edit the macro.
5a) You should see that it's saying to copy specifically whichever sheet you recorded the macro from. Change it to whatever the syntax is that means "current sheet'.
5b) you'll see a line for each edit you made explicitly setting the cell to whatever you set it to. Change that to a formula that increments it from the same cell on the current sheet.

If you've got a mind to go that route, I can help you get steps 5a and 5b right later this evening.

[Kevy Baby]

First and foremost, thank you to GD and Alex for responding - I know I can always count on you to offer assistance on this.

And thank you to Betty for, well... thinking that way!

I assume you want a solution that doesn't involve learning VBA?Yes

However, I found a fairly painless solution to Question 1. I just made several copies of the last tab and did a replace in each tab from "14" (the row on the main tab that all of the data was in that pertained to that output tab) to "15" (or "16", etc.). Since most all of the info in the tabs is cell referencing and text, it was a safe thing to do.

Took about 15 minutes to create all the copy tabs, rename them all (there were 14 new tabs to create) and fix the cell references using search and replace.

I'm not sure I understand the first question. Do you mean you want it, when you create a new worksheet that it will automatically know that cell A1 should have a certain formula in it?Yes, that was the goal. But as noted above, I found an inelegant, albeit quick and painless solution.

Now, how about the second question? That one has me stumped.

[Drince88]

For the second part - highlight ALL the tabs you want to make a change in (use the shift or control key) - it'll say [Group] at the top next to the workbook name. And then on the first tab, change the equation in the cell, and it should change it in all of them. I just tested it and it worked.

The BIG thing on this, though - is to un-group the tabs before you go and do more work - otherwise you run the risk of making changes to every sheet, that you didn't really mean to (not that I PERSONALLY have ever done anything like that - nope, not me!)

[Betty]

For the second part - highlight ALL the tabs you want to make a change in (use the shift or control key) - it'll say [Group] at the top next to the workbook name. And then on the first tab, change the equation in the cell, and it should change it in all of them. I just tested it and it worked.

The BIG thing on this, though - is to un-group the tabs before you go and do more work - otherwise you run the risk of making changes to every sheet, that you didn't really mean to (not that I PERSONALLY have ever done anything like that - nope, not me!)

That's a great trick!

[Kevy Baby]

For the second part - highlight ALL the tabs you want to make a change in (use the shift or control key) - it'll say [Group] at the top next to the workbook name. And then on the first tab, change the equation in the cell, and it should change it in all of them. I just tested it and it worked.

The BIG thing on this, though - is to un-group the tabs before you go and do more work - otherwise you run the risk of making changes to every sheet, that you didn't really mean to (not that I PERSONALLY have ever done anything like that - nope, not me!)Great tip - I shall try this tomorrow.

ETA: I just tested and it works!

[Alex]

That said, if you're going to group every cell each time you want to update it would probably be about as fast to just copy and paste into each cell.

Though I'd completely forgotten about grouping.

[Kevy Baby]

That said, if you're going to group every cell each time you want to update it would probably be about as fast to just copy and paste into each cell.I disagree. Since there are 27 tabs, grouping the tabs is much quicker.

[Alex]

Yes, but if you want to highlight each cell you still have to click on it (and make sure you correctly ctrl-click with each so you don't clear the selections you've already made and then find that you had halfway through and your group only applied to the last six you selected).

If you're going to leave the cells grouped permanently then I agree it is a great solution.

[Kevy Baby]

If I first click on the last tab, then shift-click on the second tab (the first I would need to modify), make the change in the cell(s) on that tab, it makes the global change pretty quickly.

Global changes are fairly minimal (simply to accommodate the every-changing whims of the boss), so I believe this should be a good solution.

[Ghoulish Delight]

My turn. I'm sure this one is blindingly stupid, but I can't for the life of me come up with the solution.

Cell A1 has a formula that results in a column letter.
Cell A2 has a formula that results in a row number.

I want cell A3 to be the value of the cell that would be referenced by the values of A1 and A2.

So if the formula in A1 results in "C", and the formula in A2 results in "6", I want A3 to be the value of cell C6.

[Moonliner]

My turn. I'm sure this one is blindingly stupid, but I can't for the life of me come up with the solution.

Cell A1 has a formula that results in a column letter.
Cell A2 has a formula that results in a row number.

I want cell A3 to be the value of the cell that would be referenced by the values of A1 and A2.

So if the formula in A1 results in "C", and the formula in A2 results in "6", I want A3 to be the value of cell C6.

=INDIRECT(A1&A2)

[Ghoulish Delight]

I knew there had to be away to do it in one line. Thanks Moonie.

[Kevy Baby]

Okay, I know I am going to smack my forehead when the answer gets posted because I know the answer is painfully simple and I am just clueless on this one.

I want the text in cell A1 to not print if the value in cell A2 is blank. I thought the easiest way to accomplish this was to conditionally format cell A1 with a white font if A2 is = 0. However, what is the stinking formula I would need to enter into the "Formula Is" cell in Condition 1 to accomplish this? I know how to set the formatting once I have a True result.



ETA: I found my own answer. The syntax was what was confusing me. In the cell, I had to enter =(A2=0)

[Moonliner]

Okay, I know I am going to smack my forehead when the answer gets posted because I know the answer is painfully simple and I am just clueless on this one.

I want the text in cell A1 to not print if the value in cell A2 is blank. I thought the easiest way to accomplish this was to conditionally format cell A1 with a white font if A2 is = 0. However, what is the stinking formula I would need to enter into the "Formula Is" cell in Condition 1 to accomplish this? I know how to set the formatting once I have a True result.

ETA: I found my own answer. The syntax was what was confusing me. In the cell, I had to enter =(A2=0)

Dammit, how can I look brilliant if you go and answer your own questions?

[Kevy Baby]

Okay Moonliner (or Alex, GD, BTD, Andrew, or anyone else who might now).

I have a daily log that I get. Because the raw data is not in a format I can present to the client, I copy and Paste Special > Contents the relevant data into my pre-formatted spreadsheet. The majority of the data basically gets entered when a shipment is arranged. As delivery information (such as "Delivery Appointment" and "Actually Delivery" dates) is updated, it is added to the data and gets copied into my formatted spreadsheet during the copy paste step.

However, I have recently discovered that on a rare occasion, the delivery appointment is chaged after first being entered. While I know it has happened in the past, the way I import the data, I do not see this change (I am only looking for NEW information in the data).

Is there a way to highlight when previously entered data changes in the process described above? I don't even know where to begin on this one.

HELP!

[Moonliner]

Okay Moonliner (or Alex, GD, BTD, Andrew, or anyone else who might now).

I have a daily log that I get. Because the raw data is not in a format I can present to the client, I copy and Paste Special > Contents the relevant data into my pre-formatted spreadsheet. The majority of the data basically gets entered when a shipment is arranged. As delivery information (such as "Delivery Appointment" and "Actually Delivery" dates) is updated, it is added to the data and gets copied into my formatted spreadsheet during the copy paste step.

However, I have recently discovered that on a rare occasion, the delivery appointment is chaged after first being entered. While I know it has happened in the past, the way I import the data, I do not see this change (I am only looking for NEW information in the data).

Is there a way to highlight when previously entered data changes in the process described above? I don't even know where to begin on this one.

HELP!

If I read this correctly, It sounds like an issue with the export. Does the daily log you receive include these records that were updated, or just ones that were created since the last log was created?

[Kevy Baby]

If I read this correctly, It sounds like an issue with the export. Does the daily log you receive include these records that were updated, or just ones that were created since the last log was created?It is not an issue with the data: I need to be able to see when the data has changed after being entered. Note that I cannot control the data (I export it from a legacy system into an Excel file to download).

The recent issues was when the Delivery Appointment date was originally set for 3/26/09 and was entered as such. Due to weather delays, the Delivery Appointment was delayed until 3/30/09. Unfortunately, since I only look for new data, I did not see that they had changed the date (which, as you might guess, is a problem).

[Moonliner]

It is not an issue with the data: I need to be able to see when the data has changed after being entered. Note that I cannot control the data (I export it from a legacy system into an Excel file to download).

The recent issues was when the Delivery Appointment date was originally set for 3/26/09 and was entered as such. Due to weather delays, the Delivery Appointment was delayed until 3/30/09. Unfortunately, since I only look for new data, I did not see that they had changed the date (which, as you might guess, is a problem).

So my question is, do the log files as currently created include these edited records?

If No: Then you need to re-write the log file generator. Hopefully, in addition to the date_created field (I assume that's what you are looking at the create the log file) there is also some type of date_changed or date_updated field.

If Yes: Then you need to rewrite your import routine to allow for these new records.

[Drince88]

WAY outside the box and cludgy, but maybe it will trigger your creative juices for a better fix.

Have two versions of the formatted worksheet in a workbook. Put the new data in 'sheet 2' and have it conditionally formatted to turn red (or whatever) when the data does not equal the same data in sheet 1, which was the previous version of the data.

[Kevy Baby]

Actually thought about that solution, but thought there was a better way. Moonie has been generous enough to offer his assistance, but we haven't been able to connect yet.

[Kevy Baby]

Huh - rereading the above, I realize I never connected with Moonie on this. But I did figure out a solution on my own: for the one date that was suspect of changing, I had the date entered elsewhere on the spreadsheet. I was able to set up conditional formatting so that if the one date ever became different than what it was supposed to be, it would flag itself.

But on to a new challenge

I have two data fields that I need to convert. Both actually looked like the second example below, but I was able to strip the time out since all records had the same time (12:00 a.m.) - a simple search and replace took care of those!

The two fields have the data as "Apr 30 2007" or "Jan 06 2010 01:30PM". I haven't touched the second column yet (with the time still included), but I tried formatting the first in a different date format and that didn't work. Is there a way to format the first column as a date? I will probably just do a search and replace: "Jan" becomes "1", etc. then replace all the spaces with "/" - from there it should be an easy formatting issue. Not elegant, but solves the problem.

But what about the second column? I am at a loss as to how to delete the time when all of the times are different (I don't need the time - only the date). I think I could split text to columns (at the spaces) and then just delete the time, put the data back together with a Concatenate and format. Is there a better solution?

Any suggestions?

[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:

Jan 06 2010 01:30PM

then in cell B1 put this formula:

=LEFT(A,11)

That will result in A2 having a value of Jan 06 2010. Copy the formula down the column. Then copy column B (select column, control-c) and then paste into column C as values only (select column, right click, paste special, values, ok). Delete columns A and B as unnecessry.

So now you have a column that is just a text-formatted date. To convert the text into a date format (this is messy but it will work):

In cell A2 copy this formula:

=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/")," ","/"))

This will result in a five digit number that is an Excel date code.

Copy Column B into Column C as values only (see above). Now change the date format for the column to whatever date format you want and you should be good.

That messy formula assumes the month abbreviations are

Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec

If they aren't the formula will need to be tweaked.

[Alex]

For the formula, when you paste into excel be carefule of extra white space. For example, vBulletin is adding a space between the SUBSTITUTE should always be one word so remove the occasional break).

[Kevy Baby]

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.

=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]

F2Thank you - you are my hero!!!ETA more: ctrl-u on a MacThank you for this. It is for work, where I work on a PC. Excel on the Mac sucks weenie

[Alex]

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]

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]

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]).
Sort by Column H
Highlight Column H and use the Count Subtotal (Data > Subtotals).
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).
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).
Select just the visible cells in that column (alt-;) and copy. Be sure to not select the last entry, which is the "Grand Count".
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 entry2. Create a list of records with birthdays in a certain month (in this case, April).

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).
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).
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.
Auto-Filter the spreadsheet and then select for this column any number that isn't a 4.
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.
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.

[Kevy Baby]

Thanks Alex, I will check that out: I need to learn pivot tables.

I have never taken any classes on Excel (and it shows) - almost everything I know has been due to a need to know how (I am currently learning how to use lookup tables).

[Alex]

Same here. I suck at pivot tables and generally brute force my way through things when the need would otherwise arise. But a count in a single column is pretty straightforward.

[Moonliner]

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]).

2. Create a list of records with birthdays in a certain month (in this case, April).
[LIST=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).


When all you have is a hammer, every job looks like a nail.
-- Unknown.


Seriously dude, import this **** into a database.

#1 Then becomes: Select DISTINCT State from Your.bd

#2 Would be: Select * from Your.db where mid(datefield,5,2)='04'

[Kevy Baby]

#2 Would be: Select * from Your.db where mid(datefield,5,2)='04'I found a better to resolve for #2: I had the client provide me with records already suppressed to the month of April :)

When all you have is a hammer, every job looks like a nail.
-- Unknown.

Seriously dude, import this **** into a database.Well, yes, this might be a better tool. But then I would have to purchase and learn said new tool. If 95% of what I need to transport can be covered by a pickup truck, I am not going to buy a semi-truck and get a class A license for that other 5% - I'll just make a couple of extra trips as needed.

Besides, I got to learn how to make a lookup table today!

[Moonliner]

I found a better to resolve for #2: I had the client provide me with records already suppressed to the month of April :)

Well, yes, this might be a better tool. But then I would have to purchase and learn said new tool. If 95% of what I need to transport can be covered by a pickup truck, I am not going to buy a semi-truck and get a class A license for that other 5% - I'll just make a couple of extra trips as needed.

Besides, I got to learn how to make a lookup table today!

Purchase is not a problem, if you have excel you probably already have Microsoft Access (http://www.amazon.com/Access-Dummies-Laurie-Ulrich-Fuller/dp/0470046120) in house. That's all you would need (http://office.microsoft.com/en-us/help/HA012013211033.aspx). If not, MySQL (Which runs this site) is free for the downloading albeit with a somewhat higher learning curve than Access.

[Kevy Baby]

Purchase is not a problem, if you have excel you probably already have Microsoft Access (http://www.amazon.com/Access-Dummies-Laurie-Ulrich-Fuller/dp/0470046120) in house. That's all you would need (http://office.microsoft.com/en-us/help/HA012013211033.aspx).Since I am, for the moment, still running on Office 2003, I have no Access. Alas, much to my chagrin, I am updating to Office 2007 Professional (the disk was just placed on my desk), so I have it will soon.

I have been reticent to move to 2007 as it seems that everything has been moved in Excel. But I need to be only three years out of date and not seven (we do get a free upgrade to 2010 as well).

[Alex]

One question, are manipulations something you have do repeatedly over time in the same way or are they always one offs?

[Moonliner]

Since I am, for the moment, still running on Office 2003, I have no Access. Alas, much to my chagrin, I am updating to Office 2007 Professional (the disk was just placed on my desk), so I have it will soon.

I have been reticent to move to 2007 as it seems that everything has been moved in Excel. But I need to be only three years out of date and not seven (we do get a free upgrade to 2010 as well).

I ran the beta of Word 2010 for awhile. I was less than impressed it struck me as a big step backwards in usability. Be sure to take a long look at it before you leap, free might be too high of a price to pay.


As for 2007 the "Ribbon" interface is not that bad. Just remember to look at the tabs across the top and think which one best fits what you are trying to do. Chances are that's were you will find it.

[Kevy Baby]

One question, are manipulations something you have do repeatedly over time in the same way or are they always one offs?I usually only ask if I think that it is something I MIGHT need to use again in the future. If I know something is one-off, I usually just kluge through it. For example, while I didn't come here for learning about Lookup tables and the project I used it on really didn't need it, I still used the opportunity to learn how to use them as I have needed them in the past and I am sure I will in the future.

But for the most part, most of what I do is similar in nature, but not the same thing. If there is something that needs to be done repeatedly, I use programmers at my vendors for that. Also, I need to be careful about using anything more sophisticated than Excel as I often have to share the information with others who struggle just with Excel. In other words, I have to keep what I do user friendly.

I also ask here because I just like to learn something new.

[Ghoulish Delight]

I should write a routine that increments the thread title every time Kevy posts a question.

[Moonliner]

I should write a routine that increments the thread title every time Kevy posts a question.

Better declare the counter as long int just in case.....

[Kevy Baby]

I learned something new about Excel today: the maximum number of digits in a number that you can have accurately in a cell is 15. Yes, there is a reason for it (an IEEE standard) and there are ways around it (format the cell as Text or turn on "Precision as Displayed").

A coworker was entering USPS Tracking numbers that were 19 digits and the last four digits were turned into zeroes. Did a little research and this is what I learnt. For our purposes, formatting the cells as Text worked just fine. From browsing the link below, I am not sure of the overall impact of turning on "Precision as Displayed."

http://support.microsoft.com/kb/78113

[Kevy Baby]

Alright, next question on lookups...

I have fallen in love with VLookups and I use them frequently(although for some reason I have yet to need to use an HLookup function). I am such a geek.

I am sure this is another simple one, but my searches haven't provided the results I need. Basically, I need to find the value in an array when value A matches the header across the top and value B matches the list down the side.

For example, if I had three column headers; apple, banana, and orange and three row headers; Ralph, Fred, and George. For simplicity's sake, lets say the values (left to right by row) are 1-9 (apple/Ralph is 1, banana/Ralph is 2, orange/Fred is 8, etc.)

How do I look up the value based on TWO values - so that the look up knows that when value A is (for example) orange and value B is Fred, that the result is "6"?



ETA: for what I needed tonight, I was able to cheat by using a VLookup since the column header info was just the numbers 1-8 (UPS Zones). By using this formula:

=VLOOKUP(J3,'[Lookup table 121010.xlsx]Sheet4'!$A$35:$I$42,H3+1,FALSE)

In this case, you will see I used the value in H3 (Zone) and added one to it. Since the first column of the array was the value that VLookup was looking for a match on, the second column would be Zone 1, the third Zone 2 and so on. By adding 1 to the value of the zone, it made the formula look in the correct column. I just couldn't figure out how to make it look up the correct column based on a text or non-sequential number value.

[Alex]

How will the cell that triggers the formula be formatted? That is, what exactly would be your formula's input? A single cell with value apple/Ralph? One cell says apple and the next says Ralph?

Anyway, for this array (cell A1 is the blank top left)


apple banana orange
Ralph a b c
Fred d e f
George g h i


And for simplicity sake let's say that the first half of what you're looking for (e.g. George) is in cell A12 below that array and the second half (e.g. banana) is in B12.

Then in C12 this formula would return h

=INDEX(A1:D4,MATCH(B12,A1:A4,0),MATCH(A12,A1:D1,0) )

If the input were actually in one cell you'd have put a formula into where this says "B12" and "A12" to parse the input first.

[Alex]

Hey Kevy, I'm just curious. Did you get a chance to try this and were the instructions clear?

[Kevy Baby]

Hey Kevy, I'm just curious. Did you get a chance to try this and were the instructions clear?I didn't, but I have now

I did find a minor error in your formula: you had switched the A12 and B12 references in the Match portion of the formula

=INDEX(A1:D4,MATCH(B12,A1:A4,0),MATCH(A12,A1:D1,0) ) <-- you had
=INDEX(A1:D4,MATCH(A12,A1:A4,0),MATCH(B12,A1:D1,0) ) <-- this worked

When I applied it to my own needs, I ran into trouble because I was swapping the two matches (basically, trying to make the first match on the horizontal axis and the second on the vertical). Once I found my error, it worked - thank you.

Now to move it past the testing phase and put it into real world!

[Alex]

Sorry about that, glad you found the issue. I probably had the right formula (since I pasted from Excel) but described it wrong.

[Kevy Baby]

Here's another lookup challenge that I am not sure how to handle.

I get USPS and UPS Zone Charts in a format similar to this:

Zip Code Zone
005-212 8
214-268 8
270-324 8
325 7
326-342 8
344 8
346-347 8
349 8
350-352 7
354-359 7
360-363 8
If some of the Three Digit Zips are shown in a range, is there a way to have Excel look in the range? How would I tell Excel that the Zone for 289 is 8?

[Ghoulish Delight]

My first notion is this (not an easy 1-cell solution unfortunately). The following code will return the two end points of the range (assuming the range is in cell A1:

Left(A1,FIND("-",A1)-1)
Right(A1,Find("-",A1)-1)

Those are text results, and the only way I've found so far to get a numeric value is to put those formulas in ohter cells (we'll use A2 and A3), then use =Value()

So then, for a value of 289 in Cell B1 you can do a =IF(VALUE(A2)<=B2,IF(B2<=VALUE(A3),1,0),0)

You'll get a 1 if it is in the range, 0 if not.

[Kevy Baby]

I came here to ask a question about merging multiple Excel spreadsheets, but I did a little more digging and found a plug in which does it (http://www.rondebruin.nl/merge.htm) AND learned all about the DATEVALUE function (http://office.microsoft.com/en-us/excel-help/convert-dates-stored-as-text-to-dates-HP001162867.aspx#BMdatevalue).

I love learning new things in Excel

[Betty]

I have a spreadsheet I got online and tweaked a bit for my husband to help him calculate overtime hours. Unfortunately it's not working right and now is not the time for this to be happening. ARGH! (It is SO one of those days - starting last night with the dead battery, continuing with today's flat tire and now this. While I won't go into details - the timing of this couldn't be worse.)

I'm hoping one of you excel-lent excel-lers will be able to look at it and go - add this and "ta-da" it works.

Here is a link to screen shots: https://picasaweb.google.com/110655013735398875945/DropBox?authkey=Gv1sRgCJHNsqmhrKD1Kg#

Or I could send the file if that's easier.

It should be showing overtime on saturday if M through S has 8 hours. It's not though.

[Alex]

I'm not entirely sure of the goal, so is this correct?

1. You enter hours worked on a day on Row 4. If this is 8 hours or less it automatically appears on row 5 as regular hours. If it is more than 8 hours the first 8 hours goes under regular pay and the rest goes under OT. (Not sure what DT is.)

2. Once the total hours in a week reaches 40 hours then all hours after that go to OT.

Is that correct?

[Moonliner]

I have a spreadsheet I got online and tweaked a bit for my husband to help him calculate overtime hours. Unfortunately it's not working right and now is not the time for this to be happening. ARGH! (It is SO one of those days - starting last night with the dead battery, continuing with today's flat tire and now this. While I won't go into details - the timing of this couldn't be worse.)

I'm hoping one of you excel-lent excel-lers will be able to look at it and go - add this and "ta-da" it works.

Here is a link to screen shots: https://picasaweb.google.com/110655013735398875945/DropBox?authkey=Gv1sRgCJHNsqmhrKD1Kg#

Or I could send the file if that's easier.

It should be showing overtime on saturday if M through S has 8 hours. It's not though.

For starters, Monday is in column 'B' but the formula starts in column 'C'. Update your SUM function. (Note 'SUM' appears twice in the first image.)

[Alex]

But if I'm reading the formula correct what you have is doing this logic:

1. Is the value entered for each day Monday (B4) through Sunday (H4) greater than zero?

RESPONSE: FALSE, Sunday (H4) has a value of 0.

2. Then is the sum of hours from Monday (B5) through Saturday (G5) less than 40?

RESPONSE: FALSE, you have 8 hours in each of those six days which equals 48 hours.

Therefore: Value of H5 is zero.

Not sure again what you're trying to get to since if you flipped around the first false into a TRUE the result is also zero. And if you flipped around the second false into a TRUE you'd get the least of 8, -8, and 0, which would be -8.

[Alex]

Oh there were two pictures.

Ok, in cell G5 the problem is that you have the SUM going C4:F4 instead of B4:F4. With what you have you end up asking "what is the minimum of 8, 8, and 8).

Once you fix that sum you're going to get 0 in G5.

[Betty]

I'm not entirely sure of the goal, so is this correct?

1. You enter hours worked on a day on Row 4. If this is 8 hours or less it automatically appears on row 5 as regular hours. If it is more than 8 hours the first 8 hours goes under regular pay and the rest goes under OT. (Not sure what DT is.)

2. Once the total hours in a week reaches 40 hours then all hours after that go to OT.

Is that correct?

DT is double time.

This spreadsheet this trying to calculate how many regular hours, overtime and doubletime hours in a pay period (which varies from month to month with payday on the 10th and 25th.)

The rules are:
One and one-half times the employee's regular rate of pay for all hours worked in excess of eight hours up to and including 12 hours in any workday, and for the first eight hours worked on the seventh consecutive day of work in a workweek; and
Double the employee's regular rate of pay for all hours worked in excess of 12 hours in any workday and for all hours worked in excess of eight on the seventh consecutive day of work in a workweek.

[Betty]

Oh there were two pictures.

Ok, in cell G5 the problem is that you have the SUM going C4:F4 instead of B4:F4. With what you have you end up asking "what is the minimum of 8, 8, and 8).

Once you fix that sum you're going to get 0 in G5.

Cell G5 is =IF(SUM(C5:F5)<40,MIN(MIN(8,40-SUM(C5:F5)),G4),0)

I'm not seeing C4:F4 - or am I missing something.

[Betty]

He wants to be able to type in the total hours worked each day (the red number cells are the only part he will fill in) and come up with a total for the pay period to compare against some other software they use that (seemingly) randomly does weird things to hour calculations.

A lot of help I am giving him something that doesn't work. oops.

[Moonliner]

Cell G5 is =IF(SUM(C5:F5)<40,MIN(MIN(8,40-SUM(C5:F5)),G4),0)

I'm not seeing C4:F4 - or am I missing something.

Cell G5 should read: is =IF(SUM(B5:F5)<40,MIN(MIN(8,40-SUM(B5:F5)),G4),0)

What is the formula for cell G6 and G7?

[Alex]

So the seven consecutive day rule only applies to calendar week? Working 8 days in a row from Wednesday through the next Wednesday would not?

[Betty]

Here's a link to the file: http://hemetgraphics.com/ot.zip

[Betty]

So the seven consecutive day rule only applies to calendar week? Working 8 days in a row from Wednesday through the next Wednesday would not?

That's a good question - let me verify. (I'm pretty sure the wording "work week" doesn't mean any old days.)

[Moonliner]

That's a good question - let me verify. (I'm pretty sure the wording "work week" doesn't mean any old days.)

With the one change made to cell G5 it seems to work fairly well.

[Betty]

Cell G5 should read: is =IF(SUM(B5:F5)<40,MIN(MIN(8,40-SUM(B5:F5)),G4),0)

What is the formula for cell G6 and G7?

There we go! Yay! :snap: :snap: :snap:

[Alex]

Though the rules given above never mention any trigger of overtime at 40 hours so I'm not sure why that is appearing in the formulas.

[Betty]

I'm still waiting for verification on the question but I'm 99% sure that it's working the way it should.

Thank you so much! really and truly. On a day where I've been unusually busy, had numerous car issues and a few other things pop up, you have helped tremendously.

[Moonliner]

I'm still waiting for verification on the question but I'm 99% sure that it's working the way it should.

Thank you so much! really and truly. On a day where I've been unusually busy, had numerous car issues and a few other things pop up, you have helped tremendously.

Glad to help.

[Moonliner]

Though the rules given above never mention any trigger of overtime at 40 hours so I'm not sure why that is appearing in the formulas.

That is a good question, I assume it's because five 8 hour days = 40 hours.

[Alex]

I can't see the spreadsheet from work but would be interested in seeing the formulas in the other cells.

But if it is working, then good enough.

[Moonliner]

There we go! Yay! :snap: :snap: :snap:

You probably already figured this out, but you need to make the same change to cells G13 and G21

[Betty]

You probably already figured this out, but you need to make the same change to cells G13 and G21

Thanks. :)

[Moonliner]

I was assigned a "high priority" task today. A "You are to work on nothing but this for the new few days" thing.

The task is to match up some data in a couple of overly large excel spreadsheets.

So I imported the data into SQL Server and created a couple of cross table queries to pull the needed data and drop it back into excel. Total time: ~45min.

So my question is, what's good on Netflix? I have a few days to kill.....

[Kevy Baby]

I have a spreadsheet that can only be downloaded as a tab delimited file. One of the fields is a date/time field that comes in as text in this format:

26-Jun-2014 10:11:13 AM PDT

Obviously I cannot just format it as a date. The shortest way I have found to get this converted to a date is (assuming the source data is in column A):

Enter this function in column B: =LEFT(A2,11) (the date portion is always 11 characters)
Enter this function in column C: =DATEVALUE(B2)
Copy then Paste Value of Column C
Yes, I will combine the two functions into one: I left them separate to make the example easier to follow. Also, there are several hundred rows of data and the formula will need to be filled down.

So
1. Is there a simpler way to do this in Excel? I need to be able to sort/group/etc. the data by dates and other data in the spreadsheet (daily summary of debits and credits by company name in a monthly report)
2. Having never worked in Macros (but always willing to learn something new) how difficult is this to script so that the CSV file can be automatically processed once per month? I want to be able to hand off to our accounting department to handle (I could manually do this every month myself in probably less time than I would spend writing and testing said macro, but I would rather automate and give to someone else to handle).

And I cannot change the report as it is provided: I gotta work with what I have.

[Moonliner]

The basic formula looks good: =DATEVALUE(LEFT(A1,11))

I would read Tab delimited data into "sheet2" and then use the formula on "sheet1" to format and display it the way the users need.

Does that help at all?

[Alex]

This will result in losing the time portion of the cell but doesn't require any formulas and would be a very simple macro to record.

If so, then you can do this and not mess with any formulas. Assumes the timestamp is in column A. Easy macro to record if you want.

1. Select Column A
2. Set Number format of Column A to whatever format you want to see the date expressed in. For example purposes pick 3/14/01.
3. With Column A selected (to avoid collateral damage in other columns) open the Search and Replace dialog (Ctrl-H).
4. In the "Find what" box enter " *" without the quotes (that is: <space>*). This will tell Excel to find the first instance of a space in the cell and then replace that space and everything to the right of it with what comes next.
5. In the "Replace with" box enter nothing at all.
6. Click "Replace all" button.

This will take a cell that says

26-Jun-2014 10:11:13 AM PDT

and essentially truncate the value at the first space. This truncated form is one that the date formatting recognizes and so you'll now see

6/26/14

You can then sort, pivot, use in formulas, etc., however you want.

[Alex]

Oh, and if you want to keep the time part of the time stamp around, before you do the above, copy Column A into a blank column (let's say Column B).

Do the above to Column A. Then in Column B things are a bit trickier to get rid of the date (because for some stupid reason when you want to trim tot he left the expression match starts from the right so just finding spaces will match to the one before PDT).

But these could clear out the date part if you need:

If all timestamps are from 1/1/2000 and later:

Find what: "*20?? " without quotes: *20??<space>

This essentially says "find the pattern 2 0 any character any character space and then trim that pattern and everything to the left of it."

If all timestamps are from the 1900s and 2000s:

Find what: "*20?? " without quotes: *20??<space>

then repeat with:

Find what: "*19?? " without quotes: *19??<space>

[Alex]

Kevy better be dead to explain him not coming back to thank us vociferously for being such Excel badasses.

Or just let us know that it filled his need.

[RStar]

Or he just saw boobs and forgot...

[Kevy Baby]

Oh Geez: I'm not sure how, but I missed your responses.

I will check these out tomorrow.

And thank you for your responses!!!

[RStar]

Well it's about time! ;)

Good luck! :snap: