# Seemanto Saha

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.

## 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
• 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.

## Latest Posts From Seemanto Saha

What Are the Sources for Sample Excel Data for Analysis? Online platforms: Various online database platforms such as government websites or public ...

Project Management Sample Data A project management sample data is suitable for various types of data filtering, analyzing, and visualizing. Here are the ...

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

Method 1 - Use the IF Function to Calculate a Percentage with Criteria in Excel Consider the following dataset with the sales volume of some employees ...

## Conditional Average in Excel (Complete Guide)

Conditional averages involve calculating the average of a subset of data that meets specific criteria. In this Excel tutorial, we will demonstrate how to ...

## Excel Linear Programming (Using the Solver and Graphical Methods)

The Solver Add-in can solve linear and non-linear programming problems with multiple variables and constraints, whereas the graphical method can only be used ...

## Formatting Worksheet in Excel: 11 Examples

1. Turning Off Gridline in an Excel Worksheet Go to the View tab. Uncheck the Gridlines option to disable it. The gridlines will turn off from the ...

## 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 ...

## Export Excel to txt (Ultimate Guide)

Download Practice Workbook Export Excel to txt.xlsm A dataset similar to the following is present in each sheet of the sample workbook. ...

## Ageing Formula in Excel (All Things You Need to Know)

Download Practice Workbook Ageing Formula.xlsx Method 1 - Calculate Age in Years We can use the combination of several functions to ...

## What Is the Keyboard Shortcut to Manually Add Line Break in Excel?

Double-click on the cell >> place the cursor in the required position >> press the Alt + Enter keys simultaneously to enter a new line. ...

## How to Apply a Bottom Double Border in Excel – 2 Methods

In the following image, the Bottom Double Border is applied in the row before the Total row. Method 1 - Using the Built-in Border Option Select ...

## [Fixed!] Excel Superscript Not Working (6 Ideal Solutions)

There are various reasons why superscript formatting may not be working as expected in Excel. In this tutorial, we will discuss these reasons and provide ...

## How to Update Excel Links (Manually/Automatically)

We'll use the following datasets to demonstrate how you can update links inside and between sheets. ⏷Update Links Automatically ⏷Update Excel Links ...

## How to Remove Decimals in Excel Formula Bar (3 Methods)

We have a simple dataset of numbers that have a decimal number in the formula bar, which we'll replace or display as an integer. Note: We used Microsoft ...

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.
=IF(COUNTIF(C5:E5, “>=70”)=3, “Pass”, IF(COUNTIF(C5:E5, “>=70”)>=1, “Try again”, “Fail”))

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

``````
Dim rangeName As String
Dim oAuthApp As Object

rangeName = "TestSheet!A1:C10" ' Change to your desired range

' Initialize the Google API client
Set oAuthApp = CreateObject("Scripting.Dictionary")

' Configure OAuth2 client
oAuthApp("client_secret") = "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"

' 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
End Sub

' Create a Google Sheets service instance
Dim service As Object
Set service = CreateObject("Scripting.Dictionary")
service("range") = rangeName

' Use your OAuth2 client details
Set service("oauth_client") = oAuthApp

' Check if the authentication was successful
If Not service("sheets_service") Is Nothing Then
' Fetch data from Google Sheets
End If
End Sub

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: ")

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.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.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

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

' 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

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.

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.

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

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.

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,

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.

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))),””)

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)

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))

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.

Then in the new window, we have to replace the default script with the following script:

``````
function boldTextInConcatenate() {
.getSheetByName("Bold in Concatenate");

var lastRow = ss.getLastRow();

.setBold(true)
.build();

for (var i=3;i<=lastRow;i++){
var fName = ss.getRange(i,2).getValue();
var lName = ss.getRange(i,3).getValue();

.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: