User Posts: Afia
0
How to Add Subscript in Excel Graph (7 Easy Methods)
0

If you want to add subscript(s) in an Excel graph, you have come to the right place. Here, we will walk you through 7 easy and effective methods to do the task ...

0
How to Create Monthly Depreciation Schedule in Excel
0

If you want to create a monthly depreciation schedule in Excel, you have come to the right place. Here, we will walk you through some easy methods to do the ...

0
How to Format Data Table in Excel Chart (4 Easy Methods)
0

If you want to format a data table in an Excel chart, you have come to the right place. Here, we will walk you through 4 easy methods to do the task ...

0
How to Use Millions in Data Labels of Excel Chart (3 Easy Ways)
0

If you want to create chart data labels in Millions in Excel, you have come to the right place. Here, we will walk you through 3 easy methods to do the task ...

0
How to Make a Decision Table in Excel (with Easy Steps)
0

If You want to make a decision Table in Excel, you have come to the right place. Here, we will walk you through some easy steps to do the task effortlessly. ...

0
How to Sort Data in Excel Chart (5 Easy Methods)
0

If you want to sort data in an Excel chart, you have come to the right place. Here, we will walk you through 5 easy methods to do the task smoothly. ...

0
How to Create a League Table in Excel (4 Easy Methods)
0

If you want to create a league table in Excel, you have come to the right place. Here, we will walk you through 4 easy and effective methods to do the task ...

0
How to Create a Nested Formula in Excel (3 Easy Examples)
0

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

0
How to Use COUNTIF to Count Date Less Than Today in Excel
0

If you are looking for Excel countif date less than today, you have come to the right place. Here, we will walk you through 3 easy and effective methods to do ...

0
How to Create Automatic Salary Slip Generator Using Excel
0

Salary slip is a report that has a full detailed salary of the employee. Employers used an automatic salary slip generator to understand the salary record of ...

0
How to Calculate P-Value from Confidence Interval in Excel
0

If you want to calculate P-Value from Confidence Interval in Excel, you have come to the right place. Here, we will walk you through 3 easy and effective ...

0
How to Sum Visible Cells with Criteria in Excel (5 Easy Methods)
0

If you are looking for Excel sum visible cells with criteria, you have come to the right place. Here, we will walk you through 5 easy and effective methods to ...

0
How to Refresh Excel Sheet Automatically Using VBA (4 Methods)
0

If you want to refresh Excel sheet automatically VBA, you have come to the right place. Here, we will walk you through 4 easy methods to do the task smoothly. ...

0
Excel Conditional Formatting for Date Within 3 Months (3 Methods)
0

Conditional Formatting is a handy feature to highlight cells based on a specified condition. If you want to know how to use Conditional Formatting for date ...

0
Using VLOOKUP to Merge Two Sheets in Excel (with Easy Steps)
0

If you are looking for VLOOKUP to merge two Excel sheets, you have come to the right place. In many cases, we have data in two different Excel sheets and we ...

Browsing All Comments By: Afia
  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 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

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

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

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

ExcelDemy
Logo