Ask the EXCEL guy ...

Miscellaneous and Personal Stuff I Must Share
61

[QUOTE=ComeToTheDarkSideWeHaveCookies]
My apologies if this may have been answered already.

I’ve been trying to figure out how to use Excel to get around some shortcomings in an SQL querey I have to use to run some reports.

Basically, the SQL querey results include both missing AND redundant data.

For the sake of example, Column A includes Account Numbers, Column B is an “important account factor”, and Column C includes Usernames (belonging to the acct # in Column A).

Acct #,Acct factor,Username
1,b,jon
1,b,sam
1,null,sam
1,null,ben
1,null,tim
2,c,val
2,null,ted
2,c,ted
3,null,ned
3,null,ned
3,null,ken
3,null,liv

What I want the endpoint to be is the basically a lowest common denominator of Column A and Column B. I process Column C with a seperate function, but I need them for analytical context of Column A and B. Not all account numbers have account factors (in the absence of a factor in any “Acct factor” field for any given “Acct #”, null is the valid state). I need to display the appropriate “Acct factor” state for each account number.

This is what I’d want to get out of the above data when all is said and done:

Acct #,Acct factor,Username
1,b,sam (or jon, ben, or tim, but only one)
2,c,val (or ted, but not both)
3,null,ned (or ken, or live, but only one)

This has been driving me CRAZY.

I hope you can help me.

Thanks in advance!
[/QUOTE]

So you thought you’d drive me crazy as well … hey, that’s what I’m here for. But I don’t understand what your end result is to be, so before I go further, let me see if I got it.

It seems that the rules for a given Acct # are

  1. if some of the Acct factors are not null, list one of them with a corresponding name.

  2. if all of the Acct factors are null, list one of them with a corresponding name.

Is this correct?

Do we ever output more than one line for each account factor?

In your example, for Account 1, jon and sam have “b” and “null”, while tim and ben just have “null”. Why is the answer “b” and any of the four name?

Does an Acct # ever have more than one factor? (two if we count null)

Get back to me on this one and we’ll give it a shot.

Morning to all (here at least),

w.

62

[QUOTE=intention]
So you thought you’d drive me crazy as well … hey, that’s what I’m here for. But I don’t understand what your end result is to be, so before I go further, let me see if I got it.

It seems that the rules for a given Acct # are

  1. if some of the Acct factors are not null, list one of them with a corresponding name.

  2. if all of the Acct factors are null, list one of them with a corresponding name.

Is this correct?
[/quote]

Close.

Rule 2 is correct.

Rule 1 should be: if some of the Acct factors are null, list one that is not null with a corresponding name

I was trying to phrase my example to avoid bogging down too far in specifics, but it doesn’t look like I can help it.

The usernames in my sample indicate a subset of users that have been inactivated for policy enforcement reasons, and the “Acct factor” is a billing factor that is only present on accounts that use one particular method of payment. Let’s call that billing method “CrazyCa$h”, and the Acct factor values are dynamic (but not unique) “CrazyCa$h numbers”. Acct factor=null indicates that the corresponding Acct # uses a method of payment is other than “CrazyCa$h”. There are multiple other methods, but I don’t care about them. Null is sufficient.

An Acct # can only have one method of payment at any given time, and I’m analyzing the payment methods used on accounts that have policy enforcement problems on at least on of their users. Because of bugs in the SQL that provide me my raw data, I get duplicate lines for some usernames, and some of those duplicates are missing the “CrazyCa$h” Acct factor data that should be there. These problem lines appear as a null Acct factor line, but they really aren’t.

I can find these problem Acct #s manually because the buggy SQL will always give me at least one line that does contain the Acct factor data for any given Acct # where the Acct factor is not null.

Not for the sake of this particular report. I’m only interested in eventually calculating the overall percentage of Acct #s using “CrazyCa$h” compared to the Acct#s using all other methods of payment which are truly Acct factor=null. I am also interested in each dynamic (but not unique) “CrazyCa$h number”. They become unique once you correlate them to the unique Acct #s.

Gah…

I fear that I am so going to hell for trying to drag someone else into this headache.

I wish I had something like the quick sand survival technique to fall back on. When stuck in a data analysis quagmire, don’t struggle, take deep slow breaths, and try and float on the surface of the spreadsheet untill someone happens by with rope to pull you out.

63

[QUOTE=ComeToTheDarkSideWeHaveCookies]
Close.

Rule 2 is correct.

Rule 1 should be: if some of the Acct factors are null, list one that is not null with a corresponding name

I was trying to phrase my example to avoid bogging down too far in specifics, but it doesn’t look like I can help it.

The usernames in my sample indicate a subset of users that have been inactivated for policy enforcement reasons, and the “Acct factor” is a billing factor that is only present on accounts that use one particular method of payment. Let’s call that billing method “CrazyCa$h”, and the Acct factor values are dynamic (but not unique) “CrazyCa$h numbers”. Acct factor=null indicates that the corresponding Acct # uses a method of payment is other than “CrazyCa$h”. There are multiple other methods, but I don’t care about them. Null is sufficient.

An Acct # can only have one method of payment at any given time, and I’m analyzing the payment methods used on accounts that have policy enforcement problems on at least on of their users. Because of bugs in the SQL that provide me my raw data, I get duplicate lines for some usernames, and some of those duplicates are missing the “CrazyCa$h” Acct factor data that should be there. These problem lines appear as a null Acct factor line, but they really aren’t.

I can find these problem Acct #s manually because the buggy SQL will always give me at least one line that does contain the Acct factor data for any given Acct # where the Acct factor is not null.

Not for the sake of this particular report. I’m only interested in eventually calculating the overall percentage of Acct #s using “CrazyCa$h” compared to the Acct#s using all other methods of payment which are truly Acct factor=null. I am also interested in each dynamic (but not unique) “CrazyCa$h number”. They become unique once you correlate them to the unique Acct #s.

Gah…

I fear that I am so going to hell for trying to drag someone else into this headache.

I wish I had something like the quick sand survival technique to fall back on. When stuck in a data analysis quagmire, don’t struggle, take deep slow breaths, and try and float on the surface of the spreadsheet untill someone happens by with rope to pull you out.
[/QUOTE]

Cookies, for the EXCEL guy with a rope, this is not a headache, it’s a challenge. You’ll have to earn your ticket to hell elsewhere, I fear.

The answer is a bit complex, so I’ve put it on a spreadsheet here for you to download and experiment with. Let me know if you have further questions.

w.

64

[QUOTE=intention]
Right-click (control-click on the Mac) on the chart, and choose “Move”.

w.
[/QUOTE]

The version of Excel I have at the office works differently, but I was able to do it.

Thanks!

65

[QUOTE=intention]
Cookies, for the EXCEL guy with a rope, this is not a headache, it’s a challenge. You’ll have to earn your ticket to hell elsewhere, I fear.

The answer is a bit complex, so I’ve put it on a spreadsheet here for you to download and experiment with. Let me know if you have further questions.

w.
[/QUOTE]

You rock! Thank you! This makes my life soooo much easier.

66

[QUOTE=intention]
Whoa, stop the presses. I just found this function for counting unique values:


=SUM(IF(H13:H19="",0,1/COUNTIF(H13:H19,H13:H19)))

This works provided you don’t want to count blank cells. Replace “H13:H19” with the range of interest in all three places in the formula.

w.
[/QUOTE]

You rock man. Thanks!

67

[QUOTE=intention]
SWEET! Not obvious to me. Always more to learn …

w.
[/QUOTE]

ALL RIGHT! :smiley:

I have secretly hoped that this is a cool idea, it certainly was to me when I came up with it. But I figured there is nothing new under the MS sun, and everyone else probably knew all about it. It is nice to see that at least one person who knows FAR more about excel than I was impressed.
Whew.

68

You are all very welcome for your comments, I’m glad I was able to assist you. Anyone else with EXCEL questions is most welcome to ask them.

w.

69

Thank you for this excel lent thread. I find Excel charts to be extremely aggravating. What I’m having trouble with is XY charts.

Here’s the issue. I want to plot 4 lines of data. Each line represents the deterioration rate from state 1 (best) to state 5 (worst). Management wants to see the data going downhill, not uphill as would be if you simply made the chart the easy way. So on the y axis, I want state 1 at the top and state 5 at the bottom. On the x axis is number of years. So one line of data might be this:
state 1 __0 years
state 2 __4 years
state 3 __11 years
state 4 __18 years
state 5 __40 years

Now imagine 4 columns of data so the data would appear first with y axis values then the corresponding x axis values

y __x1 __x2 __x3 __x4
1 __0 __ 0 __0 __0
2 __4 __3 __7 __7
3 __11 __8 __12 __15
4 __18 __12 __17 __20
5 __40 __42 __48 __70

I have torn my hair out trying to plot such data and any advice you could give would be appreciated.
ALSO- is there any way to create your legends without having to tell excel that series 1 label is cell a1 and series 2 label is cell a2, etc? Seems like you should be able to tell it series labels 1 thru 5 are cells a1 thru a5

70

[QUOTE=BobLibDem]

ALSO- is there any way to create your legends without having to tell excel that series 1 label is cell a1 and series 2 label is cell a2, etc? Seems like you should be able to tell it series labels 1 thru 5 are cells a1 thru a5
[/QUOTE]

I can’t say I understand the problem in the first half of your question, but in your sample table above, if your table started at A1 for example where you have a y but you excluded the y (left the cell blank, this is the key) then you should select A1 to E6 and then choose an XY plot. It should automatically create series labels x1, x2, x3, x4. If the series are in the rows versus the columns then you just select rows versus columns when creting the chart that should be the second screen that comes up.

71

BLD, I think I understand the first part of your question. In your table you need to reverse the order of your x-values so they read 5,4,3,2,1 going down. ALSO, you must change them to text, so instead of 5 you should just label it '5. That should give you the downhill effect you are looking for.

72

[QUOTE=Gangster Octopus]
BLD, I think I understand the first part of your question. In your table you need to reverse the order of your x-values so they read 5,4,3,2,1 going down. ALSO, you must change them to text, so instead of 5 you should just label it '5. That should give you the downhill effect you are looking for.
[/QUOTE]

I’m about done for the day but I shall try what you say. Thanks for the help.

73

[QUOTE=BobLibDem]
Thank you for this excel lent thread. I find Excel charts to be extremely aggravating. What I’m having trouble with is XY charts.

Here’s the issue. I want to plot 4 lines of data. Each line represents the deterioration rate from state 1 (best) to state 5 (worst). Management wants to see the data going downhill, not uphill as would be if you simply made the chart the easy way. So on the y axis, I want state 1 at the top and state 5 at the bottom. On the x axis is number of years. So one line of data might be this:
state 1 __0 years
state 2 __4 years
state 3 __11 years
state 4 __18 years
state 5 __40 years

Now imagine 4 columns of data so the data would appear first with y axis values then the corresponding x axis values

y __x1 __x2 __x3 __x4
1 __0 __ 0 __0 __0
2 __4 __3 __7 __7
3 __11 __8 __12 __15
4 __18 __12 __17 __20
5 __40 __42 __48 __70

I have torn my hair out trying to plot such data and any advice you could give would be appreciated.
ALSO- is there any way to create your legends without having to tell excel that series 1 label is cell a1 and series 2 label is cell a2, etc? Seems like you should be able to tell it series labels 1 thru 5 are cells a1 thru a5
[/QUOTE]

What Gangster Octopus said … if you select the labels along with the values before creating the chart, you should get the labels automatically.

Regarding the order of the data (high to low or low to high), that can be changed after the chart is created. What you want to do is reverse the axis. This is done as follows:

  1. double-click the axis (vertical or horizontal) to bring up the “Format Axis” dialog for that axis

  2. select the “Series” tab of the “Format Axis” dialog.

  3. select the “Values in reverse order” checkbox,

  4. click “OK”.

If you still have questions … we still have answers. Thanks, Gangster.

w.

PS - it also occurs to me that the problem may be that EXCEL is plotting the data in rows, when you want it in columns. If so,

  1. Select the graph

  2. Select the menu item “Chart : Source Data”

  3. Change from “Rows” to “Columns”, or vice versa.

74

I have an excel spreadsheet with a macro that runs a particular form. I would like the form to open automatically when I open the spreadsheet.

75

[QUOTE=Zyada]
I have an excel spreadsheet with a macro that runs a particular form. I would like the form to open automatically when I open the spreadsheet.
[/QUOTE]

Not sure if this is the best solution, but if you rename the macro Auto_Open that should work. If you wish to keep the original macro then create a new macro called Auto_Open that calls the original macro. Something like

Sub Auto_Open()
test_macro
End Sub

Sub test_macro()
MsgBox (“Hello”)
End Sub

76

Is there any way that one can save a wide spreadsheet in plain text without introducing tab spaces, quotes or other extraneous delimiters? The only format that allows for saving to plain text is .prn. Unfortunately, it only saves up to 240 characters per line and I need to go beyond this limit with many of the applications for which I use Excel. How can I overcome this limitation?

77

[QUOTE=dwc1970]
Is there any way that one can save a wide spreadsheet in plain text without introducing tab spaces, quotes or other extraneous delimiters? The only format that allows for saving to plain text is .prn. Unfortunately, it only saves up to 240 characters per line and I need to go beyond this limit with many of the applications for which I use Excel. How can I overcome this limitation?
[/QUOTE]

I’m not clear about your comment about “tab space, quotes, or other delimiters”. To save a spreadsheet, the format MUST contain some delimiter to tell EXCEL where one cell stops and another one starts when the data is read back into EXCEL.

Perhaps a sample of the data you are trying to save would be helpful. It sounds like you have text in a row of cells, which you want to export as a single line of text. If so, you can combine them using the “&” operator:

=A1 & A2 & A3 & A4 & …

I do this all the time, so I’ve written a VBA function called COMBINE that takes a range of cells and combines them as shown above.

Here is the function, copy it into your “Personal Macro Workbook”. Everything after an apostrophe (') is a comment that is ignored by EXCEL.


Function combine(r As Range, Optional s As String = " ")
    'r is the range of cells to combine, s is the separator string. By default, this is " ", it can be set to any text. Use "" as the value for no separation.
    'the function uses the formatting of each cell to format the resulting string
    If r.Cells(1) = "" Then 'deal with first cell
        combine = ""
    Else
        If IsNumeric(r.Cells(1)) Then
            combine = Application.text(r.Cells(1), r.Cells(1).NumberFormat)
        Else
            combine = r.Cells(1).Value
        End If
    End If
    For n = 2 To r.Cells.Count 'deal with following cells, skipping hidden rows and columns
        If r.Cells(n).Value <> "" And r.Cells(n).EntireRow.RowHeight > 0 And r.Cells(n).EntireColumn.ColumnWidth > 0 Then
            If IsNumeric(r.Cells(n)) Then
                combine = combine & s & Application.text(r.Cells(n), r.Cells(n).NumberFormat)
            Else
                combine = combine & s & r.Cells(n).Value

            End If
        End If
    Next
End Function

EXAMPLES OF USING THE “COMBINE” FUNCTION

If A1 = “Time”, B1 contains “is”, C1 contains the time 4:15

=COMBINE(A1:C1)

yields “Time is 4:15”

=COMBINE(A1:C1,“”)

yields “Timeis4:15”

=COMBINE(A1:C1,", - ")

yields “Time, - is, - 4:15”

HTH, if not, let me know. We don’t give up until the problem is solved.

w.

PS - “We” is not the royal we, it includes TomCat and Gangster Octopus, the killer death commando EXCEL squad … soon to be a major motion picture.

78

I don’t have a “How do I do this” question. Mine is more of a “Why the hell is it like this?!” question.

I create row1 to be my headings, for whatever.
I select row 1 and i want those headings frozen so i can scroll thru the columns and always know what the headings are. So i go to Window> Freeze Panes. Why does it freeze everything from rows 1 to 23?

Why, when i select row 2, Window> Freeze Panes. that it actually works right? And it’s been with every version of Excel i ever used and yes, i get all the service packs but i can’t understand the logic behind this…

79

[QUOTE=intention]
I’m not clear about your comment about “tab space, quotes, or other delimiters”. To save a spreadsheet, the format MUST contain some delimiter to tell EXCEL where one cell stops and another one starts when the data is read back into EXCEL.

Perhaps a sample of the data you are trying to save would be helpful. It sounds like you have text in a row of cells, which you want to export as a single line of text.
[/QUOTE]

I should have made it clear that my intention is not to make the text readable back into Excel, but that the output is an end-product, or part of a process in which I use the data for creating HTML code, particularly tables (I like to do it my own way rather than rely on Excel’s export to HTML). Anyway, here is output as it appears with all the different plain text options:

Tab characters are represented by “->”.
Text (tab delimited) (.txt):



Cell A1 -> Cell B1 -> Cell C1
Cell A2 -> Cell B2 -> Cell C2
Cell A3 -> Cell B3 -> Cell C3

Unicode Text (.txt):



Cell A1 -> Cell B1 -> Cell A1
Cell A2 -> Cell B2 -> Cell C2
Cell A3 -> Cell B3 -> Cell C3

Space delimited (.prn): (the format I like to use since this does not put tabs or commas in my data)



Cell A1 Cell B1 Cell C1
Cell A2 Cell B2 Cell C2
Cell A3 Cell B3 Cell C3

Text (Macintosh) (.txt):



Cell A1 -> Cell B1 -> Cell A1
Cell A2 -> Cell B2 -> Cell C2
Cell A3 -> Cell B3 -> Cell C3

Text (MS-DOS) (.txt):



Cell A1 -> Cell B1 -> Cell A1
Cell A2 -> Cell B2 -> Cell C2
Cell A3 -> Cell B3 -> Cell C3

CSV (Comma delimited (.csv):



Cell A1,Cell B1,Cell C1
Cell A2,Cell B2,Cell C2
Cell A3,Cell B3,Cell C3

Here is what happens when I have a single row whose contents exceeds 240 characters, using the third paragraph of the Gettysburg Address as an example:



Cell A1 Cell B1 But, in a larger sense, we can not dedicate—we can not consecrate—we can not hallow—this ground. The brave men, living and dead, who struggled here, have consecrated it, far above our poor power to add or detract. The world

will little note, nor long remember what we say here, but it can never forget what they did here. It is for us the living, rather, to be dedicated here to the unfinished work which they who fought here have thus far so nobly advanced. It is

rather for us to be here dedicated to the great task remaining before us — that from these honored dead we take increased devotion to that cause for which they gave the last full measure of devotion — that we here highly resolve that these

dead shall not have died in vain — that this nation, under God, shall have a new birth of freedom — and that government of the people, by the people, for the people, shall not perish from the earth.

Ideally, it would be nice to have all the text in the same line (for my purposes), but instead it is broken into separate lines.

Here’s another example. Let’s say I put consecutive numbers in a single row, starting with 101 and going to 150. Here is the output in .prn format:



     101     102     103     104     105     106     107     108     109     110     111     112     113     114     115     116     117     118     119     120     121     122     123     124     125     126     127     128     129     130

     131     132     133     134     135     136     137     138     139     140     141     142     143     144     145     146     147     148     149     150

For my purposes, it would be a lot easier if I could output the data to a single line and not have line breaks occur after every 240 characters.

80

[QUOTE=SlickRoenick]
I don’t have a “How do I do this” question. Mine is more of a “Why the hell is it like this?!” question.

I create row1 to be my headings, for whatever.
I select row 1 and i want those headings frozen so i can scroll thru the columns and always know what the headings are. So i go to Window> Freeze Panes. Why does it freeze everything from rows 1 to 23?

Why, when i select row 2, Window> Freeze Panes. that it actually works right? And it’s been with every version of Excel i ever used and yes, i get all the service packs but i can’t understand the logic behind this…
[/QUOTE]

Slick, thanks for writing.

As you no doubt know …

If you select a row, the frozen section is created above the row.

If you select a column, the frozen section is created to the left of the column.

If you select a range, the frozen section is created above and to the left of the range.

You ask what is the logic behind this? Well … we’d have to first determine if there is logic behind it, then look to see what it is. I never once thought about it, but your way makes more sense … which is probably why, as programmers, they did it the other way. Nobody ever accused programmers of making any excess sense … and as a programmer myself, I should know.

My best to you,

w.