Excel Question #46 - Page 4

Moonliner · 02-04-2009, 07:21 AM

Quote:

Originally Posted by 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)

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

Kevy Baby · 04-02-2009, 03:35 PM

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 · 04-02-2009, 03:58 PM

Quote:

Originally Posted by 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!

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 · 04-02-2009, 04:05 PM

Quote:

Originally Posted by Moonliner

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 · 04-02-2009, 04:43 PM

Quote:

Originally Posted by Kevy Baby

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 · 04-04-2009, 10:46 AM

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 · 04-04-2009, 05:39 PM

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 · 01-06-2010, 07:05 PM

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 · 01-06-2010, 07:44 PM

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 · 01-06-2010, 07:49 PM

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

04-02-2009, 03:35 PM	#32
Kevy Baby Chowder Head Join Date: Jan 2005 Location: Yes Posts: 18,500	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! __________________ The thing about quotes on the internet is that you cannot verify their validity. - Abraham Lincoln
	Submit to Quotes

04-04-2009, 10:46 AM	#36
Drince88 Senior Member Join Date: May 2005 Posts: 481	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.
	Submit to Quotes

04-04-2009, 05:39 PM	#37
Kevy Baby Chowder Head Join Date: Jan 2005 Location: Yes Posts: 18,500	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. __________________ The thing about quotes on the internet is that you cannot verify their validity. - Abraham Lincoln
	Submit to Quotes

01-06-2010, 07:05 PM	#38
Kevy Baby Chowder Head Join Date: Jan 2005 Location: Yes Posts: 18,500	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? __________________ The thing about quotes on the internet is that you cannot verify their validity. - Abraham Lincoln
	Submit to Quotes

01-06-2010, 07:44 PM	#39
Alex . Join Date: Feb 2005 Posts: 13,354	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.
	Submit to Quotes

01-06-2010, 07:49 PM	#40
Alex . Join Date: Feb 2005 Posts: 13,354	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).
	Submit to Quotes