Seemanto Saha

About author

Seemanto Saha graduated in Industrial and Production Engineering from Bangladesh University of Engineering and Technology. He has been with ExcelDemy for a year, where he wrote 40+ articles and reviewed 50+ articles. He has also worked on the ExcelDemy Forum and solved 50+ user problems. Currently, he is working as a team leader for ExcelDemy. His role is to guide his team to write reader-friendly content. His interests are Advanced Excel, Data Analysis, Charts & Dashboards, Power Query, and Excel VBA.

Designation

Team Leader at ExcelDemy in SOFTEKO.

Lives in

Dhaka, Bangladesh.

Education

B.Sc. in Industrial and Production Engineering (IPE), Bangladesh University of Engineering and Technology.

Expertise

Visual Basic for Applications (VBA), Data Analysis, Content Writing, Charts and Dashboards, Python, Google Apps Script, C, HTML, Microsoft Office, SolidWorks, CATIA, AutoCAD, ARENA.

Experience

  • Technical Content Writing
  • Team Management
  • Industrial Management Trainee at Coats Bangladesh
    • Time and Motion Study
    • Quality Control Management
    • Operations Optimization Study
  • Undergraduate Projects
    • Product Design and Development: Trash Compactor
    • Product Design: Stair-Climbing Weight Lifting Machine
    • Computer Aided Drafting:
      • Boston Dynamics Atlas Robots (Using CATIA)
      • Canterbury Cathedral (Using SolidWorks)

Summary

  • Currently working as Team Leader of ExcelDemy.
  • Started technical content writing of Excel & VBA for ExcelDemy in March 2023.
  • Started technical content writing of Google Sheets for OfficWheel in November 2022.
  • Worked as an Industrial Management Trainee at Coats Bangladesh.

Research & Publication

Latest Posts From Seemanto Saha

0
Excel Data for Analysis (Free Download 11 Suitable Datasets)

Excel is one of the most extensively used tools in data analysis. With proper data for analysis, various features, functions, and user-friendly interface of ...

1
Excel Sample Data (Free Download 13 Sample Datasets)

Sample data in Excel refers to datasets used for practice purposes. While learning various Excel features and functions, or performing data analysis in Excel, ...

0
Calculate Percentage with Criteria in Excel (2 Useful Examples)

In this Excel tutorial, you will learn 2 useful examples of calculating percentage with criteria. We will discuss how to use the IF function to calculate ...

0
Excel Average Formula Examples

In this Excel tutorial, you will learn various examples of how to apply formula to calculate average in Excel. We will discuss how to calculate the average ...

0
Conditional Average in Excel (Complete Guide)

In this Excel tutorial, we will learn how to calculate conditional average in Excel. We can use the AVERAGEIF or AVERAGEIFS function for this purpose. ...

0
Create Folder in Excel (Step-by-Step Guide)

Create Folder in Excel: Knowledge Hub Create Multiple Folders at Once from Excel Create Outlook Folders from Excel List Create Files from Excel List ...

0
How to Paste in Excel (Ultimate Guide)

In this article, we will present various examples of how to paste in Excel. These examples will demonstrate everything, from the process of applying basic ...

0
Excel Linear Programming (Using Solver and Graphical Methods)

Are you interested in learning how to solve Linear Programming in Excel? Linear programming has numerous applications in various fields such as allocating ...

0
Worksheet Formatting (Ultimate Guide)

Worksheet formatting in Excel involves changing the font style, fill color, border style, adjusting row height, column width, number formats, etc. of a sheet. ...

0
Excel Protect

Excel Protect: Knowledge Hub How to Protect Cells in Excel How to Protect Columns in Excel Protect Workbook in Excel Encryption in ...

0
Excel for Statistics (Functions, Tools and Examples)

In this article, we will present 111 Excel functions for statistics and 10 practical examples to apply some of these functions. We will also discuss the 2 most ...

0
Workbook Views in Excel (Everything You Need to Know)

Workbook Views in Excel: Knowledge Hub Show Only One Page in Excel Page Layout View How to Show Ruler in Excel Excel Ruler Not Showing ...

0
Workbook in Excel (Ultimate Guide)

In order to organize our data, we use a workbook in Excel. If you want to know what is a workbook in Excel, I will recommend you to go through the whole ...

