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
if some of the Acct factors are not null, list one of them with a corresponding name.
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.
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.
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.
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
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
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.
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.
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:
double-click the axis (vertical or horizontal) to bring up the “Format Axis” dialog for that axis
select the “Series” tab of the “Format Axis” dialog.
select the “Values in reverse order” checkbox,
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,
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
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?
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.
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…
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):
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:
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.
