Afia Kona

About author

Afia Aziz Kona, a graduate of Civil Engineering from Khulna University of Engineering & Technology, Bangladesh, serves as a technical content creator in the ExcelDemy project. Possessing a passion for innovation and critical thinking, she actively embraces challenges. Beyond her engineering background, Afia exhibits a keen interest in Excel, having authored numerous articles on Excel & VBA-related issues to simplify the experience for users facing obstacles in Excel. Apart from creating Excel tutorials, she is also interested in Data Analysis with MS Excel, SPSS, Python 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

Content Writing, Microsoft Office, Report Writing

Experience

  • Technical Content Writing
  • Undergraduate Projects
    • Design of a 1000 DWT (Deadweight Tonnage) Inland Cargo Vessel.

Latest Posts From Afia Kona

0
Data Model in Excel (A Complete Guide)

  Download Practice Workbook Data Model.xlsx What Is a Data Model in Excel? The Excel Data Model is a special kind of data table where ...

0
How to Copy and Paste in Excel

In this article, we will discuss and show how to copy and paste into Excel. We will extensively explain the topic with examples for your better understanding. ...

0
Solver in Excel (Everything You Need to Know)

In the image below, we have enabled the Solver Add-in. What is an Excel Solver? The Excel Solver is an add-in tool in Microsoft Excel that helps ...

0
Tally in Excel (All You Need to Know)

In this article, we will talk about and show how to work with tally in Excel. Following some easy and effective techniques, we will do the task smoothly. ...

0
How to Use a Pareto Chart in Excel (2 Suitable Examples)

Wondering how to use the Pareto chart in Excel. Do not worry, we are here for you. In this article, we will describe 2 ways to use a Pareto chart in Excel. ...

0
Excel VBA: InputBox Type with Examples

InputBox is an excellent feature of Excel VBA. Using InputBox, a user can prompt for user input in the form of text, numbers, arrays, and many more formats. ...

0
How to Use a VBA Code in Excel to Click OK on MsgBox

In the following dataset, you can see that the Printer and Battery have no Price. We want to include a MsgBox to show the product names without a Price. We ...

0
How to Find the Sum If the Cell Color Is Green in Excel (4 Easy Methods)

The following dataset has Month and Sales columns. The Sales column has several cells colored Green. Using this dataset, we will find the sum of the cells ...

0
How to Use FORECAST Function with Multiple Variables in Excel (6 Methods)

Let’s break down the methods for using the FORECAST function with multiple variables in Excel to determine Sales for the year 2022. A concise summary for each ...

0
How to Create Calendar Using VBA in Excel (with Easy Steps)

If you want to create an Excel VBA calendar, you have come to the right place. Here, we will walk you through some easy and effective steps to do the task ...

0
How to Sort Odd and Even Numbers in Excel (4 Easy Examples)

If you want to sort odd and even numbers in Excel, you have come to the right place. Here, we will walk you through 4 easy and effective examples to do the ...

0
How to Use Conditional Formatting with the IFERROR Function in Excel – 4 Examples

  The dataset showcases Name, Salary, Week, and Per Week Salary. To determine per week salary by dividing Salary by Week: Use the following ...

0
How to Apply a Vector Formula in Excel – 5 Examples

  What Is Vector Quantity? A quantity with both direction and magnitude is called a Vector quantity. The following dataset showcases vector ...

0
How to Create Data Dictionary in Excel (with Easy Steps)

If you want to create a data dictionary in Excel, you have come to the right place. Here, we will walk you through 3 easy steps to do the task smoothly. ...

0
How to Create a Workflow Tracker in Excel (with Easy Steps)

If you want to create a workflow tracker in Excel, you have come to the right place. Here, we will walk you through 10 easy and suitable steps to do the task ...

Browsing All Comments By: Afia Kona
  1. Hello Mark W
    I hope after reading my reply, you will be able to solve the problem.

    1. First, let me explain to you how the TRIM function works.
    Suppose, you have the name “ Joe Louis “, and you can see this name has leading, middle, and trailing spaces. In that case, the TRIM function will work.
    The result will be like “Joe Louis”.

    However, if the name is like “ Joe Lou is “, the TRIM function will only remove the leading and trailing space of the name. It will not remove the space between letters.
    The final result of the above name will be “Joe Lou is”.

    Now, suppose you have a number like “ 12 24 5 6 “, in this case, the TRIM function will only remove the leading and trailing spaces. Therefore, after applying the TRIM function.
    The number will look like “12 23 5 6”.

    I hope that now you will easily understand in which situations, the TRIM function works.

    2. After that, let me explain to you how the SUBSTITUTE function works.

    If you have a number like “ 1 2 45 7 “ then the SUBSTITUTE function will eliminate all the spaces. Here, suppose the old number is in cell C4. Therefore, you have to type =SUBSTITUTE(C4,” “,””).
    After that, the number will become like “12457”.

    Next, if you have a word where the letters have spaces between them like if you have “ Yell ow “ in a cell, the SUBSTITUTE function will remove the spaces. Here, suppose the old number is in cell C4. Therefore, you have to type =SUBSTITUTE(C4,” “,””).
    The result will be “Yellow”.

    However, if a Text has spaces between words then we have to identify and add the space in the SUBSTITUTE function properly. Let’s say, the name “ Adam Smith “ is present in cell C4. You can easily notice that there are three spaces between Adam and Smith. Along with that, there are lading and trailing spaces. we have to type =SUBSTITUTE(F16,” “,” “), here you to give three spaces in between the first double quote. Along with that, make sure to keep one space between the second double quote, otherwise both the words will merge into one word.
    The outcome will be “Adam Smith”

    I hope that when using the SUBSTITUTE function if you can identify the spaces between words, and add the space properly in the formula, your problem will be solved.

    3. Let us now discuss how Find and Replace works.
    Find and Replace is a useful feature to replace spaces between numbers in a cell. If you want to replace spaces between numbers, Find and Replace is an effective and easy way.

    However, For different numbers of spaces between words, we have to identify those spaces, and in the Find what box we need to press those Exact numbers of spaces. Otherwise, the Find and Replace will not work. Therefore, if different numbers of spaces between text are present in different cells then the cells need a unique number of spaces in the Find what box.

    I hope you can identify the spaces between words, and in the Find what box you can press exactly the same number of spaces. Hence, your problem might be solved.

    4. When different cell content has a different number of spaces, Power Query is extensively useful to remove those spaces.
    As in your comment, you did not mention anything regarding Power Query, I highly suggest you use Power Query. I am hopeful that it will solve your problem.

    Thank you for your comment. I hope you will now be able to solve your problem. If, however, these methods still do not work for you, please share your Excel file in the comment section. This will help me to understand the problem, and I will try my best to solve the problem.
    Regards!

  2. Dear Tanzir,
    Thank you for your comment.
    Arcsine transform is done for real numbers ranging from 0 to 1.
    In the formula, DEGREES(ASIN(SQRT(X/100))), X indicates the percent value to be transformed. Therefore, when we put 50% in the formula it actually becomes 0.5. As a result, we can Arcsine transform the data.
    However, if we put 50 instead of 50% then Arcsine transform is not possible.

    Regards
    Afia Aziz Kona

  3. Dear Kristian,
    Thank you for your comment.
    You can solve your problem by using one of the following procedures:

    1. Using Document Inspector Command
    2. Editing Trust Center for External Content
    3. Inputting Accurate Data Importing File Destination
    4. Setting Refreshing Option in Beginning
    5. Installing an Updated Version of Excel

    You can read the following two articles to get descriptive information.
    https://www.exceldemy.com/excel-external-data-connections-have-been-disabled/
    https://www.exceldemy.com/excel-data-connection-not-refreshing/

    I hope your problem will be solved.

    Best,
    Afia Aziz Kona

  4. Dear Sam,
    Thank you for your comment. I understand that you are looking for a more dynamic Attendance Sheet, if so, you can use the following VBA code as an update:

    
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    'Developed by ExcelDemy
    
    If Target.Address = "$C$4" Or Target.Address = "$C$5" Then
    
        totalDays = Array(31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31)
        
        If Range("C5").Value Mod 4 = 0 Then
            totalDays(1) = 29
        End If
    
        Dim specificDate As Date
        Dim dayOfWeek As Integer
        Dim dayName As String
        
        
        monthNumber = Month(DateValue(Range("C4").Value & " 1 2000"))
        yearNumber = Range("C5").Value
        
        Range(Range("D7").Cells(1, 1), Range("D7").Cells(6, 31 + 2)).ClearContents
        Range(Range("D7").Cells(1, 29), Range("D7").Cells(6, 31 + 2)).ClearFormats
        
        For i = 1 To totalDays(monthNumber - 1)
        
        ' Specify the date
            specificDate = DateSerial(yearNumber, monthNumber, i)
    
        ' Get the day of the week as a number (1 for Sunday, 2 for Monday, etc.)
            dayOfWeek = Weekday(specificDate, vbSunday)
    
        ' Get the name of the day of the week
            dayName = WeekdayName(dayOfWeek, False, vbSunday)
    
        ' Display the name of the day
            Range("D7").Cells(1, i) = i
            Range("D8").Cells(1, i) = Left(dayName, 3)
             
        Next i
        
        Range("D7:D12").Copy
        For i = 29 To totalDays(monthNumber - 1)
            Range("D7:D12").Cells(1, i).PasteSpecial Paste:=xlPasteFormats
            Range("D7:D12").Cells(1, i).ColumnWidth = Range("D7").Cells(1, 1).ColumnWidth
        Next i
        
        Application.CutCopyMode = False
        
        Range("D7:D12").Cells(2, totalDays(monthNumber - 1) + 1) = "Present"
        Range("D7:D12").Cells(2, totalDays(monthNumber - 1) + 1).Interior.Color = vbGreen
        Range("D7:D12").Cells(2, totalDays(monthNumber - 1) + 1).ColumnWidth = 8
        Range("D7:D12").Cells(2, totalDays(monthNumber - 1) + 1).Font.Bold = True
        Range("D7:D12").Cells(2, totalDays(monthNumber - 1) + 1).HorizontalAlignment = xlHAlignCenter
        Range("D7:D12").Cells(2, totalDays(monthNumber - 1) + 1).VerticalAlignment = xlVAlignCenter
        
        Range("D7:D12").Cells(2, totalDays(monthNumber - 1) + 2) = "Absent"
        Range("D7:D12").Cells(2, totalDays(monthNumber - 1) + 2).Interior.Color = vbGreen
        Range("D7:D12").Cells(2, totalDays(monthNumber - 1) + 2).ColumnWidth = 8
        Range("D7:D12").Cells(2, totalDays(monthNumber - 1) + 2).Font.Bold = True
        Range("D7:D12").Cells(2, totalDays(monthNumber - 1) + 2).HorizontalAlignment = xlHAlignCenter
        Range("D7:D12").Cells(2, totalDays(monthNumber - 1) + 2).VerticalAlignment = xlVAlignCenter
        
        For j = 1 To 2
            For k = 1 To 5
                With Range("D7:D12").Cells(2, totalDays(monthNumber - 1) + 1).Cells(k, j)
                    .Borders(xlEdgeTop).LineStyle = xlContinuous
                    .Borders(xlEdgeTop).Color = RGB(0, 0, 0)
                    .Borders(xlEdgeTop).Weight = xlThin
        
                    .Borders(xlEdgeBottom).LineStyle = xlContinuous
                    .Borders(xlEdgeBottom).Color = RGB(0, 0, 0)
                    .Borders(xlEdgeBottom).Weight = xlThin
        
                    .Borders(xlEdgeLeft).LineStyle = xlContinuous
                    .Borders(xlEdgeLeft).Color = RGB(0, 0, 0)
                    .Borders(xlEdgeLeft).Weight = xlThin
        
                    .Borders(xlEdgeRight).LineStyle = xlContinuous
                    .Borders(xlEdgeRight).Color = RGB(0, 0, 0)
                    .Borders(xlEdgeRight).Weight = xlThin
                End With
            Next k
        Next j
    End If
    End Sub
    

    Download this Excel file for a better understanding.
    I hope that your problem will be solved now. If you have any further issue, please let us know in the comment section.

    Best
    Afia Aziz Kona
    Excel and VBA Content Developer
    Exceldemy

  5. Dear DEREK TIERNEY,
    Thank you for your comment.
    After typing the code for the Command Button in Step 8, you have to call the private sub Call D_Display in the previous code. This will solve the problem. If you still face the issue, please attach the Excel file in the comment section.

    Best,
    Afia Aziz Kona

  6. Dear CONCERNEDEXCELUSER,
    Thank you for your comment.
    Please have a look at the graph, the error bar for Study 6 is at the topmost position. And point 1.88 is right next to point 1.84.
    Thank you.

    Regard,
    Afia Aziz Kona
    Content Developer
    Exceldemy

  7. Dear NICK,
    Thank you for your comment. Please follow the second method, Using Format Data Series Option. For a Combo Chart, this method will work.
    I hope your problem will be solved now. If, however, you are still facing an issue, you can send us your Excel file to [email protected].

    Best,
    Afia Aziz Kona
    Excel & VBA Content Developer
    Exceldemy

  8. Dear Damien,
    Thank you for your comment. To get “zero” or “Nil” for the value 0, you have to simply add an IF statement with the code.
    you can use the following code:

     Function number_converting_into_words(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 MyNumber = 0 Then
        number_converting_into_words = "zero"
        
    Else
    
        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 If
    
    End Function
    Function get_hundred_digit(xHDgt, y_b As Boolean)
    Dim x_R_str As String
    Dim x_string_Num As String
    Dim x_string As String
    Dim y_I As Integer
    Dim y_bb As Boolean
    x_string_Num = xHDgt
    x_R_str = ""
    On Error Resume Next
    y_bb = True
    If Val(x_string_Num) = 0 Then Exit Function
    x_string_Num = Right("000" & x_string_Num, 3)
    x_string = Mid(x_string_Num, 1, 1)
    If x_string  "0" Then
    x_R_str = get_digit(Mid(x_string_Num, 1, 1)) & "Hundred "
    Else
    If y_b Then
    x_R_str = "and "
    y_bb = False
    Else
    x_R_str = " "
    y_bb = False
    End If
    End If
    If Mid(x_string_Num, 2, 2)  "00" Then
    x_R_str = x_R_str & get_ten_digit(Mid(x_string_Num, 2, 2), y_bb)
    End If
    get_hundred_digit = x_R_str
    End Function
    Function get_ten_digit(x_TDgt, y_b As Boolean)
    Dim x_string As String
    Dim y_I As Integer
    Dim x_array_1() As Variant
    Dim x_array_2() As Variant
    Dim x_T As Boolean
    x_array_1 = Array("Ten ", "Eleven ", "Twelve ", "Thirteen ", "Fourteen ", "Fifteen ", "Sixteen ", "Seventeen ", "Eighteen ", "Nineteen ")
    x_array_2 = Array("", "", "Twenty ", "Thirty ", "Forty ", "Fifty ", "Sixty ", "Seventy ", "Eighty ", "Ninety ")
    x_string = ""
    x_T = True
    On Error Resume Next
    If Val(Left(x_TDgt, 1)) = 1 Then
    y_I = Val(Right(x_TDgt, 1))
    If y_b Then x_string = "and "
    x_string = x_string & x_array_1(y_I)
    Else
    y_I = Val(Left(x_TDgt, 1))
    If Val(Left(x_TDgt, 1)) > 1 Then
    If y_b Then x_string = "and "
    x_string = x_string & x_array_2(Val(Left(x_TDgt, 1)))
    x_T = False
    End If
    If x_string = "" Then
    If y_b Then
    x_string = "and "
    End If
    End If
    If Right(x_TDgt, 1)  "0" Then
    x_string = x_string & get_digit(Right(x_TDgt, 1))
    End If
    End If
    get_ten_digit = x_string
    End Function
    Function get_digit(xDgt)
    Dim x_string As String
    Dim x_array_1() As Variant
    x_array_1 = Array("Zero ", "One ", "Two ", "Three ", "Four ", "Five ", "Six ", "Seven ", "Eight ", "Nine ")
    x_string = ""
    On Error Resume Next
    x_string = x_array_1(Val(xDgt))
    get_digit = x_string
    End Function 

    Best,
    Afia Aziz Kona

  9. Dear Wietze,
    Thank you for your comment. unfortunately, your question seems unclear to me. If you can provide me with your Excel file and be more specific about your inquiry, I will be able to help you. Please share your Excel file.

    Best,
    Afia Aziz Kona

  10. Dear AAMIR
    Thank you for your comment.
    If you want to make a recorder for only half-day casual leave, you can use the same template in this article. In this case, each “1” will be a half-day leave. And the total will imply a total half day’s leave. Change the header for your needs.

    Also, for better reach, you can review the following article to record a half-day casual leave.
    https://www.exceldemy.com/calculate-half-day-leave-in-excel/

    Best,
    Afia Aziz Kona

  11. Hello Chris,
    Thank you for your comment.
    The option, “Data >> Get Data >> From Other Sources >> From Microsoft Query” should be available in Excel 2019 since Power Query is available in Excel 2019.
    However, since you are unable to find the option, I would suggest you to install a new version of Excel. I hope your problem will be solved now.

    Best,
    Afia Aziz Kona

  12. Dear Deepak,
    Thank you for your comment.
    To show 100 in value 25=100/4, you need to apply the following Custom Cell Format.

    00/4

    Best,
    Afia Aziz Kona

  13. Dear Ella,
    Thank you for your comment. I will try to provide a proper answer to your question.

    The first answer is, to set the value for b0, b1, and b2, you can assume any values. Then, you can optimize the assumed values.

    The answer to the second question is, since we have 2 independent and one dependent variable, we took 3 initial problem-solver variables. One for the dependent variable, and then for each independent variable, we took 1 additional problem solver variable.

    I hope this will help you.

    Regard,
    Afia Aziz Kona

  14. Dear Yos,
    Walaikum’assalam. Thank you so much for your comment. I will try my best to give you a proper solution.
    Firstly, I will show you how you can import a .pdf file to a .txt file, and after that, I will show you the VBA code to open the text file in Excel.

    Let me show you how you can import a .pdf file to a .txt file.

    ● First of all, upload your PDF file to your Google Drive.
    ● Then, from Google Drive >> right-click on the uploaded PDF file.
    ● Then, click on Open With from the Context Menu >> select Google Docs.
    This will open the PDF as a Google Doc file.

    ● After that, go to the File tab of Google Docs >> click on Download.
    ● This will bring out several Download options >> select Plain Text (.txt)
    This will make the Google Docs as a Text file.
    ● Next, open your Text file and see the outcome.

    Next, I will show you how you can open the Text file in Excel using VBA.
    In the beginning, carefully notice the location of the Text file, and also carefully note the name of the file.
    Here, we have marked our text file name.

    This is because we have to implement the file name and location in the VBA code properly.

    null

    Now, it is time to open a VBA Module.
    ● To open a VBA module, open your Excel file >> go to the Developer tab >> select Visual Basic.
    You can also press the ALT+F11 keys.
    This will open a VBA Editor window.
    ● Furthermore, from the Insert tab >> select Module.
    ● Moreover, in the Module, type the following code.

    Sub ConvertToNewWorkbook()
        Dim iBook As Workbook
        Dim iTexts As Workbook
        Dim iSheet As Worksheet
        Set iBook = ThisWorkbook
        Set iSheet = iBook.Sheets(1)
        Set iTexts = Workbooks.Open("C:\ExcelDemy\Student List.txt")
        iBook.Sheets(1).Cells.Copy iSheet.Cells
        iBook.Close SaveChanges:=True
    End Sub

    Here, change the location and name of the text file according to your file.
    ● Afterward, Save the code >> Run the code.
    Therefore, you will see the Excel file will have all the texas.
    I hope this was helpful. Please let us know if you have any additional queries.

    Regard,
    Afia Aziz Kona

  15. Dear W
    Thank you for your comment.
    Let me show you that your formula works properly.

    Here, I created the List column including cells F2:F4.
    Along with that, I created a Product column that includes cell J2.

    Here, as I do not know your actual dataset, I take List and Product according to my choices, however, the cells are the same as your description.

    Further, I type the following formula in cell B4.

    =TEXTJOIN(", ", TRUE, IF(COUNTIF(J2,"*"&$F$2:$F$4&"*"), $F$2:$F$4, ""))
    This is the same formula you mentioned in the comment.

    After that, I press ENTER.
    As a result, you can see the result in cell B4.
    Therefore, the formula works properly.

    I hope that your problem will be solved now.
    If you face any further problems, please share your Excel file with us in the comment section.

    Regards
    Afia Aziz Kona

  16. Dear ABBY SHULER
    Thank you for your comment. Here, for your first problem, to get rid of the #NAME error, you can copy the following VBA code in a new Excel Workbook. The uploaded Excel file sometimes show problem while running on a different computer, therefore, when you will copy the code to a new workbook, the #NAME error will be solved.

    Function AddressCompare(first_string As String, Second_string As String, _
    Comparing_Letters As Integer) As Double

    Dim int_character As Integer, Comparing_LettersMatch As Integer
    Dim n_Gram_List1 As String, n_Gram_List2 As String, n_letter_Gram As Variant
    Dim n_Gram_array1 As Variant

    For int_character = 1 To Len(first_string) – (Comparing_Letters – 1)
    If n_Gram_List1 <> “” Then n_Gram_List1 = n_Gram_List1 & “,”
    n_Gram_List1 = n_Gram_List1 & Mid(first_string, int_character, Comparing_Letters)
    Next int_character

    For int_character = 1 To Len(Second_string) – (Comparing_Letters – 1)
    If n_Gram_List2 <> “” Then n_Gram_List2 = n_Gram_List2 & “,”
    n_Gram_List2 = n_Gram_List2 & Mid(Second_string, int_character, Comparing_Letters)
    Next int_character

    n_Gram_array1 = Split(n_Gram_List1, “,”)

    For Each n_letter_Gram In n_Gram_array1
    If InStr(1, n_Gram_List2, n_letter_Gram) Then
    Comparing_LettersMatch = Comparing_LettersMatch + 1
    End If
    Next n_letter_Gram

    AddressCompare = Comparing_LettersMatch / (UBound(n_Gram_array1) + 1)
    End Function

    After that, save the code and go back to your worksheet. I hope the #NAME error will be gone now.

    For your second query, you can use 3 instead of 2. Using 3 will compare 3 letters at a time, and therefore, the result will decrease the match percentage between two addresses.
    When you will use 2, it will compare 2 letters at a time, and therefore, the match percentage between two addresses will be higher.
    Let me show you that elaborately.
    When we use =AddressCompare(C5, F5, 2) in cell E3, the result becomes 1, which indicates the Exact Match.
    However, the two addresses are not the same, therefore, using 2 does not give an accurate result.

    null

    On the other hand, when we use the formula =AddressCompare(C5, F5, 3) in cell E3, the result becomes 0.970588235, which does not indicate the Exact Match. Rather, it suggests that there is some dissimilarity between the two addresses.
    Therefore, using 3 is wise in your case.

    Here, another thing must be noted, for your address match, you have to set your own creation while using the IF function.
    Let me elaborate on this.
    Here, in cell F3, we type the following formula.
    IF(AddressCompare(C5, F5, 3)>0.5, "Full Match", "No Match")
    Here, in this formula, we give the criteria, that when AddressCompare(C5, F5, 2) is greater than 0.5, the IF function returns “Full Match“. However, when AddressCompare(C5, F5, 2) is not greater than 0.5, the IF function returns “No Match“.
    Therefore, in cell F3 the result is “Full Match“.
    However, there is some dissimilarity between the two addresses. Hence, the result in cell F3 is not accurate.

    To get an accurate result, we will type the following formula in cell F3.

    IF(AddressCompare(C5, F5, 3)>0.99, "Full Match", "No Match")
    Here, in this formula, we give the criteria, that when AddressCompare(C5, F5, 2) is greater than 0.99, the IF function returns “Full Match“. However, when AddressCompare(C5, F5, 2) is not greater than 0.99, the IF function returns “No Match“.
    Therefore, in cell F3 the result is “No Match“.
    This is the correct result.

    I really hope that you get your answer, and that you can solve your problems.
    If you face any problem, you can always let us know.

    Regards,
    Afia Aziz Kona

  17. Dear Ashiliah
    Thank you for your comment.
    Let me now show you how you can solve your problem.
    Based on your description I’ve created the dataset.
    Here, you can see in the following picture that we have Chocolate lava Cake in cell A5. we keep this in Sheet1.

    After that, in Sheet2, we have Lava cake, Strawberry drink, and Banana muffin in cells A5, A6, and A7 respectively.

    Next, we will type the following formula in cell B5 of Sheet1.
    =TEXTJOIN(", ", TRUE, IF(COUNTIF(A5, ""&Sheet2!A5:A6&""),Sheet2!A5:A6, ""))

    After that, press ENTER.
    As a result, you can see Lava cake in column B of sheet1.

    I hope you understand the solution. If you have any problems you can always let us know in the comment section.

    Regard
    Afia Aziz kona

  18. Dear Jacob Smith
    Thank you for your comment.
    The first method works properly when you press the SHIFT key after selecting rows/columns.
    Let me explain this elaborately.
    Here, in the following dataset, we want to move rows 7 and 8.

    To do so, first, we will select cells B7:D8.
    After that, we will hover our mouse cursor to the edge of the selection and wait for it to change into a 4 directional cross.

    At this point, press the SHIFT key.
    Along with that, left-click on it with your mouse and drag your selection to the desired location.
    A green line should appear to assist you to drop it at the desired location.
    Here, we drag the selected rows to Row 3, and therefore, you can see a Green Line at Row 3.
    In addition, you can see B4:D5 on the left side of the Green line which indicates the final location of the selected rows.
    After that, we will release the mouse and SHIFT key.

    As a result, you can see the movement of rows to a new location.

    I hope that you can now use Method-1.
    If you have any problems, you can always let us know in the comment section.

    Regard
    Afia Aziz Kona

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo