Posts by macromike

Re: Show the max value for each group of similar items

[TABLE="width: 612"]

[TD="colspan: 2"]DATA SET (Unsorted)[/TD]

row

PLAYER

SCORE

TEAM

PARK

DATE

1

Matt

69

Weaver

Wrigley

2002

2

Matt

74

Weaver

Wrigley

2004

3

Sam

80

Varna

Fenway

2008

4

Sam

65

Varna

Fenway

2007

5

Barbara

76

BlueJays

Ballpark

2013

6

Sam

82

Varna

Fenway

2009

7

Sam

81

Varna

Fenway

2011

8

Barbara

75

BlueJays

Ballpark

2014

9

Barbara

75

BlueJays

Ballpark

2014

10

Matt

78

Weaver

Wrigley

2003

[TD="colspan: 2"]What I want….[/TD]

[TD="colspan: 8"]Only the MAX Score for each player shown, in descending order, along with associated row data[/TD]

row

PLAYER

SCORE

TEAM

PARK

DATE

6

Sam

82

Varna

Fenway

2009

10

Matt

78

Weaver

Wrigley

2003

5

Barbara

76

BlueJays

Ballpark

2013

[/TABLE]

Re: Show the max value for each group of similar items

Also I need the information from other columns in the same row as the MAX to display.

Example: for each MAX displayed as a SUBTOTAL I also want the player's "TEAM", "BALLPARK", and "DATE" from the same row that accompanied the MAX to show.

I can't figure out how to to display this when using the SUBTOTAL and GROUP features.

Re: Show the max value for each group of similar items

I have some luck by doing this:
1) Use SUBTOTAL feature, then have it display the MAX subtotal below each group
2) Use GROUP feature, then have to collapse each group to show only the MAX (this takes time to do one by one, as I have hundreds of players)
3) Use SORT feature, to display the MAX values from largest to smallest.

Is there a quicker solution?

Re: Show the max value for each group of similar items

I don't want to necessarily use a pivot table, unless its the only solution. I want this easily used by others.

1) How do I show the maximum value for each group of similar items below?
2) Furthermore how can I quickly reorder the groups based on their max values?

Example: The full data set

[TABLE="width: 127"]

PLAYER

SCORE

Sam

[TD="align: right"]82[/TD]

Sam

[TD="align: right"]81[/TD]

Sam

[TD="align: right"]80[/TD]

Sam

[TD="align: right"]65[/TD]

Barbara

[TD="align: right"]76[/TD]

Barbara

[TD="align: right"]75[/TD]

Matt

[TD="align: right"]78[/TD]

Matt

[TD="align: right"]74[/TD]

Matt

[TD="align: right"]69[/TD]

[/TABLE]

Example: What I want it to display, after doing whatever Filter and Sort operation.

[TABLE="width: 170"]

PLAYER

HIGH SCORE

Sam

[TD="align: right"]82[/TD]

Matt

[TD="align: right"]78[/TD]

Barbara

[TD="align: right"]76[/TD]

[/TABLE]

Re: Intersecting Points Of Multiple Linear Plots

ds2007,
I'm looking at your data, and can not quite figure out how it is arranged. It looks like you have the Series for each car line. Then you have each day on th X-axis, then the value on the Y-axis. It looks like it is a Line chart,
Please provide a better explanation and maybe i can help.
When I look at the purple line (I think it is for Olds that peaks at 180 inbetween the 1/8/2008 and 1/9/2008 timeline), yet I can find no such point in your data.

Re: Scatter Plot

See solutions within this thread:
Attach labels with names to the points in a scatter plot

I believe when you say Company X,Y,Z that you mean Company 1,2,3. Let's not confuse X,Y with the XY in XY Scatter plots.

There is an elegant solution posted by Badger101 that will grab names for your points from a third column in your spreadsheet and put them on your XY scatter plot. However, you didn't want a visual basic solution.

One non-visual basic solution that I could come up for this problem is to:
1) show the labels on your XY scatter plot - you can show the X or Y value or both
2) click on each of these labels and edit them, by typing in the company name that you want.

Another non visual basic solution
1) You could draw textboxes on the plot using the drawing toolbar, near each point
2) type in the name of the company into the textboxes

Re: Garbled Left Column In

I'm not sure what you mean? I see Text - alot of it - in the left column. Each begins with the following string:
<a href="http://www.anrdoezrs.net/click-1451623-5463217?loc=http://wantitnow-search-desc.ebay.com/ws/search/WantItNowSearch?satitle= then followed by the individual item you were looking up

To reduce these common string in the left column, you could do the following:
Under EDIT, go to REPLACE. Find what:= above and Replace with: "" This is just empty spaces. Select Replace All This will cut perhaps reduce the garble.

Also is your left column wide enough with Word Wrap enabled to see this stuff?

When you OPEN the .csv file using Excel, does the Text Import Wizard automatically launch? If so, is commas being selected as delimiters? You could try changing some of the options under delimiters. Check "treat consectutive delimiters as one" or even check Tabs, or Spaces as delimiters to see if it opens your .csv file differently to give you the results that you want.

Format According to Max Decimal Place in Range

Here is a small attachment with sample data and an updated version of the macro code (renamed variables, added comments).

Format According to Max Decimal Place in Range

OK, I got carried away again, trying to build all the possible options I'd think someone would need into this code.

This code will:
(1) give user a choice if they want to find the max decimals as shown in the formula bar which could be more than shown on the screen, or find the max decimals as shown on the screen - rounded to fit the column width
(2) give user a choice between formatting all the columns in the range according to the same max decimal places or format each column individually
(3) give user the choice to display trailing zeros (which could indicate extra degrees of accuracy that don't exist when talking about a measurement) or display just empty placeholders. In both cases, the decimals will still be aligned.

Sub FormatAccordingtoMaxDecimalPlace()
'By macromike 11/30/2006
'This sub reformats a range of data according to the maximum decimal place
'used by any one of the values within that range.  - a subchoice can be to
'format each column by the highest decimal place in that column.  Additional
'options are to display trailing zeros or just placeholders.  In both cases, the 
'decimals will be aligned as a result.


'Excel usually only displays a maximum of 14 decimal places on the screen
'and in the formula bar.




'Step 1: Enter the limits for your range data below
'Could undim these statements below and hard-code them
'FirstColumn = 1
'LastColumn = 4
'FirstRow = 1
'LastRow = 30


'Set up to allow input of the range by user
FirstColumn = CInt(InputBox("Enter number of FIRST COLUMN in your range", , 1))
LastColumn = CInt(InputBox("Enter number of LAST COLUMN in your range", , 4))
FirstRow = CInt(InputBox("Enter number of FIRST ROW in your range", , 1))
LastRow = CInt(InputBox("Enter number of LAST ROW in your range", , 30))




FindmaxInternalDecimals = MsgBox("Press YES to find the max decimals as shown in the formula bar which could be more than shown on the screen" & Chr(10) & _
"Press NO to find the max decimals as shown on the screen - rounded to fit the column width", vbYesNo)


FormatRangeorColumn = MsgBox("Press YES to display all columns to the same max decimal places" & Chr(10) & _
"Press NO to display each column according to its individual highest decimal place", vbYesNo)


ShowTrailingZeros = MsgBox("Press YES to display trailing zeros.  This could indicate extra degrees of accuracy that don't exist when talking about a measurement" & Chr(10) & _
"Press NO to just display empty spaces as placeholders" & Chr(10) & "Decimals will still aligned with either selection", vbYesNo)


'Step 2a: determining the maxDecimalPlaces in the individual column
For columnx = FirstColumn To LastColumn
maxDecimalPlaces = 0  'resets this for each pass through loop


For rowx = FirstRow To LastRow
If FindmaxInternalDecimals = vbYes Then
 textx = Cells(rowx, columnx).Value
End If
If FindmaxInternalDecimals = vbNo Then
 textx = Cells(rowx, columnx).Text
End If


 DecimalLocation = InStr(1, textx, ".", vbTextCompare)
  If (DecimalLocation > 0) And (DecimalLocation) <> "" Then
  DecimalPlaces = Len(textx) - DecimalLocation
  If DecimalPlaces > maxDecimalPlaces Then
   maxDecimalPlaces = DecimalPlaces
  End If
 End If
Next


'Step 2b: reformating the individual column to the maxDecimalPlaces
If maxDecimalPlaces >= 1 Then
If ShowTrailingZeros = vbYes Then
DecimalFormat = "0.0"
For x = 2 To maxDecimalPlaces
 DecimalFormat = DecimalFormat & "0"
Next
End If
If ShowTrailingZeros = vbNo Then
DecimalFormat = "0.?"
For x = 2 To maxDecimalPlaces
 DecimalFormat = DecimalFormat & "?"
Next
End If
Range(Cells(FirstRow, columnx), Cells(LastRow, columnx)).Select
Selection.NumberFormat = DecimalFormat
Selection.Columns.AutoFit
End If


'Step 2c: This is gathering info for Step 3, if Step 3 is desired
If maxDecimalPlaces > AllmaxDecimalPlaces Then
   AllmaxDecimalPlaces = maxDecimalPlaces
End If


Next




'Step 3: This will reformat all the columns in the range to the same
'AllmaxDecimalPlaces.  Previously each column was formatted individually
'to its max number of decimal places
If FormatRangeorColumn = vbYes Then
If AllmaxDecimalPlaces >= 1 Then
If ShowTrailingZeros = vbYes Then
DecimalFormat = "0.0"
For x = 2 To AllmaxDecimalPlaces
 DecimalFormat = DecimalFormat & "0"
Next
End If
If ShowTrailingZeros = vbNo Then
DecimalFormat = "0.?"
For x = 2 To AllmaxDecimalPlaces
 DecimalFormat = DecimalFormat & "?"
Next
End If
Range(Cells(FirstRow, FirstColumn), Cells(LastRow, LastColumn)).Select
Selection.NumberFormat = DecimalFormat
Selection.Columns.AutoFit
End If
End If
Cells(FirstRow, FirstColumn).Select  'just return to first cell in range
     
UserSatisfied = MsgBox("Press YES - if satisfied with results" & Chr(10) & _
"Press NO - if want columns kept in General Format", vbYesNo)
If UserSatisfied = vbNo Then
Range(Cells(FirstRow, FirstColumn), Cells(LastRow, LastColumn)).Select
Selection.NumberFormat = General
Selection.Columns.AutoFit
End If
Cells(FirstRow, FirstColumn).Select  'just return to first cell in range


End Sub

Re: Consistent Decimal Places

Ah YES - I've found the answer
There are some truly genius people on this forum, including Derk - the OzMVP Professor. I found this post from Sept 2003 while running a search of "align decimal"
http://www.ozgrid.com/forum/sh…6&highlight=align+decimal

The solution is to change where we put "0.0" to "0.?" as a format.
The ? mark acts as a placeholder and aligns the decimals for you

From Excel's help, that I found in the posting by Derk
· # displays only significant digits; does not display insignificant zeros.
· 0 (zero) displays insignificant zeros if a number has fewer digits than there
are zeros in the format.
· ? adds spaces for insignificant zeros on either side of the decimal point, so that decimal points align. You can also use this symbol for fractions that have varying numbers of digits.

Put this code in the appropriate place in my code posted above previously to allow all the decimals to be aligned - without unnecessary trailing zeros.

If maxDecimalPlaces >= 1 Then
DecimalFormat = "#.?"   'the ? aligns decimals, but does not add trailing 0's
For x = 2 To maxDecimalPlaces
 DecimalFormat = DecimalFormat & "?"
Next

If AllmaxDecimalPlaces >= 1 Then
DecimalFormat = "#.?"   'the ? aligns decimals, but does not add trailing 0's
For x = 2 To AllmaxDecimalPlaces
 DecimalFormat = DecimalFormat & "?"
Next

Re: Consistent Decimal Places

Yep, you're right on, showing the extra decimals implies accuracy that isn't really there, (my profs would have slapped me for reporting such) but it's a necessary evil of getting everything in the range to match the largest number of decimal places found in that range.

I'm not sure if Excel has the equivalent of the DecimalTab in Word. This would be what you need to answer your last question. This is where all values irregardless of how many or few decimals are aligned with the decimal. You could start a new Thread with a title such as "Aligning values with Decimal Point".

Re: Consistent Decimal Places

OK, I got a bit carried away, but the code below will do what you ask, PLUS MORE, in case other users stumble across it and need it for their own uses.
I changed the "#.0" to "0.0" as you found you needed - good suggestion!
-and I fixed the bug I mentioned.
For your particular setup, answer YES and YES to the two message boxes that come up.

Sub FindMaxDecimalsinRange()


'This determines the maximum number of decimal places used by either:
'(A) the value as it appears in the formula bar via the .value qualifier
'(B) the value as it appears on the screen  via the .text qualifier
'The value displayed by (A) is closer to the maximum number of decimal
'places retained in memory and can be more that what's displayed on screen
'if the column is not wide enough.
'Excel usually only displays a maximum of 14 decimal places on the screen
'and in the formula bar, but may be able to retain more in memory (up to 30?)
'By macromike 11/30/2006




'Step 1: Enter the limits for your range data below
FirstColumn = 1
LastColumn = 4
FirstRow = 1
LastRow = 30


UserResponseA = MsgBox("Press YES to format according to max decimal places in memory (see formula bar)" & Chr(10) & _
"Press NO to format according to max decimal places shown shown on the screen - which depends on your column width", vbYesNo)




'Step 2a: determining the maxDecimalPlaces in the individual column
For columnx = FirstColumn To LastColumn
maxDecimalPlaces = 0  'resets this for each pass through loop


For rowx = FirstRow To LastRow
If UserResponseA = vbYes Then
 textx = Cells(rowx, columnx).Value
End If
If UserResponseA = vbNo Then
 textx = Cells(rowx, columnx).Text
End If


 DecimalLocation = InStr(1, textx, ".", vbTextCompare)
  If (DecimalLocation > 0) And (DecimalLocation) <> "" Then
  DecimalPlaces = Len(textx) - DecimalLocation
  If DecimalPlaces > maxDecimalPlaces Then
   maxDecimalPlaces = DecimalPlaces
  End If
 End If
Next


'Step 2b: reformating the individual column to the maxDecimalPlaces
If maxDecimalPlaces >= 1 Then
DecimalFormat = "0.0"
For x = 2 To maxDecimalPlaces
 DecimalFormat = DecimalFormat & "0"
Next
Range(Cells(FirstRow, columnx), Cells(LastRow, columnx)).Select
Selection.NumberFormat = DecimalFormat
End If


'Step 2c: This is gathering info for Step 3, if Step 3 is desired
If maxDecimalPlaces > AllmaxDecimalPlaces Then
   AllmaxDecimalPlaces = maxDecimalPlaces
End If


Next




'Step 3: This will reformat all the columns in the range to the same AllmaxDecimalPlaces
UserResponseB = MsgBox("Your columns have each been formatted to the max number of decimal" & Chr(10) & _
"places used within that column." & Chr(10) & _
"Do you want all your columns formatted" & Chr(10) & _
" to the same maximum number of decimal places?" & Chr(10) & _
"If you like how they look now, then answer No", vbYesNo)
If UserResponseB = vbYes Then
If AllmaxDecimalPlaces >= 1 Then
DecimalFormat = "0.0"
For x = 2 To AllmaxDecimalPlaces
 DecimalFormat = DecimalFormat & "0"
Next
Range(Cells(FirstRow, FirstColumn), Cells(LastRow, LastColumn)).Select
Selection.NumberFormat = DecimalFormat
End If
End If


Cells(FirstRow, FirstColumn).Select  'just return to first cell in range


End Sub

Re: Consistent Decimal Places

Hi Dave,
You can make it work for more than one column. But two questions.
(1) Do you want the max decimal places to be determined for all four columns, and then that SAME max decimal places applied to all four columns?
-or-
(2) Do you want the max decimal places determined for each column and then applied to each column individually?

- The msgboxes don't have to appear, I threw them in there as sort of check to see what each step was doing. You could turn one or all of them on, but it will slow things down.

Re: Consistent Decimal Places

Excel will only display up to 14 decimals in the range - I think.

In the code below, I've accessed the cells .value property to get the full amount of decimals as shown in the formula bar. If you changed this to the .text property, you woudl get however many decimals are being displayed based on your column width. The code has one bug, in that if a number has no decimal places, it thinks it still has one decimal place.

Try this:

Sub countmaxdecimals()
'Enter the ColumnNumber and FirstRow and LastRow for your range below
ColumnNumber = 1
FirstRow = 1
LastRow = 6


For rowx = FirstRow To LastRow
 textx = Cells(rowx, ColumnNumber).Value
 DecimalLocation = InStr(1, textx, ".", vbTextCompare)
 'MsgBox "DecimalLocation = " & DecimalLocation & " on row = " & rowx
 If DecimalLocation <> 0 Then
  DecimalPlaces = Len(textx) - DecimalLocation
  If DecimalPlaces > maxDecimalPlaces Then
   maxDecimalPlaces = DecimalPlaces
  End If
 End If
'MsgBox "DecimalPlaces = " & DecimalPlaces & " on row = " & rowx
Next


'MsgBox "MaxDecimalPlaces = " & maxDecimalPlaces
DecimalFormat = "#.0"
For x = 2 To maxDecimalPlaces
 DecimalFormat = DecimalFormat & "0"
Next


Range(Cells(FirstRow, ColumnNumber), Cells(LastRow, ColumnNumber)).Select
Selection.NumberFormat = DecimalFormat
End Sub

Re: Auto Re-name Chart

Quote from Dave Hawley

Guys, the whole purpose of this forum is that one persons question may answer that of a thousand others. It is against the rules to use the forum and then take it private.

kelly_s81, if your question is related to another Thread, you should link to it, if needed. Also, the rules is one question per Thread and that Thread Title must match the question.

Thanks Dave. You're right. We were running a bit astray, because this was another question started on the same project in another thread. I feel its my fault, if I had just added one more line of code to name the chart upon completion in that thread, Kelly_s81 wouldn't had this question. Its one of those things were you think should I, shoudn't I, then wish you did later, because it DID cause problems not to add it. It's good that you renamed the thread title.

Re: Best Method For Exporting Charts

If copy and paste an Excel chart to another worksheet in Excel, it still maintains the links to the SourceData for you. However, when a chart is placed in another workbook (just open up a new Excel workbook and paste it in there), the option "Links..." under the Edit menu becomes available. You can choose to break the links, to establish a stand-alone chart. It will give you a warning message which essentially says that your SourceData will become actual data instead of an address to the data in the other workbook. This could be one way to Export the charts as is, within an Excel document, without them being able to be modified.

I also do the following to transfer Excel Charts/Plots into other applications like Word and Powerpoint, especially when report writing or doing presentations. This will also break the links to the Excel data.
1) Select the entire chart (just click in the white space outside the plot area. This should grab the Title, Axis labels, Legend, everything, etc.
2) Select Copy
3) Go to Word or PowerPoint
4) Select Paste-Special
5) Insert as a Picture (Enhanced Metafile), sometimes you see Picture (Windows Metafile) - both have given me good results. There may be an option simply entitled "Picture", which may work as well. DO NOT insert as a Microsoft Excel Object - or else your links will be maintained, i.e. when the user double clicks on the Chart, Excel is reopened.

By default, it fits a scaled picture to fill the space between the the left and right margins of the Word document or PowerPoint slide I'm inserting it in. If you want, you could adjust the left and right margins before hand so the Chart is the size you want it. You can also use the click on the picture and rescale it by dragging the corners or sides as you would to any other picture. All wording and Chart bars will stay in exactly the same proportion to each other within the picture.

This is an advantage over trying to resize a Chart inserted as an embedded object on a worksheet. Sometimes resizing these embedded object charts within Excel can cause the font scaling and chart to become all jumbled up into an unreadable mess.
This occurs even if Break Links were selected (which has nothing to do with resizing), because its just a by-product of Excel's repeated attempts to autoscale many things.

Its better to even do the above Five Steps to get a Chart into Word, then Paste Special it BACK into Excel as a Picture to avoid these resizing problems. You can not just Copy and Paste Special a Chart back into Excel, since Paste Special (as a Picture) is not available. You have to create the Picture of the chart in a secondary program like Word or PowerPoint first.

Re: Linking Cells To A Word Document

Yes, its easy. It helps to have Vba textboxes created by the Visual Basic toolbar, NOT the forms textboxes created by the Forms toolbar inserted into your Word document. The following will pull a "DataRecord" from an Excel workbook and insert it into a Vba textbox within Word. Put this code within your Word document.

*I included an extra operation under Step 4 to just divide whatever value you found in Excel by 1000. I'm not sure if this is what you need in your particular case.

Sub GetExcelDataIntoWord()
'by MacroMike
'this macro goes into a Word document


'--------------------------------
'Step 1: Opens up the Excel workbook "in the background" that you want to pull data from
'--------------------------------


Dim MyExcelFile As String
'Subsitute the Path and Name to the Excel file you want to open below
MyExcelFile = "C:\Documents and Settings\Mike\Desktop\MyExcelWorkbook.xls"


Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = False           'users don't need to see Excel appearing on screen
xlApp.ScreenUpdating = False    'if true then Excel file sits ontop word file on the screen
Set xlWB = xlApp.Workbooks.Open(FileName:=FName, ReadOnly:=True, UpdateLinks:=False)


'--------------------------------
'Step 2: Extracts the information from the Excel workbook
'--------------------------------


'Enclose all these operations within a With loop to keep the focus on the Excel workbook
With xlWB.worksheets(1)
'you could subsitute code here to search for a DataRecord
'once you locate it, you know its RowNumber and ColumnNumber
'Capture the value at that RowNumber,ColumnNumber into a Variable
    DataRecord = .Cells(RowNumber, ColumnNumber).Value
End With    'The end of focus on excel operations


'--------------------------------
'Step 3: Closes the Excel workbook
'--------------------------------


xlWB.Close False ' close the workbook without saving
xlApp.Quit ' close the Excel application
Set xlWB = Nothing
Set xlApp = Nothing


Application.ScreenUpdating = True 'allow updates to be shown on the Word document in the next Step
'screenupdating was turned off previously so Excel was never shown on the screen




'--------------------------------
'Step 4: Writes variable values back to a *Vba textbox in the Word document
'--------------------------------
'*VBa textboxes are created using the Control Toolbox under the Visual Basic 
'Toolbar.  These are not the same as the Forms textboxes created under the 
'Forms toolbar.  You might be able to adapt this code to write to a Form Field 
'created with the Forms toolbar as well.


DataRecord = DataRecord / 1000 'just for user AKphidelt's needs


'subsitute whatever the name is of your VBa Textbox is in Word
WordTextBox1.Value = DataRecord


End Sub

Re: Auto Re-name Chart

I haven't left the room - yet!

Can you post your Excel file? Or email directly (my email is included in a private message to you). I think we should take this off line, since to answer everything, we are straying further from what the original question is in your thread (Auto Re-Name a chart), because its becomming mixed in with (Chart relocation) as well. I don't want to confuse other readers of this thread.

Re: Auto Re-name Chart

OK, what I think you need to do is explicitly name the chart as soon as you generate it. Then you can refer to it and move it following that. I'm not sure how you are moving it to the foot of the page - that's a separate code.

For other users please ignore the first parts of this code which is selecting a range and generating a plot. OK, I know I may be side-stepping the rules a bit, but I feel its necessary to answer the question. Also its applicable to any other problem where you might be generating multiple plots and need to maintain control over naming them.

Sub kelly() 
ChartNumber = InputBox("How many charts do you need?", "Please enter a number")
 
For X = 1 to ChartNumber 
people = InputBox("How many people sat the exam?", "Please enter a number") 
lastrow = people + 1 'based on data starting on row 2
RangetoPlot = "A2:C" & lastrow 
Charts.Add 
ActiveChart.ChartType = xlColumnClustered 
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range(RangetoPlot), PlotBy:=xlColumns 
'NOTE: change "Sheet1" above to whatever the actual sheet name is that you are pulling the data from


'Place chart in sheet
ActiveChart.Location Where:=xlLocationAsObject, Name:="Chart" & ChartNumber


'Or alternative is place chart on separate sheet where it would fill the page
'you wouldn't have to worry about moving it around on the page
'ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Chart" & ChartNumber
 
'Put in code to move the chart on the page using the ChartNumber variable 
'to reference it and move it
ActiveSheet.Shapes("Chart " & ChartNumber).IncrementLeft 122.25
ActiveSheet.Shapes("Chart " & ChartNumber).IncrementTop -1.5


Next 
End Sub