Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0613· OpenAIAi2 WildChat-4.8M (AI2)

How can I do this in excel. From O206 search column O upwards for the first value in…

  1. User

    How can I do this in excel. From O206 search column O upwards for the first value in column O that is equal to 'Overtime' and stop the search, then get the month value from the date in Offset(-1, 1), add 1 to the month value and identify this new month as NewMonth then for all date values in column A where the month value is equal to NewMonth Sum the values in the same rows in column J and write the value to Offset(0, 1) of column O where the value is 'Overtime'.

    From O206 search column O upwards for the first value in column O that is equal to 'Absence' and stop the search, then get the month value from the date in Offset(-2, 1), add 1 to the month value and identify this new month as NewMonth then for all date values in column A where the month value is equal to NewMonth Sum the values in the same rows in column L and write the value to Offset(0, 1) of column O where the value is 'Absence'.

    From O206 search column O upwards for the first value in column O that is equal to 'Holiday' and stop the search, then get the month value from the date in Offset(-3, 1), add 1 to the month value and identify this new month as NewMonth then for all date values in column A where the month value is equal to NewMonth Sum the values in the same rows in column N and write the value to Offset(0, 1) of column O where the value is 'Holiday'.

  2. Assistant

    To accomplish this task in Excel, you can use a combination of formulas and VBA code. Here's a step-by-step guide:

    Step 1: To find the first occurrence of a specific value in Column O, you can use the MATCH function. In cell O206, enter the following formula: =ROW()-1-MATCH("Overtime",OFFSET(O206,0,0,-COUNTA(O:O),1),0)

    This formula will return the row number of the first occurrence of "Overtime" in Column O.

    Step 2: To get the month value from the date in the row above the found value in Column O, use the formula: =MONTH(OFFSET(O206,-1,1))

    Step 3: Add 1 to the month value and identify it as NewMonth. In a separate cell, enter the formula: =MONTH(OFFSET(O206,-1,1))+1

    Step 4: To sum the values in Column J where the month value in Column A matches the NewMonth value, use the formula: =SUMIFS(J:J,MONTH(A:A),EE1)

    Here, EE1 is the cell where the NewMonth value is stored.

    Step 5: Write the summed value to Offset(0,1) of Column O where the value is 'Overtime'. In a separate cell, enter the formula: =IF(O206="Overtime",SUMIFS(J:J,MONTH(A:A),EE1),"")

    This formula will check if the value in O206 is 'Overtime' and if so, return the summed value calculated in Step 4. Otherwise, it will return an empty string.

    Step 6: Repeat the steps above for 'Absence' and 'Holiday', replacing 'Overtime' with 'Absence' or 'Holiday' in the formulas.

    Step 7: Finally, you can use VBA code to automate this process and apply it to multiple cells in Column O. Here's an example of the code:

    Sub SumValuesByMonth()
        Dim lastRow As Long
        Dim i As Long
        Dim rng As Range
        Dim monthValue As Integer
        Dim newMonth As Integer
        
        lastRow = Cells(Rows.Count, 15).End(xlUp).Row ' Assuming Column O is Column 15
        
        For i = lastRow To 206 Step -1
            If Cells(i, 15).Value = "Overtime" Then
                monthValue = Month(Cells(i - 1, 16).Value) ' Assuming Column A is Column 16
                newMonth = monthValue + 1
                Set rng = Range("A206:A" & lastRow) ' Assuming Column A is Column 1
                Cells(i, 15).Offset(0, 1).Value = WorksheetFunction.SumIfs(Cells(1, 10).Resize(lastRow, 1), rng, newMonth)
            
            ElseIf Cells(i, 15).Value = "Absence" Then
                monthValue = Month(Cells(i - 2, 16).Value) ' Assuming Column A is Column 16
                newMonth = monthValue + 1
                Set rng = Range("A206:A" & lastRow) ' Assuming Column A is Column 1
                Cells(i, 15).Offset(0, 1).Value = WorksheetFunction.SumIfs(Cells(1, 12).Resize(lastRow, 1), rng, newMonth)
            
            ElseIf Cells(i, 15).Value = "Holiday" Then 
                monthValue = Month(Cells(i - 3, 16).Value) ' Assuming Column A is Column 16
                newMonth = monthValue + 1
                Set rng = Range("A206:A" & lastRow) ' Assuming Column A is Column 1
                Cells(i, 15).Offset(0, 1).Value = WorksheetFunction.SumIfs(Cells(1, 14).Resize(lastRow, 1), rng, newMonth)
            
            End If
        Next i
        
    End Sub
    

    To use this code, press ALT+F11 to open the VBA editor, insert a new module, and paste the code into the module. Then, run the macro by pressing ALT+F8, selecting "SumValuesByMonth," and clicking "Run." The code will loop through the specified range and apply the calculations based on the criteria provided.

    Note: Adjust the column references in the VBA code according to your specific worksheet layout.

  3. User

    =ROW()-1-MATCH(“Overtime”,OFFSET(O206,0,0,-COUNTA(O:O),1),0) the formula returned #NA

  4. Assistant

    The formula might return #NA if there is no "Overtime" value found in the range being searched. Here are a few troubleshooting steps you could try:

    1. Make sure you are entering the formula correctly, including the quotation marks for "Overtime". Double-check for any typos or extra spaces.

    2. Verify that you are applying the formula in cell O206 or any cell in the same row or below. If you apply the formula in a cell above O206, it will return #NA because there will be no rows above to search.

    3. Ensure that there is at least one occurrence of "Overtime" in Column O above the cell where the formula is applied. If there are no instances of "Overtime", the formula will not find a match and return #NA.

    4. Check the data in Column O to confirm that the values are stored as text and not as numbers or dates. The MATCH function requires an exact match, so the data type should match as well.

    If none of these solutions solve the issue, please provide more information about your data and share a sample of the worksheet so that I can assist you further.

  5. User

    How can I share my worksheet with you so that you can help me better

  6. Assistant

    I'm sorry, but as a text-based AI, I am unable to directly interact with or access files or external resources. However, I can still try my best to help you troubleshoot or provide guidance based on the information you provide.

    If you have a specific question or issue related to your worksheet, please describe it in detail, including any error messages or unexpected results you are encountering. I'll do my best to assist you based on the information provided.

  7. User

    =ROW()-1-MATCH(“Overtime”,OFFSET(O206,0,0,-COUNTA(O:O),1),0) the formula returned #NA even though all conditions are met

  8. Assistant

    I apologize for any confusion earlier. It seems that the formula is not working as expected because of the use of curly quotation marks (" “ ” "). Excel requires straight quotation marks (" ") around the search criteria. Please try replacing the curly quotation marks with straight quotation marks in the formula.

    The corrected formula should look like this:

    =ROW()-1-MATCH("Overtime",OFFSET(O206,0,0,-COUNTA(O:O),1),0)

    By using straight quotation marks, the formula should return the correct result.

  9. User

    I change the quotation marks and it still returned ~NA

  10. Assistant

    I apologize for the confusion. The previous formula attempts to search for "Overtime" in a column range above the current cell, and it may not be suitable for your specific worksheet.

    To achieve your desired result, you can utilize VBA code to accomplish the task. Here is a modified VBA code that implements the steps you described:

    Sub CalculateValues()
        Dim lastRow As Long
        Dim i As Long
        Dim monthValue As Integer
        Dim newMonth As Integer
        Dim rng As Range
    
        lastRow = Cells(Rows.Count, "O").End(xlUp).Row
        
        For i = 206 To lastRow
            If Cells(i, "O").Value = "Overtime" Then
                monthValue = Month(Cells(i - 1, "P").Value)
                newMonth = monthValue + 1
                Set rng = Range("A206:A" & lastRow)
                Cells(i, "O").Offset(0, 1).Value = WorksheetFunction.SumIfs(Cells(1, "J").Resize(lastRow, 1), rng, newMonth)
                Exit For
            End If
        Next i
        
        For i = 206 To lastRow
            If Cells(i, "O").Value = "Absence" Then
                monthValue = Month(Cells(i - 2, "P").Value)
                newMonth = monthValue + 1
                Set rng = Range("A206:A" & lastRow)
                Cells(i, "O").Offset(0, 1).Value = WorksheetFunction.SumIfs(Cells(1, "L").Resize(lastRow, 1), rng, newMonth)
                Exit For
            End If
        Next i
        
        For i = 206 To lastRow
            If Cells(i, "O").Value = "Holiday" Then
                monthValue = Month(Cells(i - 3, "P").Value)
                newMonth = monthValue + 1
                Set rng = Range("A206:A" & lastRow)
                Cells(i, "O").Offset(0, 1).Value = WorksheetFunction.SumIfs(Cells(1, "N").Resize(lastRow, 1), rng, newMonth)
                Exit For
            End If
        Next i
    End Sub
    

    To use this code, follow these steps:

    1. Open your Excel worksheet.
    2. Press ALT + F11 to open the VBA Editor.
    3. Insert a new module by clicking on Insert and select Module.
    4. Copy and paste the provided code into the new module.
    5. Press F5 or click Run to execute the code.

    Please note that this code assumes your data starts from row 206 and the data you want to sum is in columns J, L, and N. If your data is located in different columns or you have different starting rows, you will need to modify the code accordingly.

    Make sure to save your workbook as a macro-enabled file format (.xlsm) before running VBA code. This code will iterate through the specified range and perform the calculations based on the given conditions. The results will be written to column P for Overtime, Absence, and Holiday values in column O.

    Hopefully, this updated VBA code resolves the issue, and please let me know if you need any further assistance!

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.