Joyanta Mitra

About author

Joyanta Mitra, a BSc graduate in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology, has dedicated over a year to the ExcelDemy project. Specializing in programming, he has authored and modified 60 articles, predominantly focusing on Power Query and VBA (Visual Basic for Applications). His expertise in VBA programming is evident through the substantial body of work he has contributed, showcasing a deep understanding of Excel automation, and enhancing the ExcelDemy project's resources with valuable programming insights.

Designation

Excel and VBA Content Developer at ExcelDemy, SOFTEKO.

Lives in

Dhaka, Bangladesh.

Education

B.sc in Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology.

Expertise

Verilog, Micropython, Mikroe C, Java(Core+TCP/IP), Digital design, Matlab, Assembly Programming.

Project

  • Java games: Snake, Sudoku, Reinforcement Learning using Gym and Anaconda.

Research

  • Ion Energy Distribution in Plasma, Prediction of Uniform Energy Electron Location.

Latest Posts From Joyanta Mitra

0
Excel Power Pivot Measures – 5 Examples

A DAX code will be used to find average sales, commission, success rate of broker calls, and the greatest number of calls. Download Practice ...

0
Excel Picture Format: 12 Different Scenarios

Method 1 - Making Correction to an Image Get picture format, you have to select the image first in every case. Choose Picture Format option from ...

0
How to Use Excel String Functions (Examples of 17 Functions)

  Function 1 – TEXT Function Syntax: =TEXT(value, format_text) Arguments Explanation: Argument Required/Optional Explanation ...

0
Workflow in Excel: A Must-Have Guide

In this article, we provide a detailed guide to creating and automating workflow in Excel using Excel's built-in shapes. Creating a workflow can be a ...

0
Comments in Excel (Insert, Copy, Format, Hide, Delete & Print)

This is an overview. Download Practice Workbook Comments in Excel.xlsx How to Insert/Add Comments in Excel 1. Insert Comment in ...

0
How to Add Comma in Excel (10 Easy ways)

Here's a simple overview of putting a comma separator for numerical values in Excel. Download the Practice Workbook How to Add Comma in ...

0
Excel Probability

In this article, we have used PROB, fractional method, NORM.DIST and NORMDIST function to find out Excel probability. Here's an overview of the function. ...

0
How to Create and Use a Dictionary in Excel

Here is an overview. Download Practice Workbook Dictionary in Excel.xlsm Example 1 - Creating a Data Dictionary in Excel Using an Excel ...

0
How to Insert and Use Timestamp in Excel?

n the picture below, we have demonstrated an example of inserting a timestamp in Excel. Download the Practice Workbook Timestamp in Excel.xlsm ...

0
Hide Excel Formulas (with Protecting Sheet & Without It)

This article will cover how to hide Excel formulas, either by using the Format Cells and Review features, or with a VBA macro. Since these methods require ...

0
How to Randomize in Excel? – 5 Methods

This is an overview.   Method 1 - Using the RAND Function to Get a Random Decimal Number Between 0 and 1 Go to C5 and enter the ...

0
How to Apply Excel’s Auto Formatting (Add, Customize, Use & Remove)

What Is Auto Formatting in Excel? Auto formatting in Excel simplifies the process of making your data visually appealing. It provides ready-made styles for ...

0
Cell Reference in Excel (Absolute, Relative, and Mixed)

In this article, you will learn how to use absolute, relative, and mixed cell references. We'll use a sample dataset for Products and Sales, in which we ...

0
How to Use Excel VBA Clear Clipboard [With Easy Steps]

  Here, we have provided a dataset with clipboards. Step 1: Insert a Module To add VBA code, select Visual Basic from the Developer. ...

0
3D Interpolation in Excel: 4 Easy Methods

Method 1 - Finding the Value of Z Find out the value of the Z from the Z = SIN(x2+y2)/√(x2+y2) equation. We used the following formula in the D5 cell to ...

Browsing All Comments By: Joyanta Mitra
  1. Dear April
    Thanks for your concern. There were some minor formatting issues with the VBA codes. Sorry for the inconvenience. We have updated our article. If you follow now, you will get the perfect calculator.
    Thanks for your help
    Regards,
    Joyanta Mitra
    ExcelDemy

  2. Dear Adam,
    Thank you for your concern. You can not change the color of Excel button but you can use Command Button to change the button color.
    You can follow the steps below to color your command button:
    1. use Developer tab > Insert > ActiveX Controls > Command Button.
    Adding Command button
    2. A command will appear and right click on it.
    3. Choose CommandButton Object > Edit.
    right click on the command button
    Name the button as Start.
    4. Now right click on the command button and choose View Code.
    view code
    5. Then write code below for starting.

    
    Private Sub CommandButton2_Click()
    countDown = Now + TimeValue("00:00:01")
    Range("B4") = Range("B4") + TimeValue("00:00:01")
    Application.OnTime countDown, "StartTimer"
    CommandButton2.BackColor = 13959039
    End Sub
    

    Finally the button color has been added.
    start button colored
    6. Now create a stop buttom and use the code below:

    
    Private Sub CommandButton1_Click()
    Application.OnTime EarliestTime:=countDown, Procedure:="StartTimer", Schedule:=False
    CommandButton1.BackColor = 13959039
    End Sub
    

    7. Write down the code for reset button:

    
    Private Sub CommandButton3_Click()
    Range("B4") = TimeValue("00:00:0")
    CommandButton3.BackColor = 13959039
    End Sub
    

    Finally, you will have colored command button. You can change the color by changing color code 13959039.
    Final result

  3. Dear Kelley Sauer,
    Please use this formula below to count sales having date.
    =COUNTIF(E5:E14, "<>")
    Count cells when date is used
    It will count all sales with date. Using this formula, you will not get zero anymore. Moreover I have also used Ctrl+: to insert date.
    With Regards,
    Joyanta Mitra

  4. Dear Anas,
    You have to write the following code to get data validation for duplicate values, you have write the code below.

    
    Sub CreateUniqueDropDownListInC12()
        Dim ws As Worksheet
        Dim cell As Range
        Dim dict As Object
        Dim dataValidation As Validation
        Dim dvList As String
        
        ' Define the worksheet where you want to apply the data validation
        Set ws = ActiveSheet
        
        ' Create a dictionary object to store unique values
        Set dict = CreateObject("Scripting.Dictionary")
        
        Application.ScreenUpdating = False
        
        ' Loop through the specified range (C5 to C11)
        For Each cell In ws.Range("C5:C11")
            If cell.Value <> "" Then ' Check for non-empty cells
                If Not dict.Exists(cell.Value) Then
                    dict(cell.Value) = 1 ' Add the value to the dictionary
                End If
            End If
        Next cell
        
        ' Convert the unique values to a comma-separated string for data validation
        dvList = Join(dict.Keys, ",")
        
        ' Apply data validation with a dropdown list to cell C12
        Set dataValidation = ws.Range("C12").Validation
        With dataValidation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=dvList
            .IgnoreBlank = True
            .InCellDropdown = True
            .ShowInput = True
            .ShowError = True
        End With
        
        Application.ScreenUpdating = True
    End Sub
    

    Then we get the data values of unique departments Marketing and Sales.
    datavalidation with duplicates
    With regards,
    Joyanta Mitra

  5. Dear Santosh Kumar,
    Please clarify the question. How can a person do more than 24 hours in 24 hours?
    With Regards.
    Joyanta Mitra

  6. Dear FRANCESCA BATHE,
    End Position is given according to column To. Where there is RENT in To column, the number starts 1,2,3,4 and ends with SPACE1 numbered 5. Then next is Food starting 6,7,8, 9 and ends with SPACE2 numbered 10. Likewise, numbering is done for every cell data in End Position.
    Numbering End position
    Regards,
    Joyanta Mitra

  7. Code is alright. Please inform your particular problem.

  8. Dear Prince,
    Thank you very much for reading our article.
    According to your query, 1st you wanted to know about a formula to create a drop-down list that will be used to select different types of leaves. You will get that in Step 3 of this article. In our Excel file, we selected leave using the drop-down list in Record sheet. Also when you move to any month leave information will be based on the Record sheet. So, information will not move from one month to another. Try this and hope you will get the solution.
    Otherwise, send your Excel file with what you want to get and we will try to provide a solution. You can mail us at [email protected].
    Thanks
    Joyanta Mitra
    ExcelDemy

  9. Dear Karlyn Martinez,
    For your convenience, I have showed the task with following steps.
    Steps:
    ● First, you have to recognize the pattern in the formula
    Showing pattern
    ● You can use Format Painter or drag the row to add a new row or rows for editing new data.

    Modifying data
    ● Now add new data.

    Adding new data
    ● Inset new rows in the Summary sheet.
    Inserting new rows in Summary sheet
    ● Insert the Entire row.
    Inserting new row
    ● Edit the code according to the main dataset. As now in Jan worksheet, new data is added, and so the range will be changed to AH$15 and $B$15.
    Showing the formula change

    Hope, this will be helpful for you.

    Regards,
    Joyanta Mitra
    Excel & VBA Content Developer

  10. This code will solve your problem. The code has a condition checking values blank or not.

    Sub Duplicates_Dif_Colors()
    Dim RG As Range
    Dim TT As String
    Dim CL As Range
    Dim CR As String
    Dim CP As Range
    Dim CD As Long
    Dim Cltn As Collection
    Dim J As Long
    
    On Error Resume Next
    
    If ActiveWindow.RangeSelection.Count > 1 Then
    TT = ActiveWindow.RangeSelection.AddressLocal
    Else
    TT = ActiveSheet.UsedRange.AddressLocal
    End If
    
    Set RG = Application.InputBox("Select range of data with duplicates:", "Duplicate values with Colors", TT, , , , , 8)
    If RG Is Nothing Then Exit Sub
    
    CD = 2
    Set Cltn = New Collection
    
    For Each CL In RG
    If CL.Value <> "" Then ' check if cell is not blank
    On Error Resume Next
    Cltn.Add CL, CL.Text
    If Err.Number = 457 Then
    CD = CD + 1
    Set CP = Cltn(CL.Text)
    If CP.Interior.ColorIndex = xlNone Then CP.Interior.ColorIndex = CD
    CL.Interior.ColorIndex = CP.Interior.ColorIndex
    ElseIf Err.Number = 9 Then
    MsgBox "Found excessive duplicates", vbCritical, "Duplicates with Colors"
    Exit Sub
    End If
    On Error GoTo 0
    End If
    Next
    End Sub
    

    Output:

    Blank cells not colored

  11. Please write Ticker correctly both in your xlsx file and the code. Do not try to give invalid ticker or outdated ticker. Provide problems in detail for better service please.

  12. First, you have to create different power queries for each worksheet for other tickers and copy the same code. Otherwise, it triggers the first. Then for 10 different tickers,the code is

    
    let
        TickerList = {"AAPL", "GOOG", "MSFT", "AMZN", "FB", "TSLA", "NVDA", "JPM", "BAC", "V"},
        StartDate = Excel.CurrentWorkbook(){[Name="StartDate"]}[Content]{0}[Column1],
        EndDate = Excel.CurrentWorkbook(){[Name="EndDate"]}[Content]{0}[Column1],
        #"Loop Through Tickers" = List.Transform(TickerList, each
            let
                Ticker = _,
                Source = Csv.Document(Web.Contents("https://query1.finance.yahoo.com/v7/finance/download/"&Ticker&"?period1"&StartDate&"&period2"&EndDate),[Delimiter=",", Columns=7, Encoding=1252, QuoteStyle=QuoteStyle.None]),
                #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
                #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Open", type number}, {"High", type number}, {"Low", type number}, {"Close", type number}, {"Adj Close", type number}, {"Volume", Int64.Type}})
            in
                #"Changed Type"
        ),
        #"Loop Through Tickers1" = #"Loop Through Tickers"{8}
    in
        #"Loop Through Tickers1"

    this code creates 10 tables for 10 different tickers. You have to select one table according to Ticker and hover over the query in Workbook Query Section and press View in Worksheet. You will get the table have to create 10 sheets separately.

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo