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

«‹›82 »

Bob Morales: Aug 18, 2006; ~~Just wear the fucking mask, Bob~~

I don't care how many people I probably infected with COVID-19 while refusing to wear a mask, my comfort is far more important than the health and safety of everyone around me!

Can you match a whole row using VLOOKUP or something and not just a cell?

code:

BOB  New York New York   Sales        JOE  Megacorp $50,000   Single   45
JOE  Detroit  Michigan   Accounting   JOE  ACME Co  $65,000   Married  36

So you a spreadsheet of people's information and you want to join the rows up with another spreadsheet of different info but the same people. There's like 100 columns on each sheet so I wanted to avoid doing 100 VLOOKUP formulas

Edit: Nevermind - I worked around it. I had them sorted 2 different ways, so I just did a 'sorted_order' column, 1-1000, then I sorted them the same way to make them line up, then re-sorted back on the sold sorted_order column to go back to the other way I had them sorted.

Bob Morales fucked around with this message at 22:38 on Feb 3, 2017

# ? Feb 3, 2017 21:23

Adbot: ADBOT LOVES YOU

# ? May 12, 2024 19:16

totalnewbie: Nov 13, 2005; I was born and raised in China, lived in Japan, and now hold a US passport.

I am wrong in every way, all the damn time.

Ask me about my tattoos.

You can use combination of index and match to do vlookup with multiple criteria. There's lots of stuff online about how to implement it.

# ? Feb 4, 2017 17:48

MeKeV: Aug 10, 2010

I'm having trouble figuring out the syntax to get the Filter formula to remove blank Rows instead of Columns.

"Filter views" doesn't seem to want to look at a full row, rather just an empty cell in a row.

It's a Google sheets spread sheet, and I've added a range from sheet 1 to a blank sheet, the data is A1 to J900, with a single value in each row, and some empty full rows. I want to hide all blank rows.

What's my best approach?

# ? Feb 9, 2017 21:20

AzureSkys: Apr 27, 2003

I routinely use a .xlsm file at work that I limited access to, but can talk to the owner to fix. I think when we updated Office to 2013 something changed to make the quick print button ignore the print area instead of the properly formatted area for the print out we need.
I have to ctrl+P and print from there (which is a pain since it'll hang a bit waiting for the network printer).

I couldn't find any settings about it in the page setup, print options, the printer driver, or Excel options and gave up. Now, a few years later, I added "Print" to my quick access toolbar from the All Commands list. When you select it, two options for Print or Print Preview pop open. Selecting Print opens the old style print options menu. On the bottom "Ignore Print Areas" is checked.

Here's a pic of it:

I don't know how to get to that menu other than adding and using that quick access print button in Excel 2013 (ctr+P goes print preview). Then, I don't know how to make ignore print areas unchecked as default. I've searched through various forums with no luck. I don't know where to look for any VB code that may affect it, too.
My own workbooks have it unchecked by default. If I check it, it'll be unchecked when I go back to that menu regardless of me saving the file or not. So, I don't know how to alter the default setting. If I figure out how to change it I can have the owner of the file I use update the master. It's not a huge deal, just one of those small things that's a pain over time when I have to repeat it 10+ times a day instead of clicking quickprint.

# ? Feb 10, 2017 10:27

coyo7e: Aug 23, 2007; by zen death robot

Record a macro, repeat process, stop macro, edit macro.

MeKeV posted:

I'm having trouble figuring out the syntax to get the Filter formula to remove blank Rows instead of Columns.

"Filter views" doesn't seem to want to look at a full row, rather just an empty cell in a row.

It's a Google sheets spread sheet, and I've added a range from sheet 1 to a blank sheet, the data is A1 to J900, with a single value in each row, and some empty full rows. I want to hide all blank rows.

What's my best approach?

Make a table?

# ? Feb 12, 2017 22:26

D34THROW: Jan 29, 2012; RETAIL RETAIL LISTEN TO ME BITCH ABOUT RETAIL

totalnewbie posted:

You can use combination of index and match to do vlookup with multiple criteria. There's lots of stuff online about how to implement it.

My two cents, I've completely replaced my VLOOKUPs with INDEX MATCH formulas on the occasion I need them. Much more versatile.

# ? Feb 13, 2017 17:02

Sad Panda: Sep 22, 2004; I'm a Sad Panda.

First time ever playing with a VBA macro.

I found the following code to change the colour of a tab based on the contents of a cell.

code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target <> Range("A1") Then Exit Sub
    With Me.Tab
        Select Case Range("A1")
            Case 1: .Color = vbBlack
            Case 2: .Color = vbRed
            Case 3: .Color = vbGreen
            Case 4: .Color = vbYellow
            Case 5: .Color = vbBlue
            Case 6: .Color = vbMagenta
            Case 7: .Color = vbCyan
            Case 8: .Color = vbWhite
            Case Else: .ColorIndex = xlColorIndexNone
        End Select
    End With
End Sub

It's not quite what I want so I tried making a simple bit of code based on that to start as a building block to teaching myself VBA, and came up with the following. But whether the number 5 is in K26 or not the tab never changes to black.

code:

Private Sub Worksheet_Change(ByVal Target As Range)
        With Me.Tab
            If Range("K26") = "5" Then
                .Color = vbBlack
            End If
        End With
End Sub

What am I doing wrong?

My goal is basically to look at 3 cells and change the colour of the tab based on that. Something along the lines of..

code:

If Range("J52") = "Yes" Then .Color = vbBlack  ' if that's the case then that's the end and it shouldn't change colour based on the others. This is the priority
Else If Range("M50") = "Yes" Then .Color = vbRed
Else if Range("M51") = "Yes" Then .Color = vbGreen

Something along those lines is what I'm after. Only one of M50 or M51 can possibly say yes at the same time.

Thank you so much.

# ? Feb 17, 2017 00:02

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

Sad Panda posted:

First time ever playing with a VBA macro.

I found the following code to change the colour of a tab based on the contents of a cell.
code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target <> Range("A1") Then Exit Sub
    With Me.Tab
        Select Case Range("A1")
            Case 1: .Color = vbBlack
            Case 2: .Color = vbRed
            Case 3: .Color = vbGreen
            Case 4: .Color = vbYellow
            Case 5: .Color = vbBlue
            Case 6: .Color = vbMagenta
            Case 7: .Color = vbCyan
            Case 8: .Color = vbWhite
            Case Else: .ColorIndex = xlColorIndexNone
        End Select
    End With
End Sub
It's not quite what I want so I tried making a simple bit of code based on that to start as a building block to teaching myself VBA, and came up with the following. But whether the number 5 is in K26 or not the tab never changes to black.
code:
Private Sub Worksheet_Change(ByVal Target As Range)
        With Me.Tab
            If Range("K26") = "5" Then
                .Color = vbBlack
            End If
        End With
End Sub
What am I doing wrong?

My goal is basically to look at 3 cells and change the colour of the tab based on that. Something along the lines of..
code:
If Range("J52") = "Yes" Then .Color = vbBlack  ' if that's the case then that's the end and it shouldn't change colour based on the others. This is the priority
Else If Range("M50") = "Yes" Then .Color = vbRed
Else if Range("M51") = "Yes" Then .Color = vbGreen
Something along those lines is what I'm after. Only one of M50 or M51 can possibly say yes at the same time.

Thank you so much.

Phone posting and not a VBA expert, but aren't you forcing a string comparison by using ="5" instead of =5?

# ? Feb 17, 2017 00:14

shatteringglass: Apr 27, 2010

Sad Panda posted:

code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target <> Range("A1") Then Exit Sub
    With Me.Tab
        Select Case Range("A1")
            Case 1: .Color = vbBlack
            Case 2: .Color = vbRed
            Case 3: .Color = vbGreen
            Case 4: .Color = vbYellow
            Case 5: .Color = vbBlue
            Case 6: .Color = vbMagenta
            Case 7: .Color = vbCyan
            Case 8: .Color = vbWhite
            Case Else: .ColorIndex = xlColorIndexNone
        End Select
    End With
End Sub

If you're trying to access the cell colour property, try Range("A1").Interior.Color

# ? Feb 19, 2017 10:17

D34THROW: Jan 29, 2012; RETAIL RETAIL LISTEN TO ME BITCH ABOUT RETAIL

Sad Panda posted:

snip

Are you trying to change the color of a worksheet tab? Try the .ColorIndex property, here's a list.

# ? Feb 20, 2017 16:45

legsarerequired: Dec 31, 2007; College Slice

Does anyone have any recommendations for online lessons where I could learn about indexing using VBA?

Background/tl;dr (seriously, it's long): I'm trying to index data from multiple gigantic workbooks using VBA.

My company does construction services for customers. Our five biggest customers have Excel workbooks with invoice status (i.e., "Invoice rejected, Invoice approved, Invoice in processing, etc"). These workbooks are downloaded from each customer's respective portal.

There is a separate internal workbook "Unpaid Invoices" that just has all the unpaid invoices in our department, for every single customer. Below is a very simplified version of what "Unpaid Invoices" looks like:

As of right now, our admin staff references the individual customer workbooks to fill in Column C for invoice status.

My boss has asked me to write a VBA macro that will index data from five customer workbooks, matching the unpaid invoices in "Unpaid Invoices" with their statuses in the five customer workbooks. This way, individual admins can just run the macro and have the invoice status in "Unpaid Invoices" rather than checking between the customer workbook and "Unpaid Invoices."

So this macro has to do several things:
- It has to identify out which company is named in Column A in our example above
- It has to use that company name to pull up the right workbook
- It has to go to the "Invoice Status" column in the correct company's workbook
- Put the "Invoice Status" in the appropriate column in "Unpaid Invoices"

I initially resolved it by making an INDEX(MATCH()) formula, but Excel was barely able to handle it even before I tried to add IF THEN statements (for identifying which customer is named in column A and then which workbook the macro needs to pull information from).

code:

=INDEX([CUSTOMERA.xlsx]Sheet1!$I$2:$I$61702,MATCH(H27,[CUSTOMERA.xlsx]Sheet1!$D$2:$D61702,0))

I've been nosing around other forums, and I'm really surprised that no one else has needed to index from multiple workbooks.

A few people have suggested some things:
- Using Index(Match()) as a function: Seems impractical because if I try to add IF THEN statements (for identifying the company and pulling the right workbook), Excel crashes. Excel seems barely able to handle just the INDEX(MATCH()) alone, which I wrote out like this:
- My boss says making the five customer workbooks into one workbook is not an option
- My boss says changing the customer workbook formats is not an option

My boss insists it's possible and that he could do it himself but he's too busy.

legsarerequired fucked around with this message at 21:09 on Feb 21, 2017

# ? Feb 21, 2017 21:05

fosborb: Dec 15, 2006; Chronic Good Poster

So some starting points.

I would look at data connections. You can build connections to other worksheets and load them as tables. This may even allow you to not have to download the invoices from your portal, depending on how that is set up.

Your macro should be able to refresh those connections when it runs. Set the connections to Mode=Read;ReadOnly=True so you keep everything static.

Then, just Index(match()) on the local tables rather than the downloaded sheets. This could solve your speed issue right here.

HOWEVER, if it still isn't fast enough, there are still faster ways to accomplish this.

This is the fastest, generic way I know how to find cells, save for compiling a new dll.

code:

Public Function FindCells(TargetRange As Range, Criteria As Variant, Optional VisibleCellsOnly as Boolean = False) As Range
On Error GoTo Err_FindCells

   Dim c As Range
   Dim v As Variant

   If VisisbleCellsOnly = True Then
      Set TargetRangge = TargetRange.SpecialCells(xlCellTypeVisisble)
   End If

   For Each v In Criteria
      For Each c In TargetRange

         If c.Value = v Then

            If Not FindCells Is Nothing Then
               Set FindCells = Union(FindCells, c)
            Else
               Set FindCells = c
            End If
         End If
      Next c
   Next v

Exit_FindCells: 
   Exit Function

Err_FindCells:
   Select Case Err.Number
      Case 1005: Set FindCells = Nothing
      Case Else: MsgBox Err.Number & ": " & Err.Description, vbCritical
   End Select
   GoTo Exit_FindCells

Still use the above data connections to pull down data locally
Grab your Unpaid Invoices as a range and copy to a variant
Cycle through your variant array and use each value in the first column to determine the range, and the second column to determine the criteria of the FindCells function above, rewrite the third column with your results.
Write the variant array back to the range

The trick is to avoid formulas and to avoid writing to cells thousands of times -- store it all in memory and write to the actual workbook only once. And just in general, until you learn more about what triggers calculations and screen updates, it helps to set the following at the beginning of a big macro like this:

code:

With Application
   .EnableEvents = False
   .ScreenUpdating = False
   .Calculation = xlCalculationManual
End With

Be sure to set everything back though (calculation will be xlCalculationAutomatic) at the end of your macro.

# ? Feb 22, 2017 03:59

fosborb: Dec 15, 2006; Chronic Good Poster

Aaaaaand if you really want to start to get anal about vba speed and want to time individual loops with minimal overhead to really nail down what's slowing you down....

Here's the CTimer class module

code:

Option Explicit

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'
'  CODE TIMER (CTimer.mod)
'
'  Class module to time code runs with stupid amounts of accuracy. Adds very little overhead.
'  Useful to time individual code loops to optimize larger processes
'
'  USAGE:
'  Dim CTimerObject as CTimer
'  Set CTimerObject = New CTimer
'
'  CTimerObject.StartCounter    begins counter
'  CTimerObject.LapTime         returns time in ms since start or last lap
'  CTimerObject.TimeElapsed     returns time in ms since StartCounter



Private Type LARGE_INTEGER
   lowpart As Long
   highpart As Long
End Type

Private Declare Function QueryPerformanceCounter Lib "kernel32" (lpPerformanceCount As LARGE_INTEGER) As Long
Private Declare Function QueryPerformanceFrequency Lib "kernel32" (lpFrequency As LARGE_INTEGER) As Long

Private m_CounterStart As LARGE_INTEGER
Private m_CounterEnd As LARGE_INTEGER
Private m_CounterLap As LARGE_INTEGER
Private m_crFrequency As Double

Private Const TWO_32 = 4294967296# 

Private FunctionLI2Double(LI As LARGE_INTEGER) As Double
Dim Low As Double
   Low = LI.lowpart
   If Low < 0 Then
      Low = Low + TWO_32
   End If
   LI2Double = LI.highpart * TWO_32 + Low
End Function

Private Sub Class_Initialize()
Dim PerfFrequency As LARGE_INTEGER
   QueryPerformanceFrequency PerfFrequency
   m_crFrequency = LI2Double(PerfFrequency)
End Sub

Public Sub StartCounter()
   QueryPerformanceCounter m_CounterStart
   QueryPerformanceCounter m_CounterLap
End Sub

Property Get TimeElapsed() As Double
Dim crStart As Double
Dim crStop As Double
   QueryPerformanceCounter m_CounterEnd
   crStart = LI2Double(m_CoutnerStart)
   crStop = LI2Double(m_CounterEnd)
   TimeElapsed = 1000# * (crStop - crStart) / m_crFrequency
End Property

Property Get LapTime() As Double
Dim crStart As Double
Dim crStop As Double
   QueryPerformanceCounter m_CounterEnd
   crStart = LI2Double(m_CounterLap)
   crStop = LI2Double(m_CounterEnd)

   QueryPerformanceCounter m_CounterLap
   LapTime = 1000# * (crStop - crStart) / m_crFrequency
End Property

I would recommend using the above only as a stepping stone to get a better job. If you no poo poo need to time VBA functions to the millisecond to meet expectations, your work either does not invest enough in its tools or its people, and likely both.

# ? Feb 22, 2017 04:17

legsarerequired: Dec 31, 2007; College Slice

Thank you so much for your response! I honestly was just thinking someone was going to recommend a youtube video or something (which would also be amazing, I'm always happy to learn!) but this gives me a lot to look at.

Out of curiosity, does anyone know why googling "indexing with VBA" or similar phrases doesn't bring up many results? I would have thought that my situation would be really widespread and lots of people would be googling it...

# ? Feb 22, 2017 09:08

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

Eh, indexes are a database thing mostly, and VBA is definitely not a database.

# ? Feb 22, 2017 09:13

fosborb: Dec 15, 2006; Chronic Good Poster

Also you're probably going to get a lot of hits just asking about cross workbook reference syntax, which is not your problem. Also also speed usually isn't an issue with these formulas (especially if you have already switched from vlookup to index match). So I'm seeing lots of really one off issues related to buggy code, not questions answered by standard patterns.

Out of curiosity, do the downloaded worksheets end up on a different network location from the unpaid invoices book? And what are the file sizes of the books? Your ranges were only ~60k which should be a cake walk for any, like, non punch card computer.

# ? Feb 22, 2017 14:15

legsarerequired: Dec 31, 2007; College Slice

To clarify, I think these are the issues with my original code being too slow:

- Filtering by customer name and putting in an index(match()) formula for each individual customer was mostly fine, although I notice Excel runs a little slowly when I have those spreadsheets open. The slow-down appeared to start when I tried to add If Then code to the index(match()) so that way the admins wouldn't have to filter by their customer. (i.e., one formula that has an if then linking to each respecting customer's workbook so the person doesn't have to filter down by their customer, and can just use one code to match invoice status from all the workbooks. My boss really wants this in there, but he agreed that the if then was too bulky and advised me to figure it out with VBA)

- My understanding is that each of these five big customers has an online database (for clarification, this is not an Access database, just a portal) with a list of unpaid invoices and their current status. Someone exports the data off the customer portals into an Excel spreadsheet which is kept in a folder in a shared drive that multiple admins can access. I believe each customer may have their own folder in this drive, just to keep things a little cleaner.

The five customer books vary in size, from 60k to 200k. The "Unpaid Invoices" book has all of the lines that are in the five customer books, so it's pretty large.

(examples of possible invoice statuses: Rejected meaning that the customer will not pay the invoice, Approved meaning that the customer is satisfied with our company's work and the payment is on its way to our company, Pending meaning that the customer is currently reviewing the invoice).

Thank you so much for giving me additional terms to google and for your insight goons. I've only ever taken one udemy VBA course. It was extremely helpful, but I was very surprised it didn't have a lot on indexing beyond one lesson. I want to thank everyone again for giving me pointers, and I am absolutely happy to receive additional advice on courses I can take, videos I could watch, terms I could google, etc. I cannot emphasize enough how helpful Something Awful has been in multiple areas of my life.

(as a note, I apologize if it seems like I'm over-explaining everything--sometimes when I read Excel forums, I feel like people don't explain their situation very clearly, so I'm erring on the side of "over-explaining" just in case)

legsarerequired fucked around with this message at 16:26 on Feb 22, 2017

# ? Feb 22, 2017 16:22

fosborb: Dec 15, 2006; Chronic Good Poster

You could also see if there is any difference if you build out 5 hidden columns with each one like =if(company="A",index(match()),"") and the status column is =A1&B1&C1&D1&E1

Hacky, but it sounds like quick and dirty is kind of the expectation here.

# ? Feb 22, 2017 17:47

legsarerequired: Dec 31, 2007; College Slice

I'm sorry if I'm misunderstanding, but I think I tried to add a similar IF THEN code to my initial function (just for two or three companies) and Excel crashed, haha.

I really do appreciate how much you are contributing! You've given me a lot to look over.

EDIT: Also I think I understand your post a little bit better. You're saying that I need to find out how to efficiently have the code do the following:
- find out which company is in column A
- get the data from the right workbook

I'm so sorry that I'm being so slow with this--this is my first time doing any real coding beyond HTML.

legsarerequired fucked around with this message at 18:16 on Feb 22, 2017

# ? Feb 22, 2017 18:12

totalnewbie: Nov 13, 2005; I was born and raised in China, lived in Japan, and now hold a US passport.

I am wrong in every way, all the damn time.

Ask me about my tattoos.

I've got a folder of reports in various file formats (pdf, ppt, etc.) What I want in Excel is:

code:

pdflink | pptlink | filename

That, in and of itself is pretty easy (using URL() )

But there's a few caveats:
1. Files don't always exist in certain formats. Could be PDF + PPT, could be PDF + PPTX, could be just XLS, etc.
2. I want a single column for both PPT or PPTX.
3. I don't want the link to be generated if the file doesn't exist.

I've been able to accomplish all of this by making a custom function using VBA function:

code:

Function FileExists(sFile As String)

Dim sPath As String
    sPath = "[file path here]" & sFile
    FileExists = Dir(sPath) <> ""
End Function

and basically saying if the ppt exists then make url, if not then check if pptx exists, if not then make an empty link (because URL can't just not make a link).

And it's fine! Everything works great! Except that it's rightfully slow as hell. So, I'd like to find an alternate solution.

Is there a more efficient way to do what I want? I could split up all the filetypes into its own column, but I don't think that would actually make it more efficient (probably even less so). Otherwise, I'm kind of stuck. Right now, I'm kind of resigned to just calculating the workbook manually, but if there's some better way, I'd like to know it. TIA

# ? Feb 27, 2017 17:21

fosborb: Dec 15, 2006; Chronic Good Poster

If they're all in the same folder, use the filesystemobject and getFolder + loop through the file objects to build a global array of paths when the workbook opens. Compare against that.

# ? Feb 27, 2017 19:11

totalnewbie: Nov 13, 2005; I was born and raised in China, lived in Japan, and now hold a US passport.

I am wrong in every way, all the damn time.

Ask me about my tattoos.

Thanks, that's a good lead on what I should try. Before I waste too much time, should I try something like this?

First create an array based on how many rows I've got X 4 (pdf, ppt, xls, doc).
- Create filesystemobject.
- Use a for loop and getFolder to check if each filepath exists. Edit: Wait, how do I efficiently pass the filenames from the excel spreadsheet to the script? I guess with a second array?
-- Here, I would check each filepath. I guess I would have a nested loop with the outer one for each row and the inner one for each filetype. In the inner loop, I'd check which column we're in and then check those filetypes accordingly.
- If the file exists then set that value in the array to the filepath I've just checked.

So now I have an array of either filepaths or 0. With that, I can use something like =URL(MATCH(blah),"0").

Have I got the gist of it? I'm more thinking about the logic of what I want to make rather than syntax (I can work that stuff out, eventually).

totalnewbie fucked around with this message at 00:16 on Feb 28, 2017

# ? Feb 27, 2017 23:19

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

If that gets too weird, you might be able to write a PowerShell script that'll output XLS, it'll be faster on the file access side.

# ? Feb 28, 2017 00:45

fosborb: Dec 15, 2006; Chronic Good Poster

Quick and dirty. This only does the first pdf check but it should be easy to see how to check for other file extensions and if your requirements are different this should at least get you started. The following assumes
- all reports are in the same directory
- the report list is on Sheet1
- the report list is formated as a table, named ReportNames

Put this in a new module

code:

Option Explicit

Public Const REPORTS_PATH = "C:\Users\totalnewbie\ReportsFolder"
Public Const SHEET_NAME = "Sheet1"
Public Const TABLE_NAME = "ReportNames"

Private Function FindFileName(ByRef FileNames() As Variant, ByVal FileName As String, ByVal FileExtension As String) As String

    Dim i As Integer
    
    For i = 1 To UBound(FileNames)
        If FileNames(i, 1) = FileName And FileNames(i, 2) = FileExtension Then FindFileName = FileNames(i, 3)
    Next i

End Function


Public Sub BuildFileLinks()

    Dim FileNames() As Variant
    Dim ReportNames As Variant
    Dim fso As FileSystemObject
    Dim fsoFiles As Files
    Dim fsoFile As File
    Dim i As Integer
    Dim SearchResult As String

    Set fso = New FileSystemObject
    
    Set fsoFiles = fso.GetFolder(REPORTS_PATH).Files

    ReDim FileNames(1 To fsoFiles.Count, 1 To 3)
    i = 1
    For Each fsoFile In fsoFiles
        FileNames(i, 1) = fso.GetBaseName(fsoFile.Path)
        FileNames(i, 2) = fso.GetExtensionName(fsoFile.Path)
        FileNames(i, 3) = fsoFile.Path
        i = i + 1
    Next
    
    ReportNames = ThisWorkbook.Sheets(SHEET_NAME).ListObjects(TABLE_NAME).DataBodyRange
    
    For i = LBound(ReportNames, 1) To UBound(ReportNames, 1)
        ReportNames(i, 1) = "": ReportNames(i, 2) = ""
        SearchResult = FindFileName(FileNames, ReportNames(i, 3), "pdf")
        If SearchResult <> "" Then ReportNames(i, 1) = "=HYPERLINK(""" & SearchResult & """,""pdf"")"
    Next i
    
    Range(TABLE_NAME).ClearContents
    Range(TABLE_NAME).Value2 = ReportNames
        
End Sub

And then put this in Sheet1's vba

code:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Set rng = Intersect(Target, Target.Worksheet.ListObjects(TABLE_NAME).DataBodyRange)
    If Not rng Is Nothing Then
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        BuildFileLinks
        Application.ScreenUpdating = True
        Application.EnableEvents = True
    End If

End Sub

fosborb fucked around with this message at 04:51 on Feb 28, 2017

# ? Feb 28, 2017 04:49

totalnewbie: Nov 13, 2005; I was born and raised in China, lived in Japan, and now hold a US passport.

I am wrong in every way, all the damn time.

Ask me about my tattoos.

Daaang I am going to have to look through this. Thanks for going above and beyond.

# ? Feb 28, 2017 06:23

totalnewbie: Nov 13, 2005; I was born and raised in China, lived in Japan, and now hold a US passport.

I am wrong in every way, all the damn time.

Ask me about my tattoos.

Slowly making headway! Got it to work with subfolders and it's generated PDF links nicely.

Though the links are behaving weirdly where sometimes when I click it, it will just go to a "busy" icon for a split second and do nothing. Then I click on it again and it goes through. Nothing I can't work with, but it's weird and makes me twitch because I can't explain it. Anyone heard of something like it?

By the way, I commented out

code:

ReportNames(i, 1) = "": ReportNames(i, 2) = ""

Because it was resetting everything to empty when it went through a folder. It should be safe because the only way it would get overwritten is if I have the same report name in different folders, but this should never happen. My question, though, is why was that code included? I'm not sure what the purpose is, but it seems/feels like one of those "Good programming practice" things.

totalnewbie fucked around with this message at 00:17 on Mar 7, 2017

# ? Mar 7, 2017 00:14

fosborb: Dec 15, 2006; Chronic Good Poster

To remove dead links. If a report was deleted the code as written wouldn't clear out its links. Better way is to just clear both columns as a single range toward the beginning of the function.

Glad it's working out for you otherwise!

# ? Mar 7, 2017 06:56

22 Eargesplitten: Oct 10, 2010

I'm trying to put together a pivot table report with budgets and costs. These should be separate for labor, non-labor, and accounting. The report I'm modelling this off of has that as its own field, but there are labor and non-labor budgets or costs on a single line. How do I split them up so they fit under two different headings?

# ? Mar 9, 2017 20:31

fosborb: Dec 15, 2006; Chronic Good Poster

Curious if there is a good answer to this.

I've always just copy/pasted a second set below the original range of values, which is a pain if I'm normalizing like 6 measures. And if it is supposed to be a routine process, I've used a macro. Also not great.

# ? Mar 9, 2017 20:51

SymmetryrtemmyS: Jul 13, 2013; I got super tired of seeing your avatar throwing those fuckin' glasses around in the astrology thread so I fixed it to a .jpg

22 Eargesplitten posted:

I'm trying to put together a pivot table report with budgets and costs. These should be separate for labor, non-labor, and accounting. The report I'm modelling this off of has that as its own field, but there are labor and non-labor budgets or costs on a single line. How do I split them up so they fit under two different headings?

Would you mind posting the headers and a sample line or two with obfuscated data?

# ? Mar 10, 2017 00:16

totalnewbie: Nov 13, 2005; I was born and raised in China, lived in Japan, and now hold a US passport.

I am wrong in every way, all the damn time.

Ask me about my tattoos.

What the hell is going on O.o

I've got a bug where in my table, if the first line finds a file and thus makes a link, that link is being copied down to every other line in my table.
However, if the first line doesn't find a match then all the rest of the links are being generated properly.

The table is being populated by

code:

    Range(TABLE_NAME).Value2 = ReportNames

So I set a watch on ReportNames and at no point does, for example ReportNames(3, 1) show up as what it ends up being in my table.
i.e. my watch always says ReportNames(3, 1) is either Empty, "", or CORRECT_LINK
However in my table, the link that's generated is LINE_1_LINK (same as every other line).

I have no idea why that's happening.

code:

Option Explicit

Public Const REPORTS_PATH = "ZZZZZZ"
Public Const SHEET_NAME = "Reports"
Public Const TABLE_NAME = "ReportTable"

Private Function FindFileName(ByRef FileNames() As Variant, ByVal FileName As String, ByVal FileExtension As String) As String

    Dim i As Integer
    
    For i = 1 To UBound(FileNames)
        If FileNames(i, 1) = FileName And FileNames(i, 2) = FileExtension Then FindFileName = FileNames(i, 3)
    Next i

End Function

Public Sub NonRecursiveMethod()
    Dim fso, fsoFolder, fsoSubfolder, fsoFile, queue As Collection
    Dim i As Integer, SearchResult As String, FileNames() As Variant, ReportNames As Variant
    Dim fsoFiles As Files

    Set fso = CreateObject("Scripting.FileSystemObject")
    Set queue = New Collection
    queue.Add fso.GetFolder(REPORTS_PATH) 'obviously replace
    
    ReportNames = ThisWorkbook.Sheets(SHEET_NAME).ListObjects(TABLE_NAME).DataBodyRange
    
    For i = LBound(ReportNames, 1) To UBound(ReportNames, 1)
        ReportNames(i, 1) = "": ReportNames(i, 2) = ""
    Next i

    Do While queue.Count > 0
        Set fsoFolder = queue(1)
        queue.Remove 1 'dequeue
        '...insert any folder processing code here...
        For Each fsoSubfolder In fsoFolder.SubFolders
            queue.Add fsoSubfolder 'enqueue
        Next fsoSubfolder
        Set fsoFiles = fso.GetFolder(fsoFolder).Files
        ReDim FileNames(0 To fsoFiles.Count, 1 To 3)
        i = 0
            For Each fsoFile In fsoFolder.Files
                '...insert any file processing code here...
                FileNames(i, 1) = fso.GetBaseName(fsoFile.Path)
                FileNames(i, 2) = fso.GetExtensionName(fsoFile.Path)
                FileNames(i, 3) = fsoFile.Path
                i = i + 1
            Next fsoFile
            
            For i = LBound(ReportNames, 1) To UBound(ReportNames, 1)
                SearchResult = FindFileName(FileNames, ReportNames(i, 17), "pdf")
                If SearchResult <> "" Then ReportNames(i, 1) = "=HYPERLINK(""" & SearchResult & """,""pdf"")"
            Next i
    Loop
    
    Range(TABLE_NAME).ClearContents
    Range(TABLE_NAME).Value2 = ReportNames
    
End Sub

i.e. two possible outcomes:

code:

link_to_pdf1 | pdf1
link_to_pdf1 | pdf2
link_to_pdf1 | pdf3
link_to_pdf1 | pdf4

code:

(no link)    | pdf1
link_to_pdf2 | pdf2
(no link)    | pdf3
link_to_pdf4 | pdf4

totalnewbie fucked around with this message at 00:00 on Mar 11, 2017

# ? Mar 10, 2017 23:58

D34THROW: Jan 29, 2012; RETAIL RETAIL LISTEN TO ME BITCH ABOUT RETAIL

Here's my situation. Sort of as a side project, I'm trying to figure out the relationship between multiplier and margin. I copied the values on the left from a little card my boss had from her old job that listed the multiplier to achieve a certain profit margin.

What I don't understand is why, when there's a clear progression to the data, Excel is making a logarithmic trendline that's totally off.

I'm also wondering, and Google is no help, how to extrapolate the data forward up to a 99% margin (basically 39 data points), and most of what I'm seeing shows Excel best for linear and exponential data.

EDIT: vvv Thank you. Now I feel dumb! I never did great on anything but linear extrapolation in math and I suppose I wouldn't necessarily recognize an absolutely logarithmic curve. You've made my day!

D34THROW fucked around with this message at 19:04 on Mar 16, 2017

# ? Mar 16, 2017 15:56

ShimaTetsuo: Sep 9, 2001; Maximus Quietus

It's because the relationship is not logarithmic. As a function of margin x, the multiplier is 1/(1-x).

# ? Mar 16, 2017 18:04

22 Eargesplitten: Oct 10, 2010

SymmetryrtemmyS posted:

Would you mind posting the headers and a sample line or two with obfuscated data?

Basically I misunderstood what I was supposed to do. He wanted me to use an existing sheet for dummy data, adapting it to the new sheet.

New question: we've got multiple budget lines with the same names on the same cost code, and we want them to show up independently on a pivot table. The thing is we have expenses with the same names that we don't want showing independently. I don't even know how to approach that.

# ? Mar 16, 2017 23:53

Mak0rz: Aug 2, 2008; 😎🐗🚬

~~IF statement question: how do I make one of the output values a blank cell (as opposed to 0) so it doesn't get used in Mean calculations, etc.?~~

Edit: Nevermind, I just did the obvious and wrote "" and that did the trick :v:

# ? Mar 17, 2017 00:24

SymmetryrtemmyS: Jul 13, 2013; I got super tired of seeing your avatar throwing those fuckin' glasses around in the astrology thread so I fixed it to a .jpg

22 Eargesplitten posted:

Basically I misunderstood what I was supposed to do. He wanted me to use an existing sheet for dummy data, adapting it to the new sheet.

New question: we've got multiple budget lines with the same names on the same cost code, and we want them to show up independently on a pivot table. The thing is we have expenses with the same names that we don't want showing independently. I don't even know how to approach that.

I'm pretty sure I have an idea of how to approach what you want to do, but a few lines with the proper fields will help a lot. No promises, but I'll see what I can do if you post a sample.

# ? Mar 17, 2017 02:25

22 Eargesplitten: Oct 10, 2010

Here's an example, phoneposting.

The John Doe lines need to be summed in the pivot table, but the estimate lines need to be separate.

# ? Mar 17, 2017 16:36

SymmetryrtemmyS: Jul 13, 2013; I got super tired of seeing your avatar throwing those fuckin' glasses around in the astrology thread so I fixed it to a .jpg

22 Eargesplitten posted:

Here's an example, phoneposting.

The John Doe lines need to be summed in the pivot table, but the estimate lines need to be separate.

Do all the lines which will need to be summed together have any unique characteristics in a particular column? For example, will all of them have " in the Description field? If not, will the Source always be "Enter estimates" (or something else that's consistent and not shared by non-summing entries)?

If not, do all of the lines which DON'T need to be summed together have any such unique shared characteristics?

If not, is it possible to change the data entry procedure so that they do?

# ? Mar 18, 2017 00:38

fosborb: Dec 15, 2006; Chronic Good Poster

Just append a column that has =row(). Bam, unique values.

# ? Mar 18, 2017 05:28

Adbot: ADBOT LOVES YOU

# ? May 12, 2024 19:16

kumba: Nov 8, 2003; I posted my food for USPOL Thanksgiving!

enjoy the ride; Lipstick Apathy

I've been unexpectedly thrown into a new job role that I wasn't ready for and now I have to analyze data I'm unfamiliar with that is in a really lovely format. I'm hoping someone can nudge me in the right direction here.

I'm an analyst for a call center. I've been tasked with analyzing our adherence data, e.g. how closely someone adheres to their phone schedule. Getting the straight adherence numbers is easy: we just look for how many minutes they were available to take phone calls and divide it by the number of minutes they were scheduled to be available for any given day. Easy peasy.

The trick that I need to learn is to determine, for people who are deviating from their schedule, is the reason for missing the target. This is proving difficult due to how this data is setup. Here's very small snippets:

This is a small snippet of what the adherence system looks like. Each hour is broken up into 15-minute intervals. It's color-coded for what they are scheduled to do at any given moment - in the above, dark blue is scheduled to be on the phone, teal is scheduled for break. The number in the box is the number of minutes spent available, so a 15 in a dark blue box is a perfect score for that period (note it does count seconds, so the 99% adherence below it is because this agent was available for 14.9 minutes out of 15. This is just noise and I don't care about this at all for the purpose of this analysis). At 9:30, the agent missed 3 minutes of their scheduled time.

As you can see, this gives me the numbers but does not give me the why. Here's the other bit of data I need to somehow combine this with:

This gives me exactly what the agent was doing, but the date is only provided once (in the very first row for that day) so matching is proving cumbersome, among other things.

So from this, I can see in the 9:30 - 9:45 block, the agent went unavailable for break at 9:40, and completed his work from the previous call at 9:41:32, so they missed their schedule by 3.5 minutes. I need to log this as 3 minutes missed due to going to break early.

I've setup a bunch of different categories of reasons they might deviate and I've been tallying the number of minutes for each miss as it fits into each category, like so:

(M/P/T/C is shorthand for Meeting/Project/Training/Coaching)

The top 3 is easy enough just using match/index and arrays. The main portion I'm trying to automate is the tallying of the minutes missed in the table, and I'm kind of at a loss as to how. Manually combing through these schedules every month is NOT something I want to do, but I can grin and bear it for a little bit while working on a permanent solution. Excel may not even be the best tool for this, at least by itself. I'm SQL savvy but none of this information is available in a SQL database as far as I know (or at least not one I have access to) so that's no dice.

Has anyone done anything like this that could give me a few pointers, or even a book or something that might help at all? My really basic, high level idea is to essentially build a list of events that could count against adherence (basically anywhere they log on, log off, roll available, or roll unavailable can count depending on their schedule at the time of the event), and then try to do some horrible mess of lookups and subtracting times but I'm having a hard time conceptualizing it. Thanks in advance for any tips.

# ? Apr 5, 2017 15:52

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

«‹›82 »