Mursalin Ibne Salehin

Hi JUN,

Thanks for your comment. I assume you are having this problem because Excel considers 1 (‘dataset’!=”1″) as a number. You don’t need to add the double quotation symbol for that part. So, an error occurs and it is showing “–” instead of “A“. You can use the formula below to get “A“:
=IFERROR((INDEX(datasheet!C:C,MATCH(1,(datasheet!A:A=1)*(datasheet!B:B="a")*(datasheet!D:D="22/8/1"),0))),"-")
To get “B“, use the formula below:
=IFERROR((INDEX(datasheet!C:C,MATCH(1,(datasheet!A:A=2)*(datasheet!B:B="b")*(datasheet!D:D="22/8/1"),0))),"-")
And to get “C“, you can use the formula below:
=IFERROR((INDEX(datasheet!C:C,MATCH(1,(datasheet!A:A=3)*(datasheet!B:B="c")*(datasheet!D:D="22/8/1"),0))),"-")
For your convenience, I have attached the excel file below with the formulas that I have used. I have used the formulas in Excel 365.
Download Excel File
I hope this will help you to solve your problem. If you still find any problem in your excel file, then you can send it to [email protected]. I will take a look and email you the solution.
Thanks!

Hi HONG,

Thanks for your comment. You can use the VBA code given below for the desired output.

1. Copy the VBA code and paste it into the Module window.

Function ConvertNumberToWords(ByVal MyNumber) As String
    Dim Units As String
    Dim SubUnits As String
    Dim DecimalPlace As Integer
    Dim Count As Integer
    Dim DecimalSeparator As String
    Dim UnitName As String
    Dim SubUnitName As String
    Dim SubUnitNameAlt As String

    ' Change these values according to your requirements
    DecimalSeparator = "."
    UnitName = "Ringgit Malaysia"
    SubUnitName = "Sen"
    SubUnitNameAlt = "Ringgit"

    ReDim Place(9) As String
    Place(2) = " Thousand "
    Place(3) = " Million "
    Place(4) = " Billion "
    Place(5) = " Trillion "

    ' Convert MyNumber to String
    MyNumber = Trim(CStr(MyNumber))

    ' If MyNumber is blank, return zero
    If MyNumber = "" Then
        ConvertNumberToWords = "Zero"
        Exit Function
    End If

    ' Find position of decimal place, 0 if none.
    DecimalPlace = InStr(MyNumber, DecimalSeparator)

    ' Convert SubUnits and set MyNumber to Units amount.
    If DecimalPlace > 0 Then
        SubUnits = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2))
        MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
    End If

    Count = 1
    Do While MyNumber <> ""
        TempStr = GetHundreds(Right(MyNumber, 3))
        If TempStr <> "" Then
            If Units <> "" Then
                If Count = 1 And Len(MyNumber) > 3 Then
                    Units = TempStr & " and" & Units
                Else
                    Units = TempStr & Place(Count) & Units
                End If
            Else
                Units = TempStr & Place(Count)
            End If
        End If
        If Len(MyNumber) > 3 Then
            MyNumber = Left(MyNumber, Len(MyNumber) - 3)
        Else
            MyNumber = ""
        End If
        Count = Count + 1
    Loop

    Dim Result As String
    Result = Trim(Units & " " & IIf(Count > 2, SubUnitNameAlt, IIf(Count = 2, SubUnitName, UnitName)))

    If SubUnits <> "" Then
        Result = Result & IIf(Count > 1, " and", "") & " " & SubUnits & " " & SubUnitName
    End If

    ConvertNumberToWords = Result
End Function

Function GetHundreds(ByVal MyNumber) As String
    Dim Result As String
    If Val(MyNumber) = 0 Then Exit Function
    MyNumber = Right("000" & MyNumber, 3)

    ' Convert the hundreds place.
    If Mid(MyNumber, 1, 1) <> "0" Then
        Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred"
    End If

    ' Convert the tens and ones place.
    If Mid(MyNumber, 2, 1) <> "0" Then
        Result = Result & " " & GetTens(Mid(MyNumber, 2))
    Else
        Result = Result & " " & GetDigit(Mid(MyNumber, 3))
    End If
    GetHundreds = Result
End Function

Function GetTens(TensText) As String
    Dim Result As String
    Result = ""           ' Null out the temporary function value.
    If Val(Left(TensText, 1)) = 1 Then   ' If value between 10-19
        Select Case Val(TensText)
            Case 10: Result = "Ten"
            Case 11: Result = "Eleven"
            Case 12: Result = "Twelve"
            Case 13: Result = "Thirteen"
            Case 14: Result = "Fourteen"
            Case 15: Result = "Fifteen"
            Case 16: Result = "Sixteen"
            Case 17: Result = "Seventeen"
            Case 18: Result = "Eighteen"
            Case 19: Result = "Nineteen"
            Case Else
        End Select
    Else                                 ' If value between 20-99
        Select Case Val(Left(TensText, 1))
            Case 2: Result = "Twenty"
            Case 3: Result = "Thirty"
            Case 4: Result = "Forty"
            Case 5: Result = "Fifty"
            Case 6: Result = "Sixty"
            Case 7: Result = "Seventy"
            Case 8: Result = "Eighty"
            Case 9: Result = "Ninety"
            Case Else
        End Select
        Result = Result & " " & GetDigit(Right(TensText, 1))   ' Retrieve ones place.
    End If
    GetTens = Result
End Function

Function GetDigit(Digit) As String
    Select Case Val(Digit)
        Case 1: GetDigit = "One"
        Case 2: GetDigit = "Two"
        Case 3: GetDigit = "Three"
        Case 4: GetDigit = "Four"
        Case 5: GetDigit = "Five"
        Case 6: GetDigit = "Six"
        Case 7: GetDigit = "Seven"
        Case 8: GetDigit = "Eight"
        Case 9: GetDigit = "Nine"
        Case Else: GetDigit = ""
    End Select
End Function

2. Press Ctrl+S to save the code.
3. Now, insert the formula in Cell C3 and press Enter.
=ConvertNumberToWords(B3)

I hope this code will solve your problem. If you have any other queries, please let me know in the comment section.

Regards
Mursalin,
ExcelDemy.

Dear TAREKZHRAN,
Thank you for your comment. Making a listbox of 14 columns and creating a search box will be complex. However, you can do that easily by modifying the code we provided with ComboBox.

To make a list of 14 columns change the value of col_no variable in the code we have given. Here we set 2 to 4 as col_no. Suppose your column number starts from Column B to Column O, in this case, set col_no as 2 to 15.

In the ComboBox1_Change named subprocedure change the col_header array and col_no variable like below.

col_headers = Array("B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N","O")
For col_no = 2 To 15

Similarly, in UserForm_Initialize named sub procedure set col_no as 2 to 15 and ColumnCount to 14.

To search the alphabet from left or middle or anywhere, we will replace the Left function with the InStr function. The Left function is used to search only for the first alphabet whereas Instr will help you to find it from anywhere in the text. The modified line is below.

If InStr(1, UCase(ActiveSheet.Cells(row_no, field).Value), UCase(Me.TextBox1.Text)) > 0

After modifying the code for TextBox1_Change named sub procedure will be like below.

Private Sub TextBox1_Change()
On Error Resume Next
If Me.TextBox1.Text = "" Then
Me.ListBox1.Clear
Exit Sub
End If
Me.ListBox1.Clear
Dim row_no As Integer
Dim last_row_no As Integer
last_row_no = ActiveSheet.Range("B100").End(xlUp).Row
For row_no = 5 To last_row_no
	letter = Len(Me.TextBox1.Text)
	If InStr(1, UCase(ActiveSheet.Cells(row_no, field).Value), UCase(Me.TextBox1.Text)) > 0 Then
	With Me.ListBox1
	.AddItem ActiveSheet.Cells(row_no, "B").Value
	.List(.ListCount - 1, 1) = ActiveSheet.Cells(row_no, "C").Value
	.List(.ListCount - 1, 2) = ActiveSheet.Cells(row_no, "D").Value
	End With
	End If
Next
End Sub

After applying the code, you will get results like the picture below.

The Excel file of the solution is attached below. You can download and use it.
Answer.xlsm
Hope this will help you to solve your problem. Please let us know if you have other queries.
Regards,
Mursalin
ExcelDemy.

Hi RINA,
Thanks for your comment. Per your comment, we have tried implementing the conditions in our dataset. You can see the result below:

Here, we have used the formula below in Cell I2:
=IFS(H2=15,CONCAT(HOUR(F2),".25"),H2=30,CONCAT(HOUR(F2),".5"),H2=45,CONCAT(HOUR(F2),".75"))
According to one of your conditions (in case of Kate), you wanted 6:23 to be displayed as 6.25. But if you want to display time to the nearest quarter on the clock, it gives 6.5. You can check out the Excel file from the link below:
Answer.xlsx
It will be easier for us to give a proper solution if you send us an Excel file with the desired outputs. You can send the Excel file to my email: [email protected]. Please let us know if you have any other queries.

Regards
Mursalin,
Exceldemy.

Dear SARAH,
Thanks for your comment. The Variance Inflation Factor (VIF) measures the degree of multicollinearity in a set of explanatory variables in a regression model. VIF values greater than 1 indicate that the explanatory variables are correlated to some degree. However, a VIF value of infinity is not an indication of perfect correlation.

When the R-squared value is equal to 1, it indicates that the regression model perfectly fits the data. But, setting the R-squared value to 1 does not make sense in practice as it implies that the model explains all the variation in the data, which is usually not the case.

If the R-squared value is set to 1, the VIF becomes undefined rather than infinity. This is because the formula for VIF involves dividing by the difference between 1 and the R-squared value, and division by zero is undefined.

So we can say, a large VIF value indicates some degree of correlation between the explanatory variables, but an infinite VIF does not necessarily indicate perfect correlation. Setting the R-squared value to 1 makes the VIF undefined rather than infinity.

I hope this answer will help to interpret a VIF where the R-square is 1. Please let us know if you have any other queries.

Regards,
Mursalin,
Exceldemy.

Hi MYLES,

Thanks for your comment. We are very glad to know that we could be of help to you. Let us know if you have any queries.

Good luck.

Hi STEPHEN,

Thanks for reaching out to us. I am replying to you on behalf of Exceldemy. We will be happy to help you.
Please send the Excel file to [email protected]. Also, mention what you want to update in the Excel file. We will try to give the solution as early as possible.

Thanks!

Hi KATIA,

Thanks for your comment. I am replying to you on behalf of Exceldemy. To find the average, you can use the AVERAGE function. For Method 1, you can follow the steps below.
STEPS:
1. Firstly, select Cell F5 and type the formula below:
=AVERAGE(C4:INDEX(C4:C12,E4))

2. Press Enter to see the result.
3. Secondly, select the range B3:C12.

4. Press Ctrl + T to convert the range into a table.
5. A message box will appear.
6. Click OK to proceed.
7. As a result, you will see a table like the picture below.

8. Now, if you add Oct-15 in Cell B13, then the table and formula of Cell F5 will automatically update.

For Method 2, you can follow the above steps and use the formula below:
=AVERAGE(OFFSET(B4,0,0,1,-MONTH(B3)):B4)
For Method 3, use the formula below and convert the range into a table:
=AVERAGEIFS(C4:C17, B4:B17, ">="&B4, B4:B17, "<="&F5)
You can also find the formulas in the workbook below:
Workbook with AVERAGE Formulas.xlsx
I hope this will help you to solve your problems. Please let us know if you have other queries.
Thanks!

Hi JESSICA,

Thanks for your comment. I am replying to you on behalf of Exceldemy. You can check the comment thread for the answer. Also, to know more about copying formulas across cells, you can check the link below.
Copy Formulas Across Cells
Thanks!

Hi JOJO,

Thanks for your comment. I am replying to you on behalf of Exceldemy. You can use VBA to remove texts that have strikethrough in them. Suppose, you have some text strings like the picture below:

Let’s follow the steps below to remove the texts with a strikethrough.
STEPS:
1. Firstly, go to the Developer tab and click on the Visual Basic option.

2. Secondly, select Insert >> Module to open the Module window.

3. Now, copy the code below and paste it into the Module window:
Sub Remove_Strike_through_Texts()
Dim iRng As Range, iCell As Range
Dim iStr As String
Dim X As Long
On Error Resume Next
Set iRng = Application.InputBox("Please select range:", "Microsoft Excel", _
Selection.Address, , , , , 8)
If iRng Is Nothing Then Exit Sub
Application.ScreenUpdating = Fase
For Each iCell In iRng
If IsNumeric(iCell.Value) And iCell.Font.Strikethrough Then
iCell.Value = ""
ElseIf Not IsNumeric(iCell.Value) Then
For X = 1 To Len(iCell)
With iCell.Characters(X, 1)
If Not .Font.Strikethrough Then
iStr = iStr & .Text
End If
End With
Next
iCell.Value = iStr
iStr = ""
End If
Next
Application.ScreenUpdating = True
End Sub

4. Press Ctrl + S to save the code.
5. After that, go to the Developer tab and select Macros.

6. In the Macro window, select the desired code and Run it.

7. A message box will appear.
8. Select the range from where you want to remove the strikethrough texts.
9. Finally, click OK to proceed.

10. As a result, the texts with strikethrough will be removed.

I hope this will help you to solve your problem. Please let us know if you have other queries.
Thanks!

Hi DAWID,

Thanks for your comment. Sorry to hear that you are not getting the desired output. I have tried to convert 192697 and 194898 to barcodes using the same code. In my case, it worked. You can see the result below:

I am also attaching the Excel file below:
Answer.xlsm
Moreover, the VBA code is not error-free. It can introduce errors sometimes. You can copy the code and paste it into a new workbook. It may help you to get the desired results.
I hope this will help you to solve your problem. Please let us know if you have other queries.
Thanks!

Hi MIKE M,

Sorry for the late reply. I am replying to you on behalf of Exceldemy. You can mail your problem to [email protected]. Our team will take a look and try to give a solution to your problem.

Thanks!

Hi PETE,

Thanks for your comment. I am replying on behalf of Exceldemy. To paste the values, you can try the code below:

Sub Copy_Data_from_Another_Workbook()
Dim wb As Workbook
Dim newwb As Workbook
Dim rn1 As Range
Dim rn2 As Range
Set wb = Application.ActiveWorkbook
With Application.FileDialog(msoFileDialogOpen)
.Filters.Clear
.Filters.Add "Excel 2007-13", "*.xlsx; *.xlsm; *.xlsa"
.AllowMultiSelect = False
.Show
If .SelectedItems.Count > 0 Then
Application.Workbooks.Open .SelectedItems(1)
Set newwb = Application.ActiveWorkbook
Set rn1 = Application.InputBox(prompt:="Select Data Range", Default:="A1", Type:=8)
wb.Activate
Set rn2 = Application.InputBox(prompt:="Select Destination Range", Default:="A1", Type:=8)
rn1.Copy
rn2.PasteSpecial xlPasteValues
newwb.Close False
End If
End With
End Sub

Here, we have used the code of Method 1. We changed the highlighted lines to copy and paste only the values.
I hope this will help you solve your problem. Please let us know if you have any other queries.
Thanks!

Hi A V S S PRASAD,

Sorry for the late reply. I am replying to you on behalf of Exceldemy. To match the addresses, you need to use the INDEX-MATCH functions. You will find similar formulas in the articles below.
https://www.exceldemy.com/index-match-multiple-criteria-partial-text/
https://www.exceldemy.com/index-match-partial-match/
I hope this will help you to solve your problem.
Thanks!

Hi BRIAN WINKLE,

Sorry for the late reply. I am replying to you on behalf of Exceldemy. To create a timestamp like the given example, you can take a look at the article below.
https://www.exceldemy.com/create-a-timesheet-in-excel/
I hope this will help you to solve your problem.
Thanks!

Hi JOE FRAZIER,

Thanks for your comment. I am replying to you on behalf of Exceldemy. In this article, Cells I16:I22 of Column I counts the overtime for each day of a week. That means if you want to see the overtime for Monday, you need to check Cell I16. So, you can use the same formula to calculate overtime for a single day.
I hope this will help you to solve your problem. Please let us know if you have other queries.
Thanks!

Hi DEE ZELAYA,

Sorry for the late reply. I am replying to you on behalf of Exceldemy. Here, Cell D12 is renamed as work_hours_per_week. You can use the Name Manager in the Formulas tab to define the name. To check the defined names, follow the steps below.
Firstly, download the practice book, go to the Formulas tab and select Name Manager.

In the Name Manager box, you will find all the defined names.

To apply the same formula in your new spreadsheet, you need to define the names using the Name Manager. To do that, you can follow the link below.
https://www.exceldemy.com/excel-edit-named-range/
Thanks!

Dear JESSE BATES,
Thanks for your comment. Declaring variable types is not mandatory in VBA. VBA by default assigns the necessary data type to any variable, you just need to put the values. In the given code, the timer variable contains the data type Double.
Please let us know if you have any other queries.
Thanks!

Dear MICHAEL,
Thanks for your comment. You can insert a Tab using “~009” inside the string. For example, XXXX.1TAB123456 can be written as XXXX.1~009123456. But unfortunately, it will not show two separate fields in one barcode. From my understanding, you need to use other barcode fonts for that purpose.
Otherwise, you can look at the solution below. But this solution will not provide you with one barcode. Here, we tried to concatenate two separate barcodes in one cell. Here, we will use the dataset below.

Let’s follow the steps below for the solution.
● Firstly, change the fields into barcode strings.
● To do so, select Cell D5 and type the formula below:
=Code128(B5)

● Also, do the same for the second field.

● After that, select Cell B7 and type the formula below:
=CONCAT(D5,CHAR(9),E5)

● Hit Enter to see the result.

● Now, select Cell B7 again and change the font theme to Code 128.
● Also, adjust the font size.

● Finally, you will get results like the picture below.

I hope this solution will help benefit you to some extent. Please let us know if you face any other issues.
Thanks!

Hi MKM,
Thanks for your comment. To answer your question, we can use the dataset below. It contains 3 rows of sales for Germany. The first sale happened in March and the other two sales happened in May. From this dataset, we can easily calculate the average sales for May in Germany.

To calculate the average sales for May in Germany, select Cell F5 and type the formula below:
=AVERAGEIFS(D5:D15, B5:B15,”<=5/31/2022", B5:B15,">=5/01/2022″,C5:C15,”Germany”)
Press Enter to see the result.

I hope this will help you to solve your problem. Please let us know your queries if you face any issues.
Thanks.

Dear CHRIS,
Thanks for your comment. Excel omits the leading zero by default. So, it’s not possible to get the desired barcode.
• To solve this, you must add an apostrophe (‘) in front of the leading zero.
• So, select Cell C5 in the VBA sheet of the workbook.
• Type ‘02628107336750 in Cell C5.
• Press Enter.
• You will get your results in Cell D5.
I hope this will help you to solve your problem.
Thanks.

Hello, Dom!
Thanks for your feedback. If you are facing any problems generating code 128 barcode font in Excel, then you can ask here. We will try to reply to you as soon as possible.
Thanks!

Hi BIPLAB,
Thanks for your query. You can build a formula with the nested CONCAT function. In that case, you don’t need any helper column. For the dataset we have discussed in the article, the formula will be:
=CONCAT(CONCAT(B5,” “,C5,” “,D5),”,”, CONCAT(B6,” “,C6,” “,D6),”,”,CONCAT(B7,” “,C7,” “,D7),”,”,CONCAT(B8,” “,C8,” “,D8))
You can also use the VBA code below. To apply this, you need to select the rows of the first column of the dataset and then run the code from the Macro window.

Sub Merge_Rows_with_Comma()

Dim iSelection As Range
Dim iRow As Range
Dim iCell As Range
Dim iStr As String

Application.ScreenUpdating = False
On Error Resume Next
Set iSelection = Intersect(Selection, ActiveSheet.UsedRange)

For Each iRow In iSelection.Rows
For Each iCell In iRow.Cells
iStr = iStr & ” ” & VBA.Trim$(iCell)
Next iCell
iRow.ClearContents
iRow.Cells(1, 1).NumberFormat = “@”
iRow.Cells(1, 1) = Mid(iStr, 2)
iStr = vbNullString
Next iRow

For Each iCell In iSelection
iStr = iStr & “,” & VBA.Trim$(iCell)
Next

With ActiveWindow
.Selection.ClearContents
.Selection(1, 1).NumberFormat = “@”
.Selection(1, 1).Value = Mid(iStr, 2)
End With
End Sub