0
Export Excel to txt (Ultimate Guide)

In this article, we will discuss how to export Excel to txt (or .txt) files. We will present the process to export a single sheet (active sheet) or multiple ...

0
Hide and Unhide Sheets in Excel (All Thing You Need to Know)

Hide and Unhide Sheets in Excel: Knowledge Hub How to Hide and Unhide Excel Worksheets from a Workbook How to Unhide Very Hidden Sheets in Excel ...

Browsing All Comments By: Seemanto Saha
  1. Dear CARLO MUNDAN,
    I hope you are doing well and thanks for your query.

    Assuming the marks of three subjects are in the range C5 to E5, the result will be shown in cell F5.
    The following formula gets your desired answer.
    =IF(COUNTIF(C5:E5, “>=70”)=3, “Pass”, IF(COUNTIF(C5:E5, “>=70”)>=1, “Try again”, “Fail”))

    Using COUNTIF and Nested IF Functions to Show Pass, Try Again, or Fail Output

    Here, I have used two COUNTIF functions along with an IF function inside an IF function to get the result you desired. Drag the formula down to apply it to the rest of the cells.

    If you have any more queries, please let us know in the comments.

    Regards
    Team ExcelDemy

  2. Hello Jan,
    Thanks for sharing your problem with us. I understand that you want to import data from an authenticated Google Spreadsheet to Excel.

    This is a complex method and requires several steps. Since Google Sheets are authenticated using Google Sheets APIs, you have to collect some information like client_id, client_secret, target spreadsheet ID, target spreadsheet name, and the range to be imported.

    Here is a step-by-step process:

    Step 1: Go to Google Cloud Console and select the target project (i.e. the project used for authenticating the required Google Spreadsheet)

    Step 2: Make sure the Google Sheets API is enabled. Navigate the following directory.
    APIs & Services >> Library

    Step 3: Create an OAuth 2.0 Client ID using the following sub-steps.
    Step 3.1: Go to the directory APIs & Services >> Credentials.
    Step 3.2: Click the Create credentials button and select OAuth client ID.
    Step 3.3: Set the Application type to Desktop App.
    Step 3.4: Enter a name for the Application and click the Create button.

    This will create a JSON file containing your client ID and client secret. Download the file and open it using VB.net or any other suitable application.

    Step 4: Go to the target Excel workbook and open Visual Basic Editor using the keyboard shortcut Alt + F11. Insert a Module and enable the following 3 libraries from Tools >> References directory.

    1) Microsoft Scripting Runtime
    2) Microsoft XML, v6.0
    3) Microsoft VBScript Regular Expressions 5.5

    Step 5: Insert the following VBA code and make necessary adjustments (change the spreadsheet ID, client_id, client_secret, sheet name, required range, etc.)

    Excel VBA Code

    
    Sub ImportDataFromGoogleSheetToExcel()
        Dim spreadsheetId As String
        Dim rangeName As String
        Dim oAuthApp As Object
    
        ' Define your Google Sheet details
        spreadsheetId = "1Xz_LyRk0n81VqDjiJYNBMwR1lGSBmm213JtQxK5HRnQ" ' Replace with your Google Sheets document ID
        rangeName = "TestSheet!A1:C10" ' Change to your desired range
    
        ' Initialize the Google API client
        Set oAuthApp = CreateObject("Scripting.Dictionary")
    
        ' Configure OAuth2 client
        oAuthApp("client_id") = "304250172616-5ddsaumijhuigr05t8smc1prfqr4m60g.apps.googleusercontent.com"
        oAuthApp("client_secret") = "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"
        oAuthApp("auth_uri") = "https://accounts.google.com/o/oauth2/auth"
        oAuthApp("token_uri") = "https://oauth2.googleapis.com/token"
        oAuthApp("scope") = "https://www.googleapis.com/auth/spreadsheets.readonly"
    
        ' Wait for the user to authenticate manually (adjust the waiting time as needed)
        Application.Wait Now + TimeValue("00:00:10") ' Wait for 10 seconds
    
        ' Call the Google Sheets API to get data
        Call ImportDataFromGoogleSheets(oAuthApp, spreadsheetId, rangeName)
    End Sub
    
    Sub ImportDataFromGoogleSheets(oAuthApp, spreadsheetId, rangeName)
        ' Create a Google Sheets service instance
        Dim service As Object
        Set service = CreateObject("Scripting.Dictionary")
        service("spreadsheetId") = spreadsheetId
        service("range") = rangeName
    
        ' Use your OAuth2 client details
        Set service("oauth_client") = oAuthApp
    
        ' Authenticate with Google Sheets
        Call AuthenticateWithGoogleSheets(service)
    
        ' Check if the authentication was successful
        If Not service("sheets_service") Is Nothing Then
            ' Fetch data from Google Sheets
            Call GetDataFromGoogleSheets(service)
        End If
    End Sub
    
    
    Sub AuthenticateWithGoogleSheets(service)
        Dim oAuthClient As Object
        Set oAuthClient = CreateObject("MSXML2.ServerXMLHTTP.6.0")
    
        ' Use your OAuth2 client details
        Dim oAuthApp As Object
        Set oAuthApp = service("oauth_client")
    
        ' Authenticate with Google Sheets using OAuth2
        Dim authUrl As String
        authUrl = oAuthApp("auth_uri") & "?client_id=" & oAuthApp("client_id") & "&redirect_uri=urn:ietf:wg:oauth:2.0:oob&scope=" & oAuthApp("scope") & "&response_type=code"
        Call OpenDefaultBrowser(authUrl)
    
        ' Wait for the user to authenticate manually
        Application.Wait Now + TimeValue("00:00:10") ' Wait for 10 seconds
    
        ' Check if the user is authenticated
        Dim authCode As String
        authCode = InputBox("Enter the authorization code: ")
    
        ' Exchange the authorization code for an access token
        Dim postData As String
        postData = "code=" & authCode & "&client_id=" & oAuthApp("client_id") & "&client_secret=" & oAuthApp("client_secret") & "&redirect_uri=urn:ietf:wg:oauth:2.0:oob&grant_type=authorization_code"
    
        oAuthClient.Open "POST", oAuthApp("token_uri"), False
        oAuthClient.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
        oAuthClient.send postData
    
        Dim responseText As String
        responseText = oAuthClient.responseText
        Dim accessToken As String
        accessToken = GetJsonValue(responseText, "access_token")
    
        ' Create a service to interact with Google Sheets
        Dim sheetsService As Object
        Set sheetsService = CreateObject("MSXML2.ServerXMLHTTP.6.0")
        sheetsService.Open "GET", "https://sheets.googleapis.com/v4/spreadsheets/" & service("spreadsheetId") & "/values/" & service("range"), False
        sheetsService.setRequestHeader "Authorization", "Bearer " & accessToken
    
        ' Check if authentication was successful
        If sheetsService.Status = 200 Then
            Set service("sheets_service") = sheetsService
        Else
            MsgBox "Authentication failed. Please try again."
        End If
    End Sub
    
    Sub GetDataFromGoogleSheets(service)
        Dim sheetsService As Object
        Set sheetsService = service("sheets_service")
    
        ' Send a request to get data from Google Sheets
        sheetsService.send
    
        ' Check if the request was successful
        If sheetsService.Status = 200 Then
            Dim responseText As String
            responseText = sheetsService.responseText
    
            ' Parse the response data (you can customize this part)
            Dim data As Variant
            data = ParseGoogleSheetsData(responseText)
    
            ' Paste the data into the active sheet starting from cell A1
            Dim targetSheet As Object
            Set targetSheet = ThisWorkbook.Sheets(1) ' Change to your target sheet
            targetSheet.range("A1").Resize(UBound(data, 1), UBound(data, 2)).Value = data
        Else
            MsgBox "Failed to retrieve data from Google Sheets."
        End If
    End Sub
    
    Function GetJsonValue(jsonString As String, key As String) As String
        ' A function to extract a value from a JSON string given a key
        Dim regex As Object
        Set regex = CreateObject("VBScript.RegExp")
    
        regex.Global = True
        regex.MultiLine = False
        regex.IgnoreCase = True
        regex.Pattern = """" & key & """:\s*""(.*?)"""
    
        If regex.Test(jsonString) Then
            GetJsonValue = regex.Execute(jsonString)(0).SubMatches(0)
        Else
            GetJsonValue = ""
        End If
    End Function
    
    Function ParseGoogleSheetsData(data As String) As Variant
        ' A function to parse Google Sheets data from JSON to a 2D array
        Dim json As Object
        Set json = JsonConverter.ParseJson(data)
    
        Dim values As Object
        Set values = json("values")
    
        Dim numRows As Long
        numRows = values.Count
    
        Dim numCols As Long
        numCols = values(1).Count
    
        Dim resultArray As Variant
        ReDim resultArray(1 To numRows, 1 To numCols)
    
        Dim i As Long, j As Long
        For i = 1 To numRows
            For j = 1 To numCols
                resultArray(i, j) = values(i - 1)(j - 1)
            Next j
        Next i
    
        ParseGoogleSheetsData = resultArray
    End Function
    

    Step 6: Run the code and the required data from the authenticated Google Sheets will appear in your Excel Active Sheet.

    Note that, this code will only work if you have the Google Sheets API developers have authorized your email to the target Google Spreadsheet.

    Hopefully, we were able to help you. Let us know your feedback.

    Regards,
    Seemanto Saha
    ExcelDemy

  3. Hello OLE DAGFINN TANDBERG,
    Thanks for sharing your problem with us. I understand that you are facing problems with automatic value entry from the RFID reader.

    Usually, an RFID reader places values (e.g. bib number, name, start time, finish time, etc.) in cells of newer rows automatically by moving one row down. That means if the first RFID reading places values in Row 1, then the second RFID reading should automatically place values in ROW 2, the third RFID reading should automatically place values in ROW 3, and so on.

    But in your case, you have to press the Enter key to move one row down. This is probably due to the RFID reader or software configuration. It is possible that the RFID reader software places values in cells of Active Row (i.e. the row of Active Cell) but does not offset the active cell by 1 row down for the next set of entries.

    The best solution to this problem is to modify the settings of the RFID reader. If that is not possible, you can use a VBA code to change the Active Row each time an RFID reading is performed.

    Right-click over the Sheet Tab of the Startlist sheet and select the View Code option.

    View Code Option in Sheet tab Right-Click Menu

    At this point, the Visual Basic Editor for that sheet will open. Insert the following code in the editor module.

    VBA Code to Automatically Move to Next Cell

    Excel VBA Code

    
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim activeRowChange_column As String
        activeRowChange_column = "C" ' change this according to your requirement
        
        Set activeRowChange_range = ThisWorkbook.ActiveSheet. _
        Range(activeRowChange_column & ":" & activeRowChange_column)
        
        If Not Intersect(Target, activeRowChange_range) Is Nothing Then
            Target.Offset(1, -2).Select
        End If
    End Sub
    

    As your Startlist sheet would have 3 values (i.e. bib number, start time, and name), I have assumed that the cell from the Active Row of Column C will be the last cell updated from an RFID reading. When a cell in Column C is updated from the RFID reading, the active row will automatically move to the next row and be ready for the next RFID reading value entry.

    Repeat the same steps for the “Results” sheet as well.

    To demonstrate this actually works, we will use a User Form to enter values in the Startlist and Results sheets.

    User Form to demonstrate how the VBA code automatically moves to the next cell

    After entering the Bib Number and Name, when we click the Submit button (similar to scanning a card in the RFID reader), the Bib Number, Starting Time, and Name will be saved in the Active Row of the Startlist sheet. As soon as these records are saved, the active row will automatically move to the row below. You can notice this in the following GIF:

    Now, If you enter another Bib Number and name, and click the Submit button it will be saved in a new row. You can watch this in the following GIF:

    On the other hand, if you reenter the Bib Number and Name, and click the Submit button, it will look up the Bib Number in the Startlist sheet, calculate Final Time, and insert the Bib Number, Final Time, and Required Time in the Results sheet. As soon as these values are saved, the active row will move to the row below automatically. You can watch that in the following GIF:

    As the User Form was used only to demonstrate how the Active Row moves one row below, we haven’t included the code used in the User Form. But you can find the codes in the following workbook.

    WORKBOOK

    Hopefully, this solution will be helpful for you. However, as we have assumed a lot of properties of your Workbook and the RFID reader, this solution can vary from the actual required solution. Please share your workbook and the working process of the RFID reader in such an instance.

    Regards,
    Seemanto Saha
    ExcelDemy

  4. Dear AHMET KARAASLAN,
    Thanks for your comment.

    Although the mentioned formula is an array formula, you can use it in Excel for Microsoft 365 without any modifications.

    Enter the formula in your required cell and press the Enter key. After that drag down the Fill Handle icon.

    Using INDEX-MATCH functions to get multiple results

    Note: This formula can return #NUM! error if any match isn’t found when you use the Fill Handle feature. To avoid this you can combine the IFERROR function with your formula. The modifier formula is:

    =IFERROR(INDEX($C$5:$C$11, SMALL(IF(ISNUMBER(MATCH($B$5:$B$11, $B$14, 0)), MATCH(ROW($B$5:$B$11), ROW($B$5:$B$11)),””), ROWS($A$1:A9))),””)

    Combining IFERROR function with INDEX-MATCH formula

    However, if you want to avoid using the Fill Handle feature and want all match results with a single formula, then you can use the FILTER function. This function is only available in Excel for Microsoft 365 and can filter a range based on any given criteria.

    To get the same result as the INDEX-MATCH method, apply the following formula in the required cell and press the Enter key.

    =FILTER(C5:C11,B5:B11=B14)

    Using FILTER function instead of INDEX-MATCH formula

    I hope this solution will be helpful for you. Let us know your feedback.

    Regards,
    Seemanto Saha
    ExcelDemy

  5. Dear C2k,
    Thanks for your feedback. Yes, you are right. Your mentioned formulas can achieve same results. But the TAKE and TEXTSPLIT functions in your formula are only available in Excel for Microsoft 365.
    We have updated our article according to this method and mentioned the requirement of Microsoft 365. Thanks again.
    Regards,
    Seemanto Saha
    ExcelDemy

  6. Hello JOHN C,

    Yes, you are right, the formula for calculating the Mix value is different in the given Excel file and the above article.

    The formula mentioned in our article is correct. There must have been an error while selecting cell E11, hence the adjacent cell D11 is present in the Excel file formula.

    We have updated the Excel file with the correct formula. Thanks for your feedback.

    Regards,
    Seemanto Saha
    Exceldemy

  7. Dear PHILIP SMITH,
    Thanks for reaching us. I understand that you want to convert a 4-digit Julian date to a Calendar date. In your specified format, the 1st digit is the number of the year, and the next 3 digits are the day of the year.
    To demonstrate this problem, I have taken a dataset that contains 4-digit Julian Dates in the range C5:C10. To convert these Julian dates into Calendar dates, I applied the following formula:

    =DATE(INT(YEAR(TODAY())/10)*10+VALUE(LEFT(C5,1)), 1, MOD(C5, 1000))

    Converting 4 Digit Julian Date to Calendar Date

    Now, you can extract the Julian date from the transaction number and apply the formula above to convert the Julian date to the calendar date.

    Hopefully, I was able to resolve your problem. Let us know your feedback.

    Regards,
    Seemanto Saha
    ExcelDemy

  8. Dear Agnes,

    To obtain similar results in Google Sheets, we have to create a similar dataset, click on the Extensions menu, and select Apps Script from the options.
    Dataset for Bold Text in Concatenation
    Then in the new window, we have to replace the default script with the following script:

    
    function boldTextInConcatenate() {
      var ss = SpreadsheetApp.getActiveSpreadsheet()
      .getSheetByName("Bold in Concatenate");
     
      var lastRow = ss.getLastRow();
    
    
      var bold = SpreadsheetApp.newTextStyle()
      .setBold(true)
      .build();
    
    
      for (var i=3;i<=lastRow;i++){
        var fName = ss.getRange(i,2).getValue();
        var lName = ss.getRange(i,3).getValue();
       
        var richText = SpreadsheetApp.newRichTextValue()
        .setText(fName+' '+lName)
        .setTextStyle(0, fName.length, bold)
        .build();
       
        ss.getRange(i,4).setRichTextValue(richText);
      }
    }

    Afterward, we have to Save and Run the script. The output should be like the following:
    Output of Bold Text in Concatenate
    You can download the Spreadsheet from the link below:
    Bold text in Concatenate

    Let us know your feedback.

    Regards,
    Seemanto Saha
    ExcelDemy

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo