Naimul Hasan Arif

About author

Naimul Hasan Arif, a BUET graduate in Naval Architecture and Marine Engineering, has been contributing to the ExcelDemy project for nearly two years. Currently serving as an Excel and VBA Content Developer, Arif has written more than 120 articles and has also provided user support through comments His expertise lies in Microsoft Office Suite, VBA and he thrives on learning new aspects of data analysis. Arif's dedication to the ExcelDemy project is reflected in his consistent contributions and on going enthusiasm for expanding his knowledge in data analysis.

Designation

Excel & VBA Content Developer at ExcelDemy in SOFTEKO.

Lives in

Dhaka, Bangladesh.

Education

B.sc in Naval Architecture & Marine Engineering (NAME), BUET.

Expertise

Data Analysis, Content Writing, Microsoft Office.

Experience

  • Technical Content Writing
  • Team Work
  • Naval Architect

Summary

  • Currently working as an Excel & VBA Content Developer.
  • Started technical content writing of Excel & VBA in June 2022.

Latest Posts From Naimul Hasan Arif

0
VBA to Hide All Sheets Except One in Excel

Sometimes, we might wonder to hide the secret or unnecessary sheets in terms of working with Excel. If you are looking for a better way to do it, you are ...

0
Advanced VLOOKUP in Excel

Advanced VLOOKUP in Excel: Knowledge Hub Use VLOOKUP for Multiple Columns VLOOKUP from Multiple Columns with Only One Return Use VLOOKUP to Return ...

0
Excel VBA to Loop Through Files in Folder and Copy Data 

It is not a very uncommon phenomenon that we need to make a compilation of multiple Excel files into a single one. For this, we need to go through each ...

0
[Fixed!] Excel VBA Run Time Error 1004

Excel VBA Run Time Error 1004 is a very common error in Microsoft Excel while working with Visual Basic for Applications (VBA) macros. Although it's a common ...

0
How to Change Print Area in Excel (7 Smart Ways)

Sometimes, we face the necessity to print out our work in Microsoft Excel as hardcopy. But it has a certain default dimension as a print area. Questions may ...

0
How to Plot Time Series Frequency in Excel (2 Simple Ways)

Are you looking for a better way to Plot Time Series Frequency in Excel? Well, I will try to explain two simple ways to plot time series frequency in Excel. I ...

0
Excel VBA: Select Range with Offset Based on Active Cell

Looking for ways to select a range with offset based on the active cell using VBA in Excel? Then, this is the right place for you. In order to select a ...

0
Excel VBA Sort Named Range (3 Examples)

Looking for ways to sort named range using VBA in Excel? Then, this is the right place for you. In Microsoft Excel, a Named Range is a set of cells with ...

0
VBA MsgBox That Will Automatically Disappear in Excel

At the time of working with Microsoft Excel, we might face the necessity of disappearing MsgBox automatically. With the help of VBA, we can disappear MsgBox ...

0
How to Create Bill Payment Checklist in Excel (2 Smart Ways)

In our day-to-day life, we need to deal with various bills. To organize the bill payment, we can create a bill payment checklist. In this article, I will ...

0
How to Highlight Cell If Contains Text in Excel (with Easy Steps)

We like to have our data sorted. Sometimes, we might face the necessity to differentiate cells containing text from the entire dataset. In this article, I will ...

0
How to Create an Event Calendar in Excel (with Easy Steps)

An Excel Event Calendar is a tool that tracks and schedules events, meetings, and appointments in a calendar format. It can be a useful tool for individuals ...

0
How to Verify Odd and Even Using IF Function in Excel

Verifying whether a number is odd or even can be a useful task in a variety of situations, such as data analysis or creating formulas for spreadsheets. In ...

0
How to Use Multiple IFERROR Statements in Excel (2 Smart Ways)

Microsoft Excel is a powerful tool for organizing and analyzing data, but sometimes our data isn't always clean and error-free. That's where the IFERROR ...

0
Plotting Vectors in Excel (with Easy Steps)

Plotting a vector in Microsoft Excel allows you to visualize the data contained in the vector in the form of a graph or chart. This can be useful for seeing ...

Browsing All Comments By: Naimul Hasan Arif
  1. Reply Avatar photo
    Naimul Hasan Arif Jul 19, 2022 at 3:59 PM

    Hello MARIA, thanks for the comment and sorry for my late reply.
    In the LOOKUP function, the first argument is Lookup_value. I have typed 2 just to signify the NUMBER format. You can type any number. It will work just fine.

  2. Reply Avatar photo
    Naimul Hasan Arif Aug 25, 2022 at 3:53 PM

    Thanks all of you guys for your comments.
    I think so many people are facing the same problem of applying the code in a range of cells. In our article, the VBA code that we have shown only works for a fixed cell. So, I am going to give you guys a slightly modified VBA code that will work for a range of cells( i.e. entire D column).

    Private Sub Worksheet_Change(ByVal Target As Range)

    Dim Oldvalue As String
    Dim Newvalue As String
    Dim mn As Range, pq As Range
    On Error GoTo Exitsub
    Set mn = Range(“D:D”)
    For Each pq In mn
    If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
    GoTo Exitsub
    Else: If Target.Value = “” Then GoTo Exitsub Else
    Application.EnableEvents = False
    Newvalue = Target.Value
    Application.Undo
    Oldvalue = Target.Value
    If Oldvalue = “” Then
    Target.Value = Newvalue
    Else
    Target.Value = Oldvalue & “, ” & Newvalue
    End If
    End If
    Next pq
    Application.EnableEvents = True
    Exitsub:
    Application.EnableEvents = True
    End Sub

  3. Reply Avatar photo
    Naimul Hasan Arif Aug 25, 2022 at 4:52 PM

    As the scale is different for each row, you can apply conditional formatting separately on each row with different colors. I hope that’s the simplest way to do so.

  4. Reply Avatar photo
    Naimul Hasan Arif Jan 18, 2024 at 11:44 AM

    Hello AB,
    Thanks for your response. We can certainly insert multiple photos in one row or column in a simple way. Just follow the following steps:

    1. Select your data in ascending order as the images will be inserted in the ascending order. For this, go to:
      Select data > Home tab > Sort & Filter feature > Sort A to Z.
    2. Now, go to Page Layout mode from the View tab and set the cell height and width.
    3. Select the first cell where you want to insert the first image.
    4. To insert multiple images, go to the Insert tab > Picutres feature > Place over Cells option > This Device…

    5. Right-click on the mouse and select Size and Properties…
    6. Uncheck the Lock aspect ratio option.
    7. Adjust the image size according to the cell size.
    8. Set the last image manually.
    9. Now, select the rest images and align left from Picture Format.
    10. Now. distribute the images in a column with Distribute Vertically.

    Thus, you will have all the images in a single column.

    Similarly, you cam insert multiple photos in a single row.

    Let me know if this is helpful for you or not.

    Regards,
    Naimul Hasan Arif

  5. Reply Avatar photo
    Naimul Hasan Arif Nov 5, 2023 at 11:10 AM

    Hello CJ,

    Thanks for your comment. To go into the details of your queries, let me break down the formula for you used in method 6 in a simpler form first.
    The whole formula was:
    =IFERROR(INDEX($D$5:$D$11, MATCH(ROWS($D$10:D10), COUNTIF($D$5:$D$11, “<=”&$D$5:$D$11), 0)), “”)

    Here, the ROWS function counts the number of rows in the defined array.
    ROWS($D$10:D10)1

    The COUNTIF function compares the values in the given range and denotes them with a number based on the position in the smallest to largest order.
    COUNTIF($D$5:$D$11, “<=”&$D$5:$D$11){4;6;1;5;2;3;7}

    The MATCH function compares the values returned by the ROWS & COUNTIF functions and returns the index number of the position of the exact match.
    MATCH(1, COUNTIF($D$5:$D$11, “<=”&$D$5:$D$11), 0))
    MATCH(1, {4;6;1;5;2;3;7})3

    The INDEX function returns the third date value from the defined range in general form.
    INDEX($D$5:$D$11, MATCH(ROWS($D$10:D10), COUNTIF($D$5:$D$11, “<=”&$D$5:$D$11), 0))
    INDEX($D$5:$D$11, 3)43811
    If there is an error in finding a date, the IFERROR function will return a blank cell as an output.

    While applying the ROWS function, I have set a reference point from D10 and also finished the array on D10. That returns the number of row count 1. However, it is not mandatory that you have to set the reference point from D10. You can start from any cell between D5 to D11 but the starting and ending cell reference should be the same in that array. You can apply “ROWS($D$5:D5)” and it will return 1 too which is the same output.

    If I am not wrong, the IFERROR function was introduced in the 2007 Excel version and the INDEX, MATCH, COUNTIF, & ROWS functions are available in the earliest Excel versions too. So, I hope it will work perfectly from the 2007 and the later Excel versions.
    This formula can be applied to a dynamic table. It will automatically sort dates within the given range.

    I hope you have the answers that you were looking for.

    Regards,
    Naimul Hasan Arif

  6. Reply Avatar photo
    Naimul Hasan Arif Sep 7, 2023 at 12:46 PM

    Hello SAPTARSHI,
    I have personally sent a mail with a mail body of more than 255 characters via the “Using VBA Macro to Automatically Send Email Using Outlook to Selected Recipients” method. The receiver got the mail with the full mail body. I have used Microsoft 365 and found no character restriction in this process.
    Regards,
    Naimul Hasan Arif

  7. Reply Avatar photo
    Naimul Hasan Arif Aug 9, 2023 at 11:54 AM

    Hello MANESH KURKUTE,
    Thanks for your response. Yes, we can certainly create the age bucket with negative numbers too. For this, just apply the following formula to have age 0 to -30 to the D-30 Days and -30 to -60 to the D-60 Days.

    =IF(C5<= -30,”D-60 Days”,IF(C5<0,”D-30 Days”,IF(C5<=30,”1-30 Days”,IF(C5<=60,”31-60 Days”,IF(C5<=90,”61-90 Days”,”>90 Days”)))))

    Regards,
    NAIMUL HASAN ARIF
    Exceldemy

  8. Reply Avatar photo
    Naimul Hasan Arif Aug 1, 2023 at 11:20 AM

    Dear ROBERT STREMPKE,
    Thanks for your comment. If you go through this article, you might have known that we can extract the defined information with the barcode through scanning. We can also have the sum of all goods in Excel. If you have multiple handheld readers, you can create a separate worksheet for each barcode scanner and summarize them in a new worksheet. In the summarized sheet, you can define the inventory stock balance and subtract the total sold products summing them from different sheets. I hope you have got what you are searching for.
    Regards,
    MD NAIMUL HASAN

  9. Reply Avatar photo
    Naimul Hasan Arif Jul 27, 2023 at 10:40 AM

    Hello JAKE,
    Thanks for your response.
    The simplest way to highlight rows till a specific cell, instead of the entire row is to select the range till that column before applying the conditional formatting.

    Then, you can apply any of the above methods and have your desired result.

    If you have already applied the conditional formatting and want to change the selection range, go to the Home tab and click on Conditional Formatting. After that, select Manage Rules…

    Now, change the range from the Applies to section and click on OK.

    You will have the selection changed according to your desired cell.

  10. Reply Avatar photo
    Naimul Hasan Arif Jul 2, 2023 at 2:30 PM

    Dear G BHARATHI PRABHA,
    Thanks for your response. I have used exactly the same code for my workbooks and it works perfectly. A little reminder for you that keep both the files in the same folder and keep both the files open. In order to avoid the error Subscript out of range, Try to run the code by keeping both the files open. I hope this will solve your problem.
    Regards,
    Naimul Hasan Arif

  11. Reply Avatar photo
    Naimul Hasan Arif Jun 12, 2023 at 3:45 PM

    Dear GRAHAM,
    I am using Office365 and I am getting my links retained from word to Excel. You can send us your file to have more clear solution.
    Regards,
    Naimul Hasan Arif

  12. Reply Avatar photo
    Naimul Hasan Arif May 28, 2023 at 12:41 PM

    Dear TURAN,
    Thanks for your valuable comment. That was an honest mistake from me. I have updated the error. I have also modified the code as I have found a simpler way to perform the same task.

    Best Regards,
    Naimul Hasan Arif

  13. Reply Avatar photo
    Naimul Hasan Arif May 21, 2023 at 12:22 PM

    Dear CHARLES MOREHEAD,
    Thanks for your valuable suggestion. I have considered the suggestion and updated the article.
    Regards,
    Naimul Hasan Arif

  14. Reply Avatar photo
    Naimul Hasan Arif May 7, 2023 at 12:20 PM

    Hello JENELLE CASTRO,

    Thanks for your question. If you have the same password in all the files placed in a certain folder, you can apply the following VBA code.

    Sub UnProtect_Multiple_Files()
    Dim FName As String
    Dim Path As String
    Dim FSearch As String
    
    Path = "C:\Users\Dell\Desktop\Arif\Update\"
    FSearch = "*.xls"
    FName = Dir(Path & FSearch)
    Do While FName <> ""
    Workbooks.Open FileName:=Path & FName
    Workbooks(FName).UnProtect "1234"
    Workbooks(FName).Save
    Workbooks(FName).Close
    FName = Dir()
    Loop
    End Sub

    Make necessary adjustments in the code in the Path and Password sections. I hope this is the solution which you are looking for.

    Regards,
    Naimul Hasan Arif

  15. Reply Avatar photo
    Naimul Hasan Arif May 2, 2023 at 4:26 PM

    Dear PA,
    Thanks for your appreciation and valuable question. If you want to have 3 winners without repetition, you can follow the following procedure.

    Create a column with random values using the following formula.
    =RAND()

    Then, apply the formula mentioned below in another column to rank the random values.
    =RANK(D5,D5:D14)

    Now, Insert the following formula where you want to have the winners’ names.
    =XLOOKUP(C17:C19,E5:E14,C5:C14)

    and press Enter to have multiple winners.

    I hope this is what you are looking for.

    Best regards,
    Naimul Hasan Arif

  16. Reply Avatar photo
    Naimul Hasan Arif May 2, 2023 at 3:05 PM

    Dear MUHAMMAD,
    In order to apply the number to words conversion in all worksheets of a particular workbook., you can create a function and place it in a module. As you have created a function in a module, it will be available in all worksheets of that particular workbook.

    Like I have created a module and a function named number_converting_into_words with the following VBA code.

    Function number_converting_into_words(ByVal MyNumber)
    Dim x_string As String
    Dim whole_num As Integer
    Dim x_string_pnt
    Dim x_string_Num
    Dim x_pnt As String
    Dim x_numb As String
    Dim x_P() As Variant
    Dim x_DP
    Dim x_cnt As Integer
    Dim x_output, x_T As String
    Dim x_my_len As Integer
    On Error Resume Next
    x_P = Array("", "Thousand ", "Million ", "Billion ", "Trillion ", " ", " ", " ", " ")
    x_numb = Trim(Str(MyNumber))
    x_DP = InStr(x_numb, ".")
    x_pnt = ""
    x_string_Num = ""
    If x_DP > 0 Then
    x_pnt = " point "
    x_string = Mid(x_numb, x_DP + 1)
    x_string_pnt = Left(x_string, Len(x_numb) - x_DP)
    For whole_num = 1 To Len(x_string_pnt)
    x_string = Mid(x_string_pnt, whole_num, 1)
    x_pnt = x_pnt & get_digit(x_string) & " "
    Next whole_num
    x_numb = Trim(Left(x_numb, x_DP - 1))
    End If
    x_cnt = 0
    x_output = ""
    x_T = ""
    x_my_len = 0
    x_my_len = Int(Len(Str(x_numb)) / 3)
    If (Len(Str(x_numb)) Mod 3) = 0 Then x_my_len = x_my_len - 1
    Do While x_numb <> ""
    If x_my_len = x_cnt Then
    x_T = get_hundred_digit(Right(x_numb, 3), False)
    Else
    If x_cnt = 0 Then
    x_T = get_hundred_digit(Right(x_numb, 3), True)
    Else
    x_T = get_hundred_digit(Right(x_numb, 3), False)
    End If
    End If
    If x_T <> "" Then
    x_output = x_T & x_P(x_cnt) & x_output
    End If
    If Len(x_numb) > 3 Then
    x_numb = Left(x_numb, Len(x_numb) - 3)
    Else
    x_numb = ""
    End If
    x_cnt = x_cnt + 1
    Loop
    x_output = x_output & x_pnt
    number_converting_into_words = x_output
    End Function

    Now, you can call the function in all worksheets of that particular workbook in the following way and have your number converted into words.

    I hope you have found your desired answer.

    Your regards,
    Naimul Hasan Arif

  17. Reply Avatar photo
    Naimul Hasan Arif May 2, 2023 at 12:48 PM

    Dear S. NARASIMHAN,
    Thanks for your valuable comment. No, you do not need to pay any amount of dollars to have your question answered.

    To clarify your query, I have considered a case where I have Product Name in B5:B9 but their sales value is in different columns in 3 different sheets. For sheet named January, the sales amount is in D5:D9 and for February March sheets, the sales amounts are in E5:E9 & G5:G9. Followingly, I have applied the following formula to find the total sales value of iPhone.

    =SUM(VLOOKUP(D12,$B$5:$D$9,{3},FALSE),VLOOKUP(B5,February!$B$5:$E$9,{4},FALSE),VLOOKUP(B5,March!$B$5:$G$9,{6},FALSE))

    Here, D12 refers to the product name which is to look for in the Product Name column, and return output from Sales to have the summation. As you can see in the formula, I have used {3}{4}, and {6} to define the column having sales amount referring to the first column of the look-up range as 1.
    I hope you have your desired output from the above discussion.

    Your Regards,
    Naimul Hasan Arif

  18. Reply Avatar photo
    Naimul Hasan Arif Apr 12, 2023 at 4:29 PM

    Dear LUIS FERNANDO,
    Thanks for the appreciation. Also big thanks to you for sharing your insights on the query of CHRISTOFER.

    In response to your purpose, I have considered a case where I will have a drop-down with the names of the writers. Based on the writer selection, his books will appear in another drop-down just below the cell of the writer’s name.

    I have assigned the following code in a button where I will have writers’ names without repetition under Uniquelist column.  I have created a drop-down in cell C14 with those values. Similarly, I have sorted the matched books under Sorted Books column with the writer’s name in C14.

    Sub Uniquelist()
    
    Set Rng = Range("B4:B11")
    Set Rng2 = Range("E4")
    
    Rng2.Cells(1, 1).Value = Rng.Cells(1, 1)
    Count = 1
    
    For i = 2 To Rng.Rows.Count
        Found = False
        For j = 1 To Rng2.Rows.Count
            If Rng.Cells(i, 1).Value = Rng2.Cells(j, 1) Then
                Found = True
                Exit For
            End If
        Next j
        
        If Found = False Then
            Set Rng2 = Range(Cells(4, 5), Cells(4 + Count, 5))
            Cells(4 + Count, 5) = Rng.Cells(i, 1)
            Count = Count + 1
        End If
            
    Next i
    
    With Range("C14").Validation
                        .Delete
                        .Add Type:=xlValidateList, _
                        AlertStyle:=xlValidAlertStop, _
                        Operator:=xlBetween, Formula1:="=$E$4:$E$6"
    End With
    
    Range("$E$13:$E$15").ClearContents
    
    n = 0
    For i = 1 To Rng.Rows.Count
        If Rng.Cells(i, 1) = Cells(14, 3).Value Then
            Cells(13 + n, 5) = Rng.Cells(i, 2)
            n = n + 1
        End If
    Next i
    
    
    With Range("C15").Validation
                        .Delete
                        .Add Type:=xlValidateList, _
                        AlertStyle:=xlValidAlertStop, _
                        Operator:=xlBetween, Formula1:="=$E$13:$E$15"
    End With
    
    End Sub

    Now, select a writer name from drop-down and click on Sort button. You will have the related books’ name in the following drop-down and choose a book according to your preference.

  19. Reply Avatar photo
    Naimul Hasan Arif Apr 5, 2023 at 4:25 PM

    Thanks a lot OSCAR APPELGREN for your questions.
    In response to your first question to have matched rows entirely in the drop-down, it is not possible in Excel. A standard drop-down list in Excel does not support multiple rows of data.
    If you want to filter multiple rows based on single or multiple criteria, you better use the FILTER function. It is not applicable to the older Excel version. It helps you to extract multiple matched rows quite easily than INDEX-MATCH functions.

    According to your requirements to extract matched rows based on single criteria (i.e. year 2025), I have used the following formula which works pretty well.

    =FILTER(B3:E17,D3:D17=H4)

    And, for sorting rows with multiple criteria, you just need to use the asterisk sign(*) and insert the other condition.

    =FILTER(B3:E17,(D3:D17=H4)*(E3:E17=H5))

     

  20. Reply Avatar photo
    Naimul Hasan Arif Apr 5, 2023 at 12:02 PM

    Thanks a lot BABALOLA for the appreciation. It means a lot.
    In response to your first question, let me break down the whole formula with IFERROR, INDEX, MATCH & SUM and explain it to you.

    The first part of the formula here is IFERROR(INDEX(Jan!AH$9:AH$13,MATCH($B10,Jan!$B$9:$B$13,0)),0)
    First of all the MATCH function looks through the value in range B9:B13 of Jan sheet whether it matches the value in cell B10. If it gets matched, it will return the related value according to the index from the AH9:AH13 range. The IFERROR Function is used to return a value(i.e. 0) if it can not find any proper value to return.
    Similarly, I have gone through all 12 months’ sheets and added them with the SUM function.

    In response to your second question, the sign is used to concatenate cell AH$8 with the letter H. As it is considered a half match, 0.5 is multiplied. We have considered two half-matches to have the full match count.

  21. Reply Avatar photo
    Naimul Hasan Arif Apr 5, 2023 at 10:26 AM

    Thank you PETE BAGALAYOS for your comment.
    Yes, the change persists. Once you convert a variable from string to long, it remains in the long format. Well, if you need the string format, you will have to convert it from long to string again.

  22. Reply Avatar photo
    Naimul Hasan Arif Mar 9, 2023 at 5:35 PM

    Dear SRECKO SELENDIC,
    Thanks for your appreciation. It means a lot. In order to set sheet names based on cell reference, we can use a For loop along with Worksheets.name property. Here, I have written a code to split sheet and rename sheet split sheet keeping the main sheet name unchanged. Follow the following code to do so.

    Sub SplitSheet()
    Dim Rng As Range
    Dim xRow As Range
    Dim SplitRow As Integer
    Dim xSheet As Worksheet
    On Error Resume Next
    xTitleId = "ExcelSplit"
    Set Rng = Application.Selection
    Set Rng = Application.InputBox("Range", xTitleId, Rng.Address, Type:=8)
    SplitRow = Application.InputBox("Row Number Split", xTitleId, 5, Type:=1)
    Set xSheet = Rng.Parent
    Set xRow = Rng.Rows(1)
    Application.ScreenUpdating = False
    For i = 1 To Rng.Rows.Count Step SplitRow
    resizeCount = SplitRow
    If (Rng.Rows.Count - xRow.Row + 1) < SplitRow Then resizeCount = Rng.Rows.Count - xRow.Row + 1
    xRow.Resize(resizeCount).Copy
    Application.Worksheets.Add after:=Application.Worksheets(Application.Worksheets.Count)
    Application.ActiveSheet.Range("A1").PasteSpecial
    
    For j = 2 To Worksheets.Count
    Worksheets(j).Name = "DE" & j - 1
    Next j
    
    Set xRow = xRow.Offset(SplitRow)
    Next
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    End Sub 

    The output will be like the following image.

  23. Reply Avatar photo
    Naimul Hasan Arif Mar 1, 2023 at 5:14 PM

    Dear JEFF,
    Thanks for your comment. Here, the first method not only makes Excel full-screen but also hides the title bar along with tabs and ribbons.

    The other methods also make Excel full-screen along with hiding tabs and ribbons but can’t manage to hide the title bar.

  24. Reply Avatar photo
    Naimul Hasan Arif Mar 1, 2023 at 5:12 PM

    Hello Mark,
    Glad to hear from you. There might be one of several reasons for not having the desired output. If the file is corrupted, protected, or scanned, there will be problem in extracting data. It will be helpful for me to specify the problem if you could send me the file and the code at [email protected].

  25. Reply Avatar photo
    Naimul Hasan Arif Mar 1, 2023 at 3:19 PM

    Hello QUAN,
    Thanks for your question. Here, {2,3} defines the cell number of the defined range B5:D10. 2 defines the row number and 3 defines the column number. Setting B5 as the base point and moving two rows and three columns, we will have the D6 cell which is expressed here with {2,3}.

  26. Reply Avatar photo
    Naimul Hasan Arif Jan 23, 2023 at 2:54 PM

    Thanks for your appreciation. It means a lot.
    To solve your problem, I will suggest you to change your separator from Control Panel. I hope it’ll solve the separator problem.

  27. Reply Avatar photo
    Naimul Hasan Arif Jan 18, 2023 at 11:43 AM

    Thank you very much CHARLES DARWALL for your comment. The replies to your 2 problems is  mentioned below:
    In my case, the VBA works quite fine. Here, I have Kentucky as the last member in the States List column which is also the last member in the drop-down in cell B13.

    Now, I have removed Kentucky as the last value in the States List column. So, Nevada is now the last value in the States List column which is also the last value in the drop-down.

    After that, I have added Washington D.C. as the latest last member which has also been updated automatically in cell B12.

    On the topic of your second question about being able to add a state in row 15 even if C is empty is because I have selected the range B5:B15 to add the drop-down list with the values in States List.

    data validation

  28. Reply Avatar photo
    Naimul Hasan Arif Dec 20, 2022 at 6:00 PM

    Hello BHAVNESH,
    Have a look at Article 1 and Article 2. I hope it will help you to meet your desire.

  29. Reply Avatar photo
    Naimul Hasan Arif Dec 19, 2022 at 5:05 PM

    In general, a new row is inserted above the selected cell when we use the insert command in VBA. In order to insert a new row/line, we need to modify the code. You can use the following code to do so.

    Sub InsertEntireRow()
    Selection.Offset(1, 0).EntireRow.Insert
    End Sub
  30. Reply Avatar photo
    Naimul Hasan Arif Dec 19, 2022 at 11:04 AM

    Thanks for your response.
    Normally additional information associated with Barcode is connected through a centralized dataset. When a UPC (Universal Product Code) code is scanned, the scanner sends the code to a server, which looks up the corresponding information in the database and sends it back to the scanner. In other cases like QR codes, the additional information might not be stored in a centralized dataset. QR codes can encode a URL that will take you to the related information regarding the product through scanning.

  31. Reply Avatar photo
    Naimul Hasan Arif Dec 12, 2022 at 11:23 AM

    Thank you CARLOS for your concern.
    As far as I am concerned I have followed the proper calculation process. There is an output difference between the arithmetic mean and the value using GEOMEAN. That’s the reason I think for the variation between the results. For further queries, you can contact [email protected].

  32. Reply Avatar photo
    Naimul Hasan Arif Dec 11, 2022 at 12:34 PM

    Hello VULSTA KUZENA,
    Thanks for your comment. In my case, the code runs just perfect. I have not got any error message. There might have some extra characters in that place. You can try it or send me the file to [email protected] to let me have a try.

  33. Reply Avatar photo
    Naimul Hasan Arif Nov 30, 2022 at 2:56 PM

    Hello DAMON,
    The process should work perfectly. It would be very helpful for me if you could just send me the file to give it a try.

  34. Reply Avatar photo
    Naimul Hasan Arif Nov 30, 2022 at 10:53 AM

    Hello JACKIE,
    Thanks for your comment. If we use these formulae, it will generate the date from that device (PC/Laptop/Mobile). So, check the date of your device first.
    I think it will solve your problem.

  35. Reply Avatar photo
    Naimul Hasan Arif Nov 20, 2022 at 4:57 PM

    Thanks SARAH for your comment and appreciation.
    You can use the following VBA code with necessary changes to apply your code.

    Sub AddRowFromAnotherSheet()
    Sheets(“sheet1”).Range(“B7:E7”).Copy Sheets(“sheet3”).Range(“B9:E9”)
    End Sub

  36. Reply Avatar photo
    Naimul Hasan Arif Nov 20, 2022 at 11:23 AM

    Hello Darko,
    Thanks for your valuable comment. This problem should not occur if the values are generated within Excel. However, This problem might happen if you copy and paste data from the web pages. In that case, there might have some non-printable characters which are preventing you from sorting data.
    To check if there are any non-printable characters, use the LEN function to have the length of those cells.
    If they are not giving proper output, apply the following formula to remove the non-printable characters.
    =CLEAN(A1)*1
    After that, I hope the Sort function will work perfectly.

  37. Reply Avatar photo
    Naimul Hasan Arif Nov 15, 2022 at 12:45 PM

    If you look at our method 5, we have tried to apply conditional formatting based on a certain condition. The value was fixed all along the applied range.

    But with changing conditional values, we need more detailed information. Please contact us through mail or any social media with your dataset.

  38. Reply Avatar photo
    Naimul Hasan Arif Nov 15, 2022 at 10:42 AM

    Definitely possible. You just need to follow the following procedures to do so.
    After selecting the entire dataset, go to the Insert tab. Followingly, click on Filled Map from the Maps option and you will have your desired output.

  39. Reply Avatar photo
    Naimul Hasan Arif Nov 10, 2022 at 1:57 PM

    I will be glad if I could help you even a little.
    The most important think here is to be careful about the code range. Input the range in the code according to your dataset.
    For more simplification, you can try the following code where the code runs in range B3:B12 and sorts the values in the Ascending order.

    Private Sub AutoSort(ByVal Target As Range)
    If Not Intersect(Target, Range(“B:B”)) Is Nothing Then
    Range(“B3:B12”).Sort Key1:=Range(“B3”), Order1:=xlAscending, Header:=xlNo
    End If
    End Sub

    I hope it’ll run perfectly.

  40. Reply Avatar photo
    Naimul Hasan Arif Nov 10, 2022 at 11:40 AM

    Thanks for your valuable comment.
    A few factors might play a vital role in your problem.
    1. As the file contains VBA code, the file must be saved in “.xlsm” format.
    2. Don’t forget to change the ranges in your code. I have applied the code in D column. So, it’ll only be applicable in the D column.

  41. Reply Avatar photo
    Naimul Hasan Arif Nov 7, 2022 at 11:31 AM

    Hi ISE. Thanks for your comment. Actually the date format is defined from the Number format. You can choose your date format from Number Format under the Home tab. There is no need to add VBA code to define date format.

  42. Reply Avatar photo
    Naimul Hasan Arif Nov 7, 2022 at 10:41 AM

    Thank you AHAMED for your query.
    For the explanation on how to mail merge Excel to Excel, you can check the following link:
    https://www.exceldemy.com/mail-merge-from-excel-to-excel/
    I hope it will fulfill your need.

  43. Reply Avatar photo
    Naimul Hasan Arif Nov 6, 2022 at 5:57 PM

    Thanks for your appreciation. The solutions mentioned here should work perfectly. It would be helpful for us if you could kindly send the template. It would let us give a try.

  44. Reply Avatar photo
    Naimul Hasan Arif Nov 6, 2022 at 4:16 PM

    Thanks for your comment. Look, I have used the following formula to Find If A Range of Cells Contains Specific Text in Excel.

    =COUNTIF(B5:B19,”*”&D5&”*”)>0

    Here, I have just mentioned the range B5:B19. So, in this way, it is not mandatory to know the exact row where Winter is Coming is written.

  45. Reply Avatar photo
    Naimul Hasan Arif Oct 11, 2022 at 7:32 PM

    Thanks XAVIER for your correction.

    Actually, there is just a little mistake in the code. Instead of writing B5:B, it’s been written B5:B10. This correction has worked perfectly for me.


    You can submit more problems to us at [email protected]. Regards!

  46. Reply Avatar photo
    Naimul Hasan Arif Oct 10, 2022 at 5:11 PM

    Thanks for your query.

    It’s kind of a complicated task to filter specific data from certain cells of different worksheets with certain condition. I have tried a possible simple solution to pull data from different sheets into one sheet using FILTER function.I have used the following dataset for filtering the rows having Geller as Last Name and replace the Geller word with blank.

    I have used the following formula to fulfill the purpose.

    =FILTER(IF(Dataset!B4:J17=”Geller”,””,Dataset!B4:J17),Dataset!C4:C17=”Geller”)

    For further information related to Excel, you can send message via email. email id: [email protected] 

  47. Reply Avatar photo
    Naimul Hasan Arif Oct 3, 2022 at 5:12 PM

    According to your requirements, you wanted to display the last 3 months’ data by a graph. You can follow the following procedure where I have tried to give you a simple solution that will help you display the last 3months’ data by graphical representation..

    Create a new column, input the following formula in the 1st cell of that column and AutoFill till the cell you need. This additional column will define with numerical value the last 3 rows containing data.

    =IF(AND(D4>0,ISBLANK(D5)),1,IF(B5=1,2,IF(B5=2,3,””)))

    Next, create a Pivot Table with Months as Filters, Last 3 Months as Axis, and Sum of Product 1, Sum of Product 2, and Sum of Product 3 as Values.

    Now, choose your preferred graphical representation format to display the last 3 months’ data.

     

    Note: Don’t forget to refresh the Pivot Table after inserting the new month’s data. Otherwise, the graph won’t get updated. Alternatively, you can use Auto Update Pivot Table to lessen your hustle.

  48. Reply Avatar photo
    Naimul Hasan Arif Sep 25, 2022 at 3:25 PM

    Thanks for sharing your valuable thoughts.

  49. Reply Avatar photo
    Naimul Hasan Arif Sep 25, 2022 at 2:46 PM

    You can apply a formula combining VALUE and RIGHT functions to get the last 4 digits.
    For example- you can use the following formula to get the last 4 digits of cell C5.
    =VALUE(RIGHT(C5,4))
    After that, apply the COUNT function to count the number of cells in that column.
    I hope you wil get what you are looking for.

  50. Reply Avatar photo
    Naimul Hasan Arif Sep 25, 2022 at 10:38 AM

    There is social media connection link in the introduction section about author. You can send message there.

  51. Reply Avatar photo
    Naimul Hasan Arif Sep 4, 2022 at 2:11 PM

    Thanks for the appreciation.
    In my case, it works just fine. It is very tough for me to give a solution without analyzing your code related to the dataset. It would be helpful for me if you could provide me your code.

  52. Reply Avatar photo
    Naimul Hasan Arif Sep 4, 2022 at 1:01 PM

    Use of “for loop” function is a very simple approach for this purpose. You can use the following code to merged the defined cells to 100 tabs. Based on your sheets, you just need to change the value of “i” in the code.

    Option Explicit
    Public Sub FitTheMergedCells()
    Call MergedCellsAutoFit(Range(“B5:C6”))
    Call MergedCellsAutoFit(Range(“B7:C8”))
    Call MergedCellsAutoFit(Range(“B9:C11”))
    Call MergedCellsAutoFit(Range(“B12:C12”))
    End Sub
    Public Sub MergedCellsAutoFit(gg As Range)
    Dim aa As Integer
    Dim bb As Integer
    Dim cc As Single
    Dim dd As Single
    Dim ee As Single
    Dim ff As Single
    Dim i As Integer
    For i = 1 To 100
    With Sheets(“Sheet” & i)
    cc = 0
    For bb = 1 To gg.Columns.Count
    cc = cc + .Cells(1, gg.Column + bb – 1).ColumnWidth
    Next bb
    cc = .Cells(1, gg.Column).ColumnWidth + .Cells(1, gg.Column + 1).ColumnWidth
    gg.MergeCells = False
    ee = Len(.Cells(gg.Row, gg.Column).Value)
    dd = .Range(“ZZ1”).ColumnWidth
    .Range(“ZZ1”) = Left(.Cells(gg.Row, gg.Column).Value, ee)
    .Range(“ZZ1”).WrapText = True
    .Columns(“ZZ”).ColumnWidth = cc
    .Rows(“1”).EntireRow.AutoFit
    ff = .Rows(“1”).RowHeight / gg.Rows.Count
    .Rows(CStr(gg.Row) & “:” & CStr(gg.Row + gg.Rows.Count – 1)).RowHeight = ff
    gg.MergeCells = True
    gg.WrapText = True
    .Range(“ZZ1”).ClearContents
    .Range(“ZZ1”).ColumnWidth = dd
    End With
    Next i
    End Sub

  53. Reply Avatar photo
    Naimul Hasan Arif Aug 30, 2022 at 12:30 PM

    Thanks for your appreciation and for sharing your modified code.

  54. Reply Avatar photo
    Naimul Hasan Arif Aug 30, 2022 at 12:27 PM

    Yeah. There are ways to sort columns in descending order.
    You can apply the following VBA in the dataset used in the first method to sort the data in descending order.

    Sub SortSingleColumnWithoutHeader()
    Range(“B5”, Range(“B5”).End(xlDown)).Sort Key1:=Range(“B5”), Order1:=xlDescending, Header:=xlNo
    End Sub

  55. Reply Avatar photo
    Naimul Hasan Arif Aug 30, 2022 at 12:05 PM

    Thanks ANDREW for your query.

    You can check out the following formula that I have applied with the dataset mentioned in the image.

    =INDEX(B5:E12,MATCH($B$15,$D$5:$D$12,0),0)

    It gives the entire row with the first matched value. But it will not give all the matched rows as output. You can modify the formula using any aggregate function like SMALL to get all the rows at one go.

  56. Reply Avatar photo
    Naimul Hasan Arif Aug 25, 2022 at 3:35 PM

    I have tried the following VBA code for all the cells in D Column. I hope this is the thing you are looking for.

    Private Sub Worksheet_Change(ByVal Target As Range)

    Dim Oldvalue As String
    Dim Newvalue As String
    Dim mn As Range, pq As Range
    On Error GoTo Exitsub
    Set mn = Range(“D:D”)
    For Each pq In mn
    If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
    GoTo Exitsub
    Else: If Target.Value = “” Then GoTo Exitsub Else
    Application.EnableEvents = False
    Newvalue = Target.Value
    Application.Undo
    Oldvalue = Target.Value
    If Oldvalue = “” Then
    Target.Value = Newvalue
    Else
    Target.Value = Oldvalue & “, ” & Newvalue
    End If
    End If
    Next pq
    Application.EnableEvents = True
    Exitsub:
    Application.EnableEvents = True
    End Sub

  57. Reply Avatar photo
    Naimul Hasan Arif Aug 21, 2022 at 12:05 PM

    You can apply the INDEX – MATCH functions combination to find out whether the value is matching with “2 ” or not in row 23 and then, use the INDIRECT function to retrieve the matched value with the value in row 6.

  58. Reply Avatar photo
    Naimul Hasan Arif Aug 21, 2022 at 12:01 PM

    Thanks for your appreciation. It means a lot.

  59. Reply Avatar photo
    Naimul Hasan Arif Aug 17, 2022 at 5:48 PM

    Unfortunately for some strange reasons, the formulas with the INDEX function don’t behave properly while starting from any other row but row 1. But luckily you have some other alternatives like the FILTER function in case your dataset start from row6

  60. Reply Avatar photo
    Naimul Hasan Arif Aug 17, 2022 at 3:02 PM
    Definitely possible. You just need to concatenate the values in a cell, can use the TEXTJOIN function to do so.

    I have the use the following formula for this case.

    =TEXTJOIN(“, “,TRUE,IF(F5=$C$5:$C$15,$B$5:$B$15,””))

  61. Reply Avatar photo
    Naimul Hasan Arif Aug 17, 2022 at 12:16 PM

    Thanks for your query.
    Yes, it is possible to apply multiple criteria. Not sure how your data looks, but according to your query, I have tried to reorganize it as follows to categorize names by region based on Vehicle.

    =INDEX(B5:B15,MATCH(1,(B18=$D$5:$D$15) * (C18=$C$5:$C$15),0))

  62. Reply Avatar photo
    Naimul Hasan Arif Aug 16, 2022 at 3:34 PM

    Thanks for your insight and for trying something on your own.

  63. Reply Avatar photo
    Naimul Hasan Arif Aug 16, 2022 at 2:52 PM

    I’m afraid in an ideal scenario once you’ve used data validation with list of items to be selected, you will encounter an error since Excel anticipates values from that list only not any free text.

  64. Reply Avatar photo
    Naimul Hasan Arif Aug 16, 2022 at 2:49 PM

    You can use the Clear property of the cell. You need to apply Range(“__your cell__”).Clear to make the selection cleared.

  65. Reply Avatar photo
    Naimul Hasan Arif Aug 16, 2022 at 2:46 PM

    You can wrap your code in a loop to iterate continuously across a column if you want to traverse it.

  66. Reply Avatar photo
    Naimul Hasan Arif Aug 16, 2022 at 2:44 PM

    If you want to traverse through a column, you can wrap your code with loop which will continue throughout the column.

  67. Reply Avatar photo
    Naimul Hasan Arif Aug 16, 2022 at 2:22 PM

    As far as I’ve understood you are wanting to get value with category criteria, where for different categories items under those categories will show up. I’ve tried to visualize that like the following.

    you can get the Products with respect to the category by using the following formula

    =IF(ROWS($F$5:F5)>$C$15,” “,INDEX($B$5:$B$12,AGGREGATE(15,6,(ROW($C$5:$C$12)-ROW($C$5)+1)/($C$5:$C$12=$E$5),ROWS($F$5:F5))))

    The Count column is obtained using the COUNTIFS function. =COUNTIFS($C$5:$C$12,B15). Feel free to access the reply-workbook.

  68. Reply Avatar photo
    Naimul Hasan Arif Aug 16, 2022 at 9:17 AM

    Hi,

    For columns A:
    From the Data tab, select Data Tools and pick Data Validation from there. This will open the Data validation feature. From there, mention the text length of minimum “1” to maximum “8”.

    For column B, follow the same procedure. Just in the maximum section, input “10”.

  69. Reply Avatar photo
    Naimul Hasan Arif Aug 16, 2022 at 9:16 AM

    Thanks for the appreciation.
    For columns A & B:
    From the Data tab, select Data Tools and pick Data Validation from there. This will open the Data validation feature. From there, mention the text length of minimum “1” to maximum “8”.

    For column C, follow the same procedure. Just in the maximum section, input “10”.

  70. Reply Avatar photo
    Naimul Hasan Arif Aug 14, 2022 at 5:30 PM

    You can try using SpecialCells(xlCellTypeVisible) property while setting the detailsLastRow object. SpecialCells(xlCellTypeVisible) will trigger Excel to consider visible cells only. I hope this edited VBA will help you to get your desired output.

  71. Reply Avatar photo
    Naimul Hasan Arif Aug 14, 2022 at 12:21 PM

    In case of making some effective lookup, every input should be unique (unless you are in any particular case) to get the related information. If you have some similar names, let’s assume some Employee Names, then use other particulars as input like Employee ID. If you don’t have this type of unique number (ID) column you can create it quite easily, for assistance do check https://www.exceldemy.com/excel-auto-generate-number-sequence/, then apply the lookup. I hope this will solve your problem.

  72. Reply Avatar photo
    Naimul Hasan Arif Jul 19, 2022 at 1:12 PM

    Sorry DIEGO for my late response.
    There needs to make some changes in the formula in case of finding the second last non blank cell.
    I have used the following formula using LOOKUP function in cells D5 to D15 that is the chemistry marks in the dataset to find the second last non-blank cell.
    =LOOKUP(2,1/((D5:D15<>D15)*(D5:D15<>“”)),D5:D15)

    My dataset is given below:

    Second Last Non Blank Value

    Name Physics Chemistry
    Green 164 110 (D5)
    Jack 185 165
    Joey 178 132
    Mark 183 137
    Austin 165 112
    Marvin 173 119
    Mason 186 170
    Mount 170
    Martin 177 160
    Freeman 164
    Federer 163 111 (D15)

    Second Last Non Blank Value 160

    For me it worked perfectly. I hope it will work the same way for you too.

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo