Small Excel Question Not Worth Its Own Thread

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread

Sub Par: Jul 18, 2001; Dinosaur Gum

gigabitnokie posted:

I have a worksheet that looks like this:
code:
John    Smith     10/22/09
Jane    Doe       8/17/09
...
I'd like to have another worksheet display the names and expiration dates of those records whose expiration dates have already passed. Make sense? In this case only Jane Doe would appear on the next worksheet, but on 10/23/09 John Smith would appear as well.

Thanks in advance for any help ya'll can provide

How automated would this need to be? Filter and copy/paste seems like the easiest, unless you're looking for an automated solution where every time you open the workbook the second sheet has only the "expired" info.

# ¿ Aug 28, 2009 14:50

Adbot: ADBOT LOVES YOU

# ¿ Apr 28, 2024 00:22

Sub Par: Jul 18, 2001; Dinosaur Gum

I am working on this while drinking a beer because it seems like a useful thing to have handy, I will be interested to see how you did it because I was making a lot of assumptions about your files (they would all have a header row, they wouldn't have duplicate data points, etc).

# ¿ Sep 2, 2009 04:16

Sub Par: Jul 18, 2001; Dinosaur Gum

ABombInABowl posted:

I have a web service that emails me certain information regarding a customer inquiry. The email body is XML with the clients information. I need to build a "dashboard" in Excel that will retrieve the last 10 emails, parse certain information from the XML, and then display this data in a worksheet, automatically updating when a new email comes in.

Any ideas where to start? I've gone through MSDN documentation but it's a little overwhelming for me. I can do some basic programming in VBA but I've never written anything to interact with other Office applications or XML data...

I dislike using VBA in Excel to access data in Outlook. I have some reports that do similar things. I generally have incoming emails checked by outlook for the appropriate sender/subject/attachment name/whatever, and when it finds the email, it downloads the attachment to a known directory.

I then have the relevant Excel document execute a macro whenever it's opened to get the most recent X number of XML documents from the known location and do whatever operations are necessary. Hope that helps.

If you want to interface directly with Outlook, you'll need to add the Outlook objects reference, and it's easy enough, but you'll have to make sure to handle errors related to Outlook sucking/being unavailable/closed so your users don't get runtime errors when they do dumb things.

# ¿ Dec 4, 2009 18:07

Sub Par: Jul 18, 2001; Dinosaur Gum

Schleep posted:

I couldn't figure out a way to get my other question answered so I've decided to put each parameter in a separate column. All I need it to do now is recognize this pattern in one column:

ROW 5=MAX($D$3:D4)
ROW 6=MAX($D$3:D4)
ROW 7=MAX($D$3:D5)
ROW 8=MAX($D$3:D5)
ROW 9=MAX($D$3:D6)
ROW 10=MAX($D$3:D6)
ROW 11=MAX($D$3:D7)
ROW 12=MAX($D$3:D7)
=...ect

This... Excel sucks at. I don't know of many ways around it, but I have a convoluted method that I use from time to time. If someone knows of a better method, by all means, let me know, but here's what I do.

I generally create a new sheet, and use it to generate the pattern I want. In your case, my goal will be to get a column that has values like this:

code:

D4
D4
D5
D5
D6
D6
...

In cell A1, I enter the number 4. I then select A1 and A2 (which is blank) and use the fill handle to fill down to the number of values I need. This will create a column of incrementing numbers with a blank cell between. I then select cell B2 and enter the number 4. Then select B2 and B3, and fill down. You will have two columns now with numbers incrementing, skipping a cell between each.

In C1, I enter the formula ="D"&A1 and in C2 I enter the formula ="D"&B2. Then fill down the C column. The result looks like this:

You will use the values in the C column in the range for your MAX formula, which is accomplished thusly:

=MAX($D$3:INDIRECT(Sheet1!C1))

Where Sheet1 is the name of the sheet where you did all that work. INDIRECT() tells Excel to use the value of Sheet1!C1 as a range parameter. You can then just fill down your formula from there. Modify this procedure to suit your purposes.

quote:

and this pattern in the other column:

ROW 5=MAX(D$5:D7)
ROW 6=MAX(D$6:D8)
ROW 7=MAX(D$6:D9)
ROW 8=MAX(D$7:D10)
ROW 9=MAX(D$7:D11)
ROW 10=MAX(D$8:D12)
ROW 11=MAX(D$8:D13)
ROW 12=MAX(D$9:D14)
=...ect

The dollar sign will prevent Excel from incrementing, so you should remove it, but that won't fix the issue anyway. I would do something similar to the above for this one.

You could also write a UDF if you need something more dynamic. Hope this helps.

# ¿ Jan 4, 2010 16:12

Sub Par: Jul 18, 2001; Dinosaur Gum

Can you disregard the "name" prefix or whatever prefix and just get the last X number of characters? If so, you can just use vlookup.

Edit: if I'm not understanding it right, you could probably just turn on the macro recorder and then manually do all the finding/replacing you want, and then stop the recorder and have a look at the code in the vba editor. It's pretty straightforward to adapt the recorded macro for your own purposes.

Sub Par fucked around with this message at 04:11 on Jan 16, 2010

# ¿ Jan 16, 2010 04:08

Sub Par: Jul 18, 2001; Dinosaur Gum

Grey Area posted:

Is there a way to do sprintf() or String.format() in VBA (Excel 2003)? Using lots of & gets unreadable fast.

edit: Any idea why this gives a 1004 error:
code:
yearClassAvgPrice = "=IF(SUM(Totalt!$C$7:$C$23)=0;0;SUMPRODUCT(Totalt!$C$7:$C$23;Totalt!$D$7:$D$23)/SUM(Totalt!$C$7:$C$23))"
SummaryOrigin.Offset(7, 5).Value = yearClass1AvgPrice
Pasting the formula into a cell works.

You've got semicolons in the if statement that should be commas. When I paste that in, Excel tells me about it being wrong. When I change the semicolons to commas, it tells me there's something else wrong that it can auto-correct. I'm too lazy to compare your original to the auto-corrected, but here it is:

code:

=IF(SUM(Totalt!$C$7:$C$23)=0,0,SUMPRODUCT(Totalt!$C$7:$C$23:Totalt!$D$7:$D$23)/SUM(Totalt!$C$7:$C$23))

But beyond that, you're setting the value of the cell to "yearClass1AvgPrice" where the variable you're storing that string in is just "yearClassAvgPrice" with no 1. That's the runtime error.

Sub Par fucked around with this message at 14:58 on Jul 22, 2010

# ¿ Jul 22, 2010 14:56

Sub Par: Jul 18, 2001; Dinosaur Gum

Quick and dirty solution in VBA. Create a module, create this sub:

code:

Sub GetSelectedFilterItems(strPivotName As String, strPivotField As String)

Dim strReturn As String
strReturn = "Timesheets: "

For Each Item In ActiveSheet.PivotTables(strPivotName).PivotFields(strPivotField).PivotItems
    If Item.Visible = True Then strReturn = strReturn & Item.Name & ", "
Next Item

strReturn = Left(strReturn, Len(strReturn) - 2)

Sheets("Sheet2").Cells(2, 1).Value = strReturn

End Sub

Then add this to the worksheet that has the pivot table on it:

code:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
GetSelectedFilterItems "pivottable1", "col3"
End Sub

Substitute "pivottable1" for the name of your pivot table (edit: you could also just do target.name) and "col3" for the name of the field you want to filter on (looks like "Timesheet Number" in your screenshot). This will dump the selected items into a comma-separated list into cell A2 of whatever sheet the pivot table is on. Adjust as needed. Results:

Sub Par fucked around with this message at 16:37 on Nov 3, 2010

# ¿ Nov 3, 2010 16:33

Sub Par: Jul 18, 2001; Dinosaur Gum

Did you refresh the pivot after clearing the data out of the workbook?

# ¿ Nov 3, 2010 19:33

Sub Par: Jul 18, 2001; Dinosaur Gum

Can you post the code? Excel isn't very good at recording a macro with the idea of a dynamic range, and oftentimes you will need to make a couple edits to what was recorded to make it work as you intended.

# ¿ Jan 12, 2011 07:19

Sub Par: Jul 18, 2001; Dinosaur Gum

Ok, this should do the trick. Replace the sub Sort() stuff with this:

code:

Sub Sort()
'
' Sort Macro
'
' Keyboard Shortcut: Ctrl+Shift+S
'

Dim UsedRows As Integer
UsedRows = ActiveSheet.UsedRange.Rows.Count
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range(Cells(2, 1), Cells(UsedRows, 1)) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range(Cells(2, 3), Cells(UsedRows, 3)) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range(Cells(2, 4), Cells(UsedRows, 4)) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range(Cells(2, 2), Cells(UsedRows, 2)) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange ActiveSheet.UsedRange
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

What I'm doing here is changing the static ranges in there (A2:A60, etc) to dynamic ones. At the start, a variable is set to the number of used rows on your sheet, which is used to build all those ranges for the 4 sort conditions. Then at the bottom in the with statement, I changed the range there (A2:J60) to the range that is currently being used in the workbook. Brief testing indicates to me that it works.

Edit: to answer your questions, the reason it only allowed for 60 rows is because that was the number of rows present when you recorded the macro. As I said, Excel doesn't have any way of knowing that you're trying to build a macro meant to handle an arbitrary number of rows/columns. The recorder is designed to repeated exactly what you did while it was running. And what you did was sort 60 rows of data a certain way, regardless of how many cells you select.

You can see how literal it is by recording a macro that is just you scrolling the mousewheel. It literally records all of those screen scrolling actions as well, so it can play them back to you.

Sub Par fucked around with this message at 07:32 on Jan 12, 2011

# ¿ Jan 12, 2011 07:29

Sub Par: Jul 18, 2001; Dinosaur Gum

The overflow is because Excel is bad at figuring things out and can't do 420*420 with 16 bit integers. Just change (420*420) to 176400 and that will fix it.

# ¿ Jun 13, 2011 15:52

Sub Par: Jul 18, 2001; Dinosaur Gum

You can accomplish this fairly easily with a macro. Try this:

code:

Sub AddHyperlinks()

Dim TargetColumn As Integer, strSubAddress As String
TargetColumn = 1

For c = 1 To 2 'Columns to include
    
    For r = 2 To 3 'Rows to include
        Cells(r, c).Select
        strSubAddress = "Sheet2!" & Cells(2, TargetColumn).Address 'Replace Sheet2 with the sheet your target data is on
        ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
            strSubAddress, TextToDisplay:=Cells(r, c).Value
        TargetColumn = TargetColumn + 3
    Next r

Next c

End Sub

In the first for loop, you'll want to replace the numbers 1 and 2 with the start and end columns where your data is present in COMPANY LIST (in your screenshot this would be 1 and 5).

In the second for loop, you'll want to replace the numbers 2 and 3 with the row range within which your data is present in COMPANY LIST (in your screenshot this would be 2 and 11).

Finally, you'll want to replace "Sheet2" where strSubAddress is assigned to be the name of the sheet where your "actual data" is. If these are spread across multiple sheets you can modify this code to account for that or you can just run it multiple times. Hope that helps.

Edit: to make this faster to write assumed you would have the sheet you intend the hyperlinks to be on as the active sheet when the macro is run.

# ¿ Jun 20, 2011 17:09

Sub Par: Jul 18, 2001; Dinosaur Gum

Halcyon posted:

Sorry if this has been asked already, but how do I generate a set of 400 numbers from the range 1-1000, without any repeats?

Right now I have 1000 entrants that I need to widdle down, randomly, to 400.
I ve tried rand() the cell next to the entrant, dragging it down to number 1000. then filter, either ascending or descending, the randomly generated numerical values and taking the top 400 values. I assumes there's a lot more compact and less complex way of doing this.

thanks

Excel kind of sucks when it comes to tasks like this. When I have a set of data and need the top N random rows, I do exactly what you do: create a column, fill down with RAND(), and then sort by that column and take the top N rows.

You could write VBA to accomplish what you want in a more compact way, but unless you're going to be doing this repeatedly, there's really no reason to since the above is easy enough.

# ¿ Jun 21, 2011 04:30

Sub Par: Jul 18, 2001; Dinosaur Gum

Mr. Apollo posted:

Thanks. I followed your instructions but I seem to have done something wrong as when I got to run it I get an "Invalid procedure call or argument" error.

I don't know if it matters, but on the first sheet when I want to place all the hyperlinks, each column has a different number of names in it. So in the second loop I gave the row range of the column that has the largest amount of names in it.

Sorry. Change to this:

code:

Sub AddHyperlinks()

Dim TargetColumn As Integer, strSubAddress As String
TargetColumn = 1

For c = 1 To 2 'Columns to include
    
    For r = 2 To 3 'Rows to include
        Cells(r, c).Select
        strSubAddress = "Sheet2!" & Cells(2, TargetColumn).Address 'Replace Sheet2 with the sheet your target data is on
        if Cells(r, c).Value <> "" then
        ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
            strSubAddress, TextToDisplay:=Cells(r, c).Value
        end if
        TargetColumn = TargetColumn + 3
    Next r

Next c

End Sub

This will prevent Excel from attempting to add a hyperlink when there is no company name in the given cell.

# ¿ Jun 21, 2011 17:11

Sub Par: Jul 18, 2001; Dinosaur Gum

You can find out more by adding "on error goto errReport" on the line after "sub addhyperlinks()" and then right before "end sub" add this:

code:

exit sub
errReport:
msgbox err.description

But this is probably happening because your sheets are protected or possibly hidden. What version of Excel are you using?

# ¿ Jun 22, 2011 21:13

Sub Par: Jul 18, 2001; Dinosaur Gum

You're missing an "else" answer for that last if.

quote:

=IF(CR5<=100%,"Fail",IF(CW5<=100,"Pass",IF(DB5<=100,"Merit",IF(DB5=100,"Distinction",PUTSOMETHINGHERE))))

# ¿ Jul 26, 2011 20:26

Sub Par: Jul 18, 2001; Dinosaur Gum

Add a new column called year, and in that column fill the formula =year(A2) where A is the column that contains your date. Then on a new tab, create a pivot table, use the year column you created as the column, the date as the row (format as MM/DD or however you want), and put the value in the data section.

Edit: I see you have this data in many places. I would suggest combining it into one sheet. If it won't fit, I'd suggest dumping it into a single DB table and then building the pivot on that table.

# ¿ Aug 9, 2011 21:07

Sub Par: Jul 18, 2001; Dinosaur Gum

I'm pretty sure what you want to do is a violation of Google's TOS. They used to have a SOAP API that you could use for something like this (up to 1k queries/day) but they are no longer giving out licenses for that.

You could put this lovely formula into a cell of column B:

quote:

=HYPERLINK("http://www.google.com/search?ie=UTF-8&oe=UTF-8&sourceid=navclient&gfns=1&btnI=Im+Feeling+Lucky&q="&A1,"I'm Feeling Lucky")

This would create a hyperlink in the cell that, when clicked, will open up the Google I'm Feeling Lucky result for whatever text is in cell A1.

# ¿ Aug 19, 2011 21:00

Sub Par: Jul 18, 2001; Dinosaur Gum

You would want to have a couple rules. The first one would be to have no formatting where where the following formula is true: =B1="Y" and you would want to specify that if that rule is true, Excel should stop.

The second rule should be to format based on the values of the A column. Here's as screenshot of the rules I set up:

And here's how it looks:

# ¿ Sep 7, 2011 18:21

Sub Par: Jul 18, 2001; Dinosaur Gum

Sorry, I misunderstood your problem. You want to use an array formula. In your "highlight cells based on their values" rule, for the min and max select "formula" and put this in for the minimum:

code:

=MIN(IF($B:$B="N",$A:$A))

And this in for the maximum:

code:

=MIN(IF($B:$B="N",$A:$A))

after you type/paste them in, press alt+enter. That should do the trick. Example:

# ¿ Sep 7, 2011 21:14

Sub Par: Jul 18, 2001; Dinosaur Gum

What kind of stuff are you needing to do? UI type "button click does these events" stuff, data manipulation, other stuff? Honestly I haven't read any books that are super helpful and generally work until I get stuck at which point I google, which usually works just fine.

In a pinch I've walked down to the Barnes and Noble and peeked in a few books, both the Oreilly "Programming Excel with VBA and .NET" title and the VBA for dummies one ( :lol:

but it is decent) were helpful. But I'd suggest not buying a book unless you really learn best that way.

# ¿ Sep 15, 2011 00:47

Adbot: ADBOT LOVES YOU

# ¿ Apr 28, 2024 00:22

Sub Par: Jul 18, 2001; Dinosaur Gum

Since your standings tab probably has fixed ranges that don't change, you'll be able to get by this way:

1) Open the VBA editor and double-click the "results" tab in the tree nav on the left.

2) There are two drop-down boxes above the right pane. On the left, select "Worksheet". On the right, select "Change".

3) This will create two Subroutines. Delete the one called Worksheet_SelectionChange. In the other (Worksheet_Change) enter this code (modifying for your purposes:

code:

    ActiveWorkbook.Worksheets("Standings").Sort.SortFields.Clear
    'Instead of B2:B14, put the range that contains the range for the column you want to sort by (without headers)
    ActiveWorkbook.Worksheets("Standings").Sort.SortFields.Add Key:=Range( _
        "B2:B14"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    'Instead of A1:B14, put the range that contains the range you want to sort INCLUDING headers.
    With ActiveWorkbook.Worksheets("Standings").Sort
        .SetRange Range("A1:B14")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

As far as sorting by multiple criteria, when you sort this way, those sorts cascade. So you can take that second statement there (..Sort.SortFields.Add...) and add a bunch of them like this to sort (for example by columns B, then C, then D):

code:

    ActiveWorkbook.Worksheets("Standings").Sort.SortFields.Clear
    'Instead of B2:B14, put the range that contains the range for the column you want to sort by (without headers)
    ActiveWorkbook.Worksheets("Standings").Sort.SortFields.Add Key:=Range( _
        "B2:B14"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    'Instead of C2:C14, put the range that contains the range for the column you want to sort by (without headers)
    ActiveWorkbook.Worksheets("Standings").Sort.SortFields.Add Key:=Range( _
        "C2:C14"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    'Instead of D2:D14, put the range that contains the range for the column you want to sort by (without headers)
    ActiveWorkbook.Worksheets("Standings").Sort.SortFields.Add Key:=Range( _
        "D2:D14"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    'Instead of A1:D14, put the range that contains the range you want to sort INCLUDING headers.
    With ActiveWorkbook.Worksheets("Standings").Sort
        .SetRange Range("A1:D14")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Hope this at least helps you.

# ¿ Mar 17, 2012 04:12

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread