Sourav Kundu

About author

Sourav Kundu, BSc, Naval Architecture & Marine Engineering, Bangladesh University of Engineering and Technology, is a dedicated technical content creator of the ExcelDemy project. He has a keen interest in Excel and he leverages his problem-solving skills to provide solutions on user interface with Excel. In his position as an Excel & VBA Content Developer at ExcelDemy, Sourav Kundu not only adeptly addresses challenging issues but also demonstrates enthusiasm and expertise in navigating complex situations. Apart from creating Excel tutorials, he is interested in Data Analysis, SPSS, Web Scraping, and Report Writing.

Designation

Excel & VBA Content Developer at ExcelDemy in SOFTEKO.

Lives in

Dhaka, Bangladesh.

Education

B.Sc. in Naval Architecture & Marine Engineering, BUET.

Expertise

Autocad, Rhino, Maxsurf, Adobe illustrator, C++, Abaqus, MS office.

Experience

  • Technical Content Writing
  • Undergraduate Projects
    • Design of an Offshore support vessel
    • Hazard analysis of ship recycling industry of Bangladesh

Latest Posts From Sourav Kundu

0
Line Graph in Excel (Plot, Fit and Overlay)

How to Plot Line Graph with Single Line in Excel The sample dataset contains Sales by a company for the year 2018. Select the data range B5:C16. ...

0
How to Perform Calculus in Excel? (Differentiation and Integration)

Calculus is an important part of mathematics. We may have to perform several calculus operations for practical analysis. Even though there are no Excel ...

0
How to Recover Excel File? (Overwritten and Deleted Files)

We often have to recover our overwritten or deleted Excel files. These files are important for retrieving important information. To recover overwritten ...

0
How to Use Operators in Excel? (All You Need to Know)

In this article, you'll get to know about 4 commonly used operators in Excel. They are Arithmetic, Comparison/Logical, Reference, and Concatenation operators. ...

0
How to Create Mekko/Marimekko Chart in Excel (With Easy Steps)

In this article, we will show you step-by-step procedures to create a Mekko or Marimekko chart in Excel. We will take an imaginary dataset. Then, we will ...

0
220++ Keyboard Shortcuts in Excel ( for Quick Navigation)

Shortcuts in Excel allow us to complete a task quickly because we don't have to go through multiple tabs or context menus. Instead, we only need a few keyboard ...

0
Excel VBA Digital Clock on UserForm: A Comprehensive Guide

In Excel VBA, we can create a Digital Clock on UserForm to show the current time. A digital clock on UserForm can be useful for various practical purposes. ...

0
Excel VBA: How to Use Target Row (4 Ways)

You can use Target Row in Excel VBA for different purposes. This is a useful tool to carry out many tasks by just selecting a specific row. In this article, ...

0
Excel VBA to Pause and Resume Macro (5 Examples)

We can pause and resume macro in Excel VBA for some practical reasons. The user might want to pause the macro to change the data. Also, it gives the user time ...

0
How to Create Shift Roster 24×7 with Excel Automation

  Step 1 - Define Names for the Dropdown List Create dropdown boxes for 3 different items: Month, Year, and Shift. Let's say we have five shifts ...

0
How to Hide and Unhide Sheets in Excel with VBA (6 Examples)

This article will show you how to hide and unhide sheets in Excel with VBA. Hiding and unhiding in Excel can help the user to organize data more effectively. ...

0
How to Perform Permutation and Combination in Excel VBA

We can perform permutation and combination in Excel VBA. Permutation and Combination are often used in mathematical analysis, especially in the field of ...

0
How to Calculate Expected Return in Excel (2 Easy Steps)

Portfolio management is a major activity that every corporate financials has to undergo. In portfolio management, the calculation of expected returns is ...

0
How to Use For Loop Skip to Next Iterations In Excel VBA

Method 1 – 'Skip to Next' Iteration in the 'For-Next Loop' with Step Statement   Below is a dataset of different St. IDs and their marks in different ...

0
How to Count Unique Values in Excel with Multiple Criteria

This article will show you some methods to count unique values in an Excel worksheet based on different criteria. The Excel worksheet can count unique values, ...

Browsing All Comments By: Sourav Kundu
  1. Hello, ALEJANDRO
    Thank you for your comment. We have got an effective solution for your problem. We have taken 2 tables in the same worksheet as you can see from the following image:

    image 1

    To compare between these 2 tables, you can use the following VBA code:

    Sub HighLight_Diff_in_same_Ws()
        Dim ws As Worksheet
        Dim cell_1 As Range, cell_2 As Range
        Set ws = ThisWorkbook.Sheets("Sheet1")
        
        For Each cell_1 In ws.Range("B5:D12")
            Set cell_2 = ws.Cells(cell_1.Row, cell_1.Column + 4)
            If Not (cell_1.Value = cell_2.Value) Then
                cell_1.Interior.Color = vbGreen
                cell_2.Interior.Color = vbGreen
            End If
        Next cell_1
    End Sub

    Note: You should modify the Worksheet Name, Range and Cells references according to your data table.

    Image 2

    When you run the VBA code, it will highlight the differences between the 2 tables in green color.

    Image 3

    I hope, this is the solution you were looking for. If you have further queries let us know in the comment section. We will solve them as soon as possible.

    Regards,
    Sourav Kundu.
    Exceldemy

  2. Hi JOHN,
    Thank you for sharing your problem with us. We have got a simple solution to your problem. You can use a VBA event for this purpose. Whenever you need a blank attendance sheet for a new month, you just have to change the value of the green-colored cell G5.
    First, Right-click on your worksheet named Automated.
    Then, click on View Code.

    view code

    Then copy the following VBA code and paste it into the opening window:

    Private Sub Worksheet_Change(ByVal Target As Range)
       
        If Not Intersect(Target, Me.Range("G5")) Is Nothing Then
       
          Me.Copy After:=Sheets(Sheets.Count)
          Me.Range("E11:AI100").ClearContents
        End If
    End Sub
    

    code

    Now, go back to the Automated sheet and insert Daily Attendences.

    null

    Now, write something in the G5 cell say N, and press Enter.
    Instantaneously, the Automated sheet will be cleared and will create a new sheet Automated (2).

    Now, go to the Automated (2) sheet and you can see the previous attendance record has been copied to this sheet.

    The original worksheet is blank and is ready to take instructions from scratch.

    Note: You can change the green color cell reference from the VBA code.

    We hope that we have provided a reasonable solution to your problem. If you have more queries you can always mention them in the comment section.

    Regards,
    Sourav Kundu
    Exceldemy.

  3. Dear JOHN SINCLAIR,
    Thank you for sharing your problem with us. We have got a simple solution for your problem. It seems to us that you might be selecting the Name field from the Income table. That’s why Sum of Total Cost column is getting filled with 140033.01.
    Merge Two Pivot Tables Comment Solution-1
    To solve this problem, make sure to mark the Name field from the Cost table and leave the Name field from the Income table unmarked. This will solve the problem and you will be able to merge different tables as you wanted.
    Merge Two Pivot Tables Comment Solution-2
    We hope, our solution will work for your problem. If you face further difficulties with merging two pivot tables in Excel, you are always welcome to inform us.
    Regards,
    Sourav Kundu
    Exceldemy

  4. Hello SUNZEEV,
    Thank you for your comment. Here, we are providing two revised formulas to meet your requirements.
    For simplicity, we have modified the dataset with two additional Company names.
    To get all the device names for each company, write the following formula in E6 and press Enter.
    =IFERROR(INDEX($B$5:$C$19,SMALL(IF($B$5:$B$19=E5,(ROW($B$5:$B$19)-ROWS(B1:B4)),""),(ROW($B$5:$B$19)-ROWS(B1:B4))),2),"")
    You will get all the results of Samsung at once.
    Now, drag the E6 cell to the right to H6 cell. You will get your desired results.

    Another way is to use the FILTER function. For this method, write the following formula in E6 and press Enter.
    =FILTER(C5:C19,B5:B19=E5)
    Now, drag the E6 cell to the right to the H6 cell. You will get your desired results. The FILTER function is available on Excel 365 only.

    You can download the workbook from the link below.
    Answer.xlsx
    We hope these methods will satisfy your queries. If you have further questions, you are always welcome.

    Regards,
    Sourav
    ExcelDemy.

  5. Hi JEFF Z,
    Thank you for sharing your problem with us. We have got a very simple solution to your problem. You just have to delete the last line (Call Shell(“TaskKill /F /IM Acrobat.exe”, vbHide)) from the code.
    And, the revised code is given as follows:

    Sub Extract_Data_from_PDF()
    
    Set MyWorksheet = ActiveWorkbook.Worksheets("Sheet1")
    Application_Path = "C:\Program Files\Adobe\Acrobat DC\Acrobat\Acrobat.exe"
    PDF_Path = "C:\Users\PC 21\Downloads\sample.pdf"
    
    Shell_Path = Application_Path & " """ & PDF_Path & """"
    Call Shell(pathname:=Shell_Path, windowstyle:=vbNormalFocus)
    
    Application.Wait Now + TimeValue("0:00:03")
    
    SendKeys "%vpc"
    SendKeys "^a"
    SendKeys "^c"
    
    MyWorksheet.Range("A1").PasteSpecial Paste:=xlPasteAll
    
    End Sub

    The problem with the previous code was that the code would close the PDF file before pasting it into Excel. So, we have removed the last line so that, the code doesn’t close the pdf file at all. In this case, you have to close the pdf file manually.
    Make sure to close the pdf file before running the code. And, also, make sure to clear the contents of Column 1 of Sheet 1 before running the code.

    Regards,
    Sourav Kundu
    ExcelDemy.

  6. Hi NN,
    Thank you for sharing your problem with us. We have got a solution to your requirement. With this solution, whenever you need a blank record, you just have to change the value of the yellow-colored cell. You have to use a VBA code in the worksheet for the purpose.
    First, Right-click on your worksheet named Template.
    Then, click on View Code.

    Then. copy the following VBA code and paste it into the opening window:

    Private Sub Worksheet_Change(ByVal Target As Range)
        ' Check if the changed cell is D3
        If Not Intersect(Target, Me.Range("D3")) Is Nothing Then
          ' Copy the entire sheet
          Me.Copy After:=Sheets(Sheets.Count)
          ' Clear contents of range D6:AH1006
          Me.Range("D6:AH1006").ClearContents
        End If
    End Sub


    Now, go back to your workbook and insert some Employee Leaves.
    Now, write something say N, and press Enter.

    The code will create a new worksheet containing the Employee Leaves records.

    Now, go back to the previous worksheet.
    The original worksheet is blank and is ready to take instructions from scratch.

    Note: You can change the Yellow color cell reference from the VBA code.
    You can download the updated template with the VBA code from below:
    Answer.xlsm
    We hope that we have provided a reasonable solution to your problem. If you have more queries you can always mention them in the comment section.

    Regards,
    Sourav Kundu
    Exceldemy.

  7. Hello, STEVE C.
    Thank you for sharing your problem with us. All the methods shown in the article should work perfectly for Microsoft 365 version. You might be using an older version. That’s why you are facing the problem. So, make sure to activate Microsoft 365 on your PC. Then, you should be able to use these procedures to get the color index.
    Do let us know if your problem is solved or not.
    Regards,
    Sourav Kundu
    ExcelDemy

  8. Hello, RUSSEL.
    Thank you for sharing your problem with us. We have provided 2 different ways to solve your problem.
    For example, we have a dataset of Product, Size, and Price. We will find the total price of the Small Shirts.
    Method-1:
    For this, write the following formula in the F9 cell and press Enter.
    =IFERROR(INDEX($D$5:$D$15, SMALL(IF(($G$4=$B$5:$B$15)*($G$5=$C$5:$C$15), ROW($D$5:$D$15)-ROW($D$5)+1), ROWS($1:1))), "")
    And, you will get the first Price value.
    Then Drag the F9 formula downwards to get other Price values that meet the criteria.

    You can see that we have got two Price values that meet the multiple criteria.
    Now, you can use the SUM function to get the total Price value.

    Method 2:
    Write the following formula in F9 and press Enter.
    =SUM(IFERROR(FILTER(D5:D15, (G4=B5:B15)*(G5=C5:C15)), ""))
    And, you will get the total Price value with multiple criteria.

    The FILTER function used in this method is only available for Microsoft 365 and Excel 2021.
    Solution to Your Problem:
    You can use the formula below to solve your problem:
    =SUM(IFERROR(FILTER($G$20:$G$52, ($B$20:$B$53=$B3)*($E$20:$E$52=$D3)*($F$20:$F$52=CH$1)*($A$20:$A$53=$A3)*($D$20:$D$52=$E3)), ""))
    We could have given you the exact formula if we had your dataset. Let us know if your problem is solved.
    Regards,
    Sourav
    ExcelDemy.

  9. Hello, KENNY H.
    Thank you for sharing your problem with us. However, we would have liked more information about your problem. The method described in this article should work perfectly. Try to follow the methods and steps given in this article as it is. Make sure to download the proper code 128 font.
    You can email us screenshots of your problem or your workbook at [email protected]
    That would really help us to understand your problem and give a proper solution.
    You can also follow the articles below for generating barcodes in Excel.
    1. Create Barcode in Excel
    2. Generate 2D Barcode in Excel
    Have a nice day!
    Regards,
    Sourav
    Exceldemy.

  10. Hi VIJAY,
    Thank you for sharing your problem with us. As you wanted to determine each employee’s shift assuming that, their holiday can be any weekday, we have modified the attendance sheet. We hope this solution will meet your requirement.

    1. First, you have to create a new table that will include the preferred work shifts of each employee.
    2. For simplicity, we are going to format these cells based on text value.
    3. So, select the data range F10:L14.

    4. Now, from the Home tab go to Conditional Formatting and Select Format only cells that contain.
    5. Then you have to set Yes as Specific Text.
    6. Also, choose green as the format color.
    7. Then, press the OK button.

    8. Similarly, create another condition that if the cell contains the specific text No, the cell will be formatted as red.

    9. Your selected cells should still be blank.
    10. Now, to indicate the work shift, write Yes or No in these formatted cells.
    11. And, you can see the cells are instantaneously formatted with green and red colors (You can also use Yes or No from the drop-down box instead of writing them manually).

    11. Now, go to the attendance sheet and suppose, there is no condition in this Shift table.
    12. Select the F19 cell and create a new condition.

    13. You have to select Use a formula to determine which cells to format as the New Formatting Rule.
    14. Then, write the following formula in the formula box.
    =INDEX($B$9:$L$14, MATCH($B19,$B$9:$B$14,0),MATCH(F$18,$B$9:$L$9,0))="No"
    15. Also, select a reddish format color and press OK.

    16. Then, use the Fill Handle to copy the conditional formatting for all cells as shown in the following image.
    17. You can see, the Attendance table has been marked by each employee’s preferred off days.

    18. Now, you can select Present (P) or Absent (A) for each employee.

    You may need to modify the conditional format formula according to your data table. Be careful about relative and absolute referencing. You can also check the Excel file from the link below:
    Answer.xlsx
    We hope this comment has been useful to you. Have a nice day.
    Regards
    Sourav
    Exceldemy.

  11. Thank you MOhamad for your query. The Persian language is not available in the UNICODE. That’s why you are facing the problem. If you want to use the code for Persian characters, you have to modify the code slightly. The code can not take Persian language in the InputBox. So, instead of InputBox, we have to insert text to be highlighted in a cell. Let’s insert Persian text to be highlighted in the C10 cell. So, the modified vba code should be like this one.
    Sub Text_Highlighter()

    Application.ScreenUpdating = False

    Dim Rng As Range

    Dim cFnd As String

    Dim xTmp As String

    Dim x As Long

    Dim m As Long

    Dim y As Long

    cFnd = Range(“C10”).Value

    y = Len(cFnd)

    Color_Code = Int(InputBox(“Enter the Color Code: ” + vbNewLine + “Enter 3 for Color Red.” + vbNewLine + “Enter 5 for Color Blue.” + vbNewLine + “Enter 6 for Color Yellow.” + vbNewLine + “Enter 10 for Color Green.”))

    For Each Rng In Selection

    With Rng

    m = UBound(Split(Rng.Value, cFnd))

    If m > 0 Then

    xTmp = “”

    For x = 0 To m – 1

    xTmp = xTmp & Split(Rng.Value, cFnd)(x)

    .Characters(Start:=Len(xTmp) + 1, Length:=y).Font.ColorIndex = Color_Code

    xTmp = xTmp & cFnd

    Next

    End If

    End With

    Next Rng

    Application.ScreenUpdating = True

    End Sub

    You should insert your preferred cell instead of C10. And this code will highlight characters of all other languages.

  12. Hey Yann, we have tried your formula. But unfortunately, this formula is not working as you said it would. It gives a Value error instead.

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo