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

Ninja.Bob: Mar 31, 2005

If you want more control over which rows in one range match to rows (or columns) in another, generally you use the INDEX function. The ROW function will give you your current row number and to compress 10 rows into one just divide by 10 and take the FLOOR or CEILING. Adding and subtracting before the division and after the ceiling allows you to offset the rows in the source and target ranges. In this case using ceiling is easier than floor because it will map 1 - 10 > 1 rather than 0 - 9 > 0 and is generally easier to reason about.

code:

Vendor Name                                                  Account        Amount
=INDEX('Master List'!$A$2:$A$100,CEILING((ROW()-1)/10,1))    Expense 1      $
=INDEX('Master List'!$A$2:$A$100,CEILING((ROW()-1)/10,1))    Expense 2      $$
=INDEX('Master List'!$A$2:$A$100,CEILING((ROW()-1)/10,1))    Expense 3      $$$

In this case the number of header rows in the sheet is 1 (ROW()-1) and the number of times you want each row repeated is 10. If the range you are indexing into includes headers add the number to the ceiling (e.g. 1+CEILING ...). You don't need to provide a column number for the index function but you can if you need it.

# ¿ Dec 30, 2018 05:13

Adbot: ADBOT LOVES YOU

# ¿ May 3, 2024 06:40

Ninja.Bob: Mar 31, 2005

If you aren't exactly sure how the formula works, you can use the Evaluate formula tool under Formulas > Formula Auditing to see it broken down step by step. You understood correctly what I meant by header rows.

If the first row of actual data in the export sheet is row 20 then you would want ROW()-19, as ROW()-19 = 1. If the data in the 'Master List' sheet starts at row 10 then the index range should be 'Master List'!$A10$A$.... So the final formula would be =INDEX('Master List'!$A$10:$A$1000,CEILING((ROW()-19)/10,1)). This will then get the customer name from row 10 in the master list for export rows 20 - 29, 11 > 30 - 39, etc.

If you are getting different results from auto filling up vs down then there is definitely an error. The formula should be identical in each row so make sure that you are using an absolute range that doesn't change as you copy it. The other common source of error is changing the first row but forgetting to update the rest and then copying them as a block to end up with a different formula every 10 rows. This can be hidden in this scenario because it might take a few customers before the error grows large enough for the result to be wrong.

# ¿ Dec 31, 2018 05:26

Ninja.Bob: Mar 31, 2005

Here is a function:

code:

Option Explicit

Public Function ConcatHeaders(headerRow As range, cellRow As range) As String
    Dim i As Integer
    For i = 1 To cellRow.Cells.Count
        If cellRow.Cells(i).Value <> "" Then
            ConcatHeaders = ConcatHeaders + headerRow.Cells(i).Value + ", "
        End If
    Next
    ConcatHeaders = Left(ConcatHeaders, Len(ConcatHeaders) - 2)
End Function

In the vba editor (Alt + F11) create a module (Insert > Module) and paste it in. You can then use it in your workbook like =ConcatHeaders($D$1:$H$1,D2:H2) and copy it down.

# ¿ Mar 24, 2019 07:24

Ninja.Bob: Mar 31, 2005

I don't understand why you need an array formula. Can't you just put your formula in cell B2, point it at A2 and copy it down as many rows as you need?

You can pass a custom sort order to the sort function using this notation {1;5;2;...}

=IF(ISBLANK(A2),"",TRANSPOSE(SORT(IMPORTXML("http://www.omdbapi.com/?t=" & A2 & "&r=xml&apikey=29e7c8aa","//@plot|//@director|//@writer|//@genre|//@actors|//@rated|//@imdbID|//@country|//@year"),{5;8;2;4;7;1;6;3;9},true)))

I added a sheet to that link with how it could work.

# ¿ Jan 6, 2020 04:58

Ninja.Bob: Mar 31, 2005

To do it with standard formulas this is how I would approach it, the padded general comments may be duplicates though but this could be solved using a similar method to the task comments, i.e. ranking and picking top n.

code:

B2:J2  : 1 or 0 for each assessment item
K2     : =COUNTIF(B2:J2,1)   # Number of tasks we could choose to comment on
L2:T2  : =RAND()*B2          # A random value for each completed task, incomplete tasks will be 0.
U2     : =LARGE($L2:$T2,1)   # First comment value
V2     : =LARGE($L2:$T2,2)   # Second comment value
W2     : =LARGE($L2:$T2,3)   # Third comment value
X2     : =IF($K2>0,INDEX(Assessment_comments,MATCH(U2,$L2:$T2,0),1),INDEX(General_Comments,INT(1+RAND()*ROWS(General_Comments))))
Y2     : =IF($K2>1,INDEX(Assessment_comments,MATCH(V2,$L2:$T2,0),1),INDEX(General_Comments,INT(1+RAND()*ROWS(General_Comments))))
Z2     : =IF($K2>2,INDEX(Assessment_comments,MATCH(W2,$L2:$T2,0),1),INDEX(General_Comments,INT(1+RAND()*ROWS(General_Comments))))

If k2 > 0 there is at least one task comment so we get the index of the task with the highest value and then offset by that amount in the task comment table, otherwise there is at least three general comments so return a random comment from the general comments table and so on.

# ¿ Mar 28, 2020 07:16

Ninja.Bob: Mar 31, 2005

Does something like:
=OFFSET(Table1[#Headers],0,3,1,COLUMNS(Table1[#Headers])-3)
do what you need?

I think you'll need to use a named range to use it in a dropdown though.

# ¿ Dec 19, 2020 19:57

Ninja.Bob: Mar 31, 2005

I know this is an old question, but here is an answer anyway. You can use something like this (assuming a recent version of excel):

=TEXTJOIN(", ",TRUE,REPT($A$2:$A$16,ISNUMBER(SEARCH(" "&D2&","," "&$B$2:$B$16&","))))

Where
$A$2:$A$16 are the category labels,
$B$2:$B$16 are the number collections, and
D2 is the number you are searching for.

# ¿ Apr 25, 2021 12:17

Ninja.Bob: Mar 31, 2005

You basically had it. The only things that needed changing are your if statements need an end if when they are more than one line and you need to wrap the formula for c2 in double inverted commas, e.g. r.offset(0,2) = "=B2+15"

You say you want this to work for any new row though, so if you change A to be Range("A:A") then when any cell in column A is edited columns b and c will be automatically filled. I have made the column C value generic so it should just work.

code:

Private Sub Worksheet_Change(ByVal Target As Range)    
  Dim A As Range, B As Range, Inte As Range, r As Range    
  Set A = Range("A2:A2")
  Set Inte = Intersect(A, Target)    
  If Inte Is Nothing Then Exit Sub    
  Application.EnableEvents = False    
  For Each r In Inte
    If r.Offset(0, 1).Value = "" Then
      r.Offset(0, 1).Value = Date
    End If
    If r.Offset(0, 2).Value = "" Then
      r.Offset(0, 2).Value = "=" & r.Offset(0, 1).Address(False, False) & "+15"
    End If
  Next r    
  Application.EnableEvents = True    
End Sub

# ¿ May 19, 2021 18:46

Ninja.Bob: Mar 31, 2005

Using this formula in your conditional formatting will work:

=AND($A2=$D$2,$B2=$E$2)

# ¿ Jul 22, 2021 11:52

Ninja.Bob: Mar 31, 2005

I would start with something like =left(b2, len(b2)/4)

If the data is more complicated than that I would find the point where the first line is repeated again (i.e. a new line followed by the first line of text) and extract the lines before that.

=LEFT(B2, FIND(CHAR(10)&LEFT(B2,FIND(CHAR(10),B2)),B2)-1)

Also it's ctrl+J not alt+J for a new line character, so that may have been the issue.

# ¿ Oct 14, 2022 03:59

Ninja.Bob: Mar 31, 2005

With the various updates and suggestions, I'm not sure what your code looks like now, but this code should work, just update the const strings as required. Also make sure that the cell is not being updated when the pivot table changes size when the filter updates.

I suggest for this function, if an error is thrown then the best thing to do is just quit. This could be because the sheet / table / field doesn't exist in which case you need to fix your code, or the user has entered an invalid value. If the user enters something invalid then the function will clear the filter but not be able to set it again. Depending on your users and use case this might be fine or you might want to add something to let them know.

Visual Basic .NET code:

Private Sub Worksheet_Change(ByVal target As Range)
On Error GoTo ErrorHandler

    Const filterCellName As String = "B2"
    Const worksheetName As String = "Sheet1"
    Const pivotTableName As String = "PivotTable1"
    Const pivotFilterFieldName As String = "COL A"
    
    If Not Intersect(target, Range(filterCellName)) Is Nothing Then
        With Worksheets(worksheetName).PivotTables(pivotTableName).PivotFields(pivotFilterFieldName)
            .ClearAllFilters
            .CurrentPage = Range(filterCellName).Value
        End With
    End If
    
    Exit Sub

ErrorHandler:
    'notify users of an error here.

End Sub

A few caveats if you want to notify your users:

Check if Range(filterCellName).Value = "" and set .CurrentPage to "(All)" instead, otherwise you'll throw an error when you really don't need to.
If you want to update a cell to notify your users you may run into an infinite loop of worksheet_change events. Let us know if this is your plan and we can provide more advice.

# ¿ Nov 18, 2022 10:37

Ninja.Bob: Mar 31, 2005

Searching for that error message seems to indicate that Excel can't find the field you are looking for in the pivot table. Check to make sure your the pivotFilterFieldName is correct, I think it should be something like this based on your previous post.

Visual Basic .NET code:

Const filterCellName As String = "B2"
Const worksheetName As String = "Sheet1"
Const pivotTableName As String = "Depots"
Const pivotFilterFieldName As String = "Depot Filter"

# ¿ Nov 18, 2022 23:55

Ninja.Bob: Mar 31, 2005

I understand this can be frustrating and not a worthwhile time investment, but given the error you had: "Unable to get the PivotFields property of the PivotTable class" you are very close to a working solution and it's probably something really simple like a trailing space or something similar.

You can check for trailing spaces, etc., with a formula in your spreadsheet e.g. =""""&A2&"""" (cell reference based on your earlier screen clip, it should point to the filter name cell in your pivot table) and then copy and pasting the field name wrapped in double inverted commas into your vba as the pivotFilterFieldName. Alternatively, if you run the PrintPivotFields macro below (in the vba editor, click the green arrow, hit F5, or Run > Run, and then select the PrintPivotFields macro) and it will print out all the pivot table filter fields in the current file to the vba immediate window (View > Immediate Window), formatted so you can copy and paste the correct set of lines straight into the original function (also included for reference).

If you try these solutions and still have no success then, while I'm still happy to help, I understand if you don't want to spend any more time on it.

Visual Basic .NET code:

Private Sub Worksheet_Change(ByVal target As Range)
On Error GoTo ErrorHandler

    Const filterCellName As String = "B1"
    Const worksheetName As String = "Sheet1"
    Const pivotTableName As String = "Depots"
    Const pivotFilterFieldName As String = "DEPOT FILTER"
        
    If target.Cells.Count = 1 And Not Intersect(target, Range(filterCellName)) Is Nothing Then
        With Worksheets(worksheetName).PivotTables(pivotTableName).PivotFields(pivotFilterFieldName)
            .ClearAllFilters
            .CurrentPage = Range(filterCellName).Value
        End With
    End If
    
    Exit Sub
ErrorHandler:
    'notify users of an error here.

End Sub

Sub PrintPivotFields()
    
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim found As Boolean
    
    found = False
    For Each ws In Worksheets
        For Each pt In ws.PivotTables
            For Each pf In pt.PageFields
                found = True
                Debug.Print "----------------------"
                Debug.Print "    Const worksheetName As String = """ & ws.Name & """"
                Debug.Print "    Const pivotTableName As String = """ & pt.Name & """"
                Debug.Print "    Const pivotFilterFieldName As String = """ & pf.Name & """"
            Next
        Next
    Next
    
    If Not found Then
        Debug.Print "No page (filter) fields found"
    End If
    
End Sub

# ¿ Nov 25, 2022 12:54

Ninja.Bob: Mar 31, 2005

Use an XLOOKUP, assuming your table is from a1 to f5 the formula would be:

=XLOOKUP(A2,$C$1:$F$1,C2:F2,B2,0,1)

which matches the estimated grade against the column headers and returns the ungraded column if it's not found.

# ¿ Jan 5, 2023 14:04

Ninja.Bob: Mar 31, 2005

Trivia posted:

I hope what I've written makes sense. If any of you can tell me how to do it better or just point me in the right direction code-wise I'm sure I will eventually figure it out.

A couple of suggestions:

Use a javascript array to hold your variables rather than another sheet.
Try to do all your updating at once, rather than each cell one at a time. Apps script makes a call for each cell read or write, so if you can bundle it into a single setValues() instead of multiple setValue() it will be much quicker.
Use a custom number format for your items so they are numbers rather than strings: Format > Number > Custom Number Format > 0 "(hours)" / 0 "(days)" . This will make working with the cell data much easier.

Given your template, this is how I would approach it. I would define an array that contains the areas I am interested in for each boat. This gives you flexibility if you have boats with different numbers of engines or items, or you just want to change the sheet formatting. Then when a cell is updated, check to see if it's a current date cell for one of the boats and update that boat by reading and then writing all of its values at once.

JavaScript code:

const boats = [
  {
    "currentHours": "C3",
    "currentDate": "D3",
    "lastRecordedHours": "C4",
    "lastRecordedDate": "D4",
    "items": "B8:D53"
  },
  {
    "currentHours": "G3",
    "currentDate": "H3",
    "lastRecordedHours": "G4",
    "lastRecordedDate": "H4",
    "items": "F8:H53"
  },
];

// easy way to log errors as the execution log is unreliable for onEdit 
function log(spreadsheet, message) {
  const logSheet = spreadsheet.getSheetByName("Errors") || spreadsheet.insertSheet("Errors");
  logSheet.appendRow([new Date(), message] );
}

function onEdit(e) {
  const range = e.range;
  const editedCell = range.getA1Notation();
  const sheet = range.getSheet();

  try {
    
    boats.forEach(boat => {
      if(editedCell === boat.currentDate) {
        //calc deltas
        const currHours = sheet.getRange(boat.currentHours).getValue();
        const prevHours = sheet.getRange(boat.lastRecordedHours).getValue();
        const hours = currHours - prevHours;

        const currDate = sheet.getRange(boat.currentDate).getValue();
        const prevDate = sheet.getRange(boat.lastRecordedDate).getValue();
        const days = (currDate - prevDate) / 86400000; // milliseconds in 1 day
        
        // update last recorded fields and reset current fields
        sheet.getRange(boat.lastRecordedHours).setValue(currHours);
        sheet.getRange(boat.lastRecordedDate).setValue(currDate);
        sheet.getRange(boat.currentHours).setValue("");
        sheet.getRange(boat.currentDate).setValue("");

        // update the cells in the boats items.
        // collect all the values and set them all at once.
        const itemRange = sheet.getRange(boat.items);        
        const values = itemRange.getValues();
        
        for(let r = 0; r < values.length;r+=2) {
          for(let c = 0; c < values[r].length; c+=1) {
            values[r][c] += hours;
            values[r+1][c] += days;
          }
        }
        itemRange.setValues(values);
      }

    });
  } 
  catch (error) {
    log(e.range.getSheet().getParent(), error.message);
  }
}

# ¿ Feb 19, 2023 07:39

Ninja.Bob: Mar 31, 2005

HootTheOwl posted:

I think you deleted the parenthesis around your method calls in the bottom block.
...FormatConditions.Delete()
Selection.FormatConditions.Add(xlExpression, , "TRUE")

VBA has two different syntaxes for function calls depending on if they are on a line by themselves or not. Adding parenthesis would change the meaning of the program, see: https://stackoverflow.com/a/15519085/3317913

AG3 posted:

I have a slightly modified piece of VBA code I got from google that I've been using for one of my Excel sheets at work, but for some reason I can't get it to work on the Excel version on my other PC. It goes like this:

Another thing to check would be that the code is in the correct worksheet object.
Given you mention changing locale settings, if your language is not English then you may need to change

Visual Basic .NET code:

Selection.FormatConditions.Add xlExpression, , "TRUE"
'e.g. german
Selection.FormatConditions.Add xlExpression, , "WAHR"

# ¿ Nov 15, 2023 00:39

Ninja.Bob: Mar 31, 2005

Ideal Paradigm posted:

Is there a way to filter a set of values from one sheet and apply it to two other sheets in the same workbook?

Yes, but the only method I know uses vba. There may be better solutions than this.

I've assumed that there are 3 sheets each with a table, i.e. Sheet1 > Table1, Sheet2 > Table2, Sheet3 > Table3, and each table has a column named ID.

Firstly, you want to be able to catch an event that fires when the source table's (Sheet3) filter updates. Excel doesn't have a built in event for this, so create a new sheet (Sheet4) that has a single formula in it =SUBTOTAL(2,Table3[ID]) . When Sheet3's table is filtered this formula gets recalculated. This allows us to use Sheet4's Worksheet_Calculate() method to handle the filter update. You can hide this sheet after setting it up.

The rest is just getting the filtered ids from Sheet3 and applying them to sheets 1 & 2. In the vba editor add this code to the Sheet4 object, and update the constant values to suit your object names:

Visual Basic .NET code:

Option Explicit

' source table
Const sSheet As String = "Sheet3"
Const sTable As String = "Table3"
Const sColumn As String = "ID"

' target table 1
Const t1Sheet As String = "Sheet1"
Const t1Table As String = "Table1"
Const t1Column As String = "ID"

' target table 2
Const t2Sheet As String = "Sheet2"
Const t2Table As String = "Table2"
Const t2Column As String = "ID"


' run when Sheet4 is calculated i.e. when Table3 is filtered.
Private Sub Worksheet_Calculate()
    
    ' get an array of filtered ids from table 3.
    Dim ids() As String
    ids = GetFilteredIds
    
    'apply the filter to other tables
    ThisWorkbook.Worksheets(t1Sheet).ListObjects(t1Table).ListColumns(t1Column).DataBodyRange.AutoFilter 1, ids, xlFilterValues
    ThisWorkbook.Worksheets(t2Sheet).ListObjects(t2Table).ListColumns(t2Column).DataBodyRange.AutoFilter 1, ids, xlFilterValues
    
End Sub

Private Function GetFilteredIds() As Variant
    
    Dim sourceRange As Range
    Set sourceRange = ThisWorkbook.Worksheets(sSheet).ListObjects(sTable).ListColumns(sColumn).DataBodyRange
    
    Dim ids() As String
    ReDim ids(0 To sourceRange.Count)
        
    Dim n As Long
    n = 0
    
    Dim c As Range
    For Each c In sourceRange
        If Not c.EntireRow.Hidden Then
            ids(n) = c.Value
            n = n + 1
        End If
    Next
    
    ReDim Preserve ids(0 To n - 1)
    GetFilteredIds = ids

End Function

# ¿ Nov 23, 2023 06:44

Ninja.Bob: Mar 31, 2005

HootTheOwl posted:

That's not vb.net code!

But it does have pretty colours

. We need to go on strike until vba code styling is added.

# ¿ Nov 23, 2023 12:05

Ninja.Bob: Mar 31, 2005

HootTheOwl posted:

So the ID numbers do match just some of them don't have a mate in the other sheet?
Is this what you're after:.
If, an ID in sheet 1 or 2 is present in sheet 3
Then, keep the row
Otherwise, you want the row filtered out and removed?

This is how I interpreted the question. I also thought that the Sheet3 values would have their own filter applied and updated, hence the VBA solution. Ideal Paradigm: if you don't need to filter Sheet3 then go with someone else's solution. It's also worth noting that my solution will need to be tweaked slightly to work on mac.

Ideal Paradigm posted:

Apologies, I'm not familiar with the VB code. Should I look this up on YouTube on how to input this?

To add the code:
Open the VBA editor using the Alt + F11 shortcut, or via the ribbon in the developer tab > Visual Basic. The developer tab is hidden by default, so you may need to show it by: right clicking the ribbon in an empty space, selecting customise the ribbon, and checking developer in the right hand panel.
In the VBA editor in the left hand side project panel there will be a project that correlates with your workbook with a number of objects nested under it, one for each sheet and another for the workbook as a whole. The code I posted needs to go inside the Sheet4 object (assuming that is the new sheet with the subtotal formula), so double click the object and paste in the code in the main window.

The code will run whenever Sheet4 is recalculated, so assuming you have added the formula to Sheet4 as previously mentioned, updating the filter on Sheet3 will run the code and filter sheets 1 and 2.

# ¿ Nov 24, 2023 03:05

Adbot: ADBOT LOVES YOU

# ¿ May 3, 2024 06:40

Ninja.Bob: Mar 31, 2005

It's probably easiest to use a regular expression replace. You'll need to add a reference to 'Microsoft VBScript Regular Expressions 5.5' in the vba editor menu Tools > References.

Visual Basic .NET code:

Dim reg As New RegExp
reg.Pattern = "[0-9]+$"

For Each tbl In ActiveSheet.ListObjects
    tbl.Name = reg.Replace(tbl.Name, "") & shortMonth
Next tbl

# ¿ Apr 10, 2024 09:29

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