Tanjima Hossain

Hello MS,

The link will be sent to your Email Id which you will use to fill up the form in the download section of this article.

Please provide your valid Email address in the form of the download section.

Thanks
ExcelDemy

Hello Alphonse,
Thanks for your appreciation.
You can try the code below to count number of Mondays for a month of a year

Function countmonday(ByVal mname As String, ByVal yrvalue As String) As Integer
Dim totalcount, given_mnth As Integer
Dim given_date As Date
given_date = CDate(mname & " 1, " & yrvalue)
    Select Case Weekday(given_date)

        Case vbMonday
        Case vbTuesday
            given_date = DateAdd("d", 6, given_date)
        Case vbWednesday
            given_date = DateAdd("d", 5, given_date)
        Case vbThursday
            given_date = DateAdd("d", 4, given_date)
        Case vbFriday
            given_date = DateAdd("d", 3, given_date)
        Case vbSaturday
            given_date = DateAdd("d", 2, given_date)
        Case vbSunday
            given_date = DateAdd("d", 1, given_date)
    End Select
    given_mnth = Month(given_date)
    Do
        totalcount = totalcount + 1
        given_date = DateAdd("ww", 1, given_date)
    Loop While (Month(given_date) = given_mnth)
    countmonday = totalcount
End Function

• Then, type the function name and enter the month name and year to count Mondays.
As a result, we are getting 5 which represents Mondays of January 2023.

Best Regards
ExcelDemy

Hello Ali,
Hope you are doing well. I tried to create a custom format with number/text/number for numeric values like 14002502.
• After opening up the Format Cells dialog box, type the following format in the Type area under the Custom tab.
#### "/Pound/" ####
Here, #### represents four digits before and after the text. Here, I used “/Pound/” as the text part within inverted commas.

• After pressing OK, you will get the following results.

Best Regards
ExcelDemy

Hello Michael,

Hope you are doing well. Here, I have the following dataset containing 3 columns where we have some products. After combining all of the columns into a column we will clean up all of the empty cells.

• Write down the following code in a module.

  Sub CombineColumns1()
Dim x, y As Range
Dim i As Integer
Dim LastRow As Integer
Dim cell As Range
Dim zTxt As String
On Error Resume Next
zTxt = Application.ActiveWindow.RangeSelection.Address
Set x = Application.InputBox("please select the range of texts", "Merged List", zTxt, , , , , 8)
Set y = Application.InputBox("please select the whole data range", "Merged List", zTxt, , , , , 8)
If x Is Nothing Then Exit Sub
LastRow = x.Columns(1).Rows.Count + 1
For i = 2 To x.Columns.Count
Range(x.Cells(1, i), x.Cells(x.Columns(i).Rows.Count, i)).Cut
ActiveSheet.Paste Destination:=x.Cells(LastRow, 1)
LastRow = LastRow + x.Columns(i).Rows.Count
Next
For i = y.Cells.Count To 1 Step -1
If Len(y.Cells(i)) = 0 Then
y.Cells(i).Delete xlUp
End If
Next i
End Sub  

• Press F5.
Then, you will get the following input box.
• Select the range of cells containing texts and press OK.

Later, another input box will appear.
• Select the whole data range containing all the blank cells.
• Press OK.

In this way, we combined all of the columns in the first column and deleted the rest of the cells.

Best Regards
Tanjima Hossain

Hello MICHAEL,

Thanks for your inquiry. Actually, the formula used in Method-1 has been updated to use in Method-3 for behaving differently from Method-1. In Method 1 we tried to combine two columns by adding another column after the ending of one column. But in Method-3 it was the intent to join 2 columns by adding the cell contents row-wise. If you need to add the columns serially then please follow the previous 2 methods.

Thank you
Tanjima Hossain

Hello JORDAN,

Hope you are doing well! As per your requirement, I am considering the following scenario where in a table I have two lists of products with which I will compare the products in the Order List column. I will use a formula that will match a product from the Order List column with products from the Product List 1 column, for matches, the name of the product will return. Otherwise, the formula will search for matches in the Product List 2 column and will return the product name if any matches are found. Otherwise, we will get “No Match”.

• Enter the following formula in cell F4.
=IF (COUNTIF ($B$4: $B$10, E4)>0, E4, IF (COUNTIF ($C$4: $C$10, E4)>0, E4, "No Match"))
• Drag down the Fill Handle tool.

Finally, we are having Green Apple and Kiwi as they appear in the Product List 2 column, and Banana as it appears in the Product List 1 column.

Best Regards,
Tanjima Hossain
ExcelDemy

Hello SEAN,
Here, we have listed the following tasks in a sheet which we will classify according to their importance and urgency.

• For creating drop-down lists for each of the cells in range C5:D12, we have opened the Data Validation dialog box.
• In the Source box, type High, Medium, Low.

Then, we selected the following values for the tasks in the Task sheet.

In another sheet named Matrix, we have created the following template.

• For the portion of High Important and High Urgent use the following formula in cell D5, press ENTER, and use the AutoFill feature up to cell D8.
=IFERROR(INDEX(Task!$B$5:$B$12,SMALL(IF((LEN(Task!$B$5:$B$12)<>0)*(Task!$C$5:$C$12="High")*(Task!$D$5:$D$12="High"),ROW(Task!$B$5:$B$12)-ROW(Task!$B$4),""),ROW(Task!B5)-ROW(Task!$B$4))),"")

• For the portion of High Important and Medium Urgent use the following formula in cell F5, press ENTER, and use the AutoFill feature up to cell F8.
=IFERROR(INDEX(Task!$B$5:$B$12,SMALL(IF((LEN(Task!$B$5:$B$12)<>0)*(Task!$C$5:$C$12="High")*(Task!$D$5:$D$12="Medium"),ROW(Task!$B$5:$B$12)-ROW(Task!$B$4),""),ROW(Task!B5)-ROW(Task!$B$4))),"")

• For the portion of High Important and Low Urgent use the following formula in cell H5, press ENTER, and use the AutoFill feature up to cell H8.
=IFERROR(INDEX(Task!$B$5:$B$12,SMALL(IF((LEN(Task!$B$5:$B$12)<>0)*(Task!$C$5:$C$12="High")*(Task!$D$5:$D$12="Low"),ROW(Task!$B$5:$B$12)-ROW(Task!$B$4),""),ROW(Task!B5)-ROW(Task!$B$4))),"")

• For the portion of Medium Important and High Urgent use the following formula in cell D9, press ENTER, and use the AutoFill feature up to cell D12.
=IFERROR(INDEX(Task!$B$5:$B$12,SMALL(IF((LEN(Task!$B$5:$B$12)<>0)*(Task!$C$5:$C$12="Medium")*(Task!$D$5:$D$12="High"),ROW(Task!$B$5:$B$12)-ROW(Task!$B$4),""),ROW(Task!B5)-ROW(Task!$B$4))),"")

• For the portion of Medium Important and Medium Urgent use the following formula in cell F9, press ENTER, and use the AutoFill feature up to cell F12.
=IFERROR(INDEX(Task!$B$5:$B$12,SMALL(IF((LEN(Task!$B$5:$B$12)<>0)*(Task!$C$5:$C$12="Medium")*(Task!$D$5:$D$12="Medium"),ROW(Task!$B$5:$B$12)-ROW(Task!$B$4),""),ROW(Task!B5)-ROW(Task!$B$4))),"")

• For the portion of Medium Important and Low Urgent use the following formula in cell H9, press ENTER, and use the AutoFill feature up to cell H12.
=IFERROR(INDEX(Task!$B$5:$B$12,SMALL(IF((LEN(Task!$B$5:$B$12)<>0)*(Task!$C$5:$C$12="Medium")*(Task!$D$5:$D$12="Low"),ROW(Task!$B$5:$B$12)-ROW(Task!$B$4),""),ROW(Task!B5)-ROW(Task!$B$4))),"")

• For the portion of Low Important and High Urgent use the following formula in cell D13, press ENTER, and use the AutoFill feature up to cell D16.
=IFERROR(INDEX(Task!$B$5:$B$12,SMALL(IF((LEN(Task!$B$5:$B$12)<>0)*(Task!$C$5:$C$12="Low")*(Task!$D$5:$D$12="High"),ROW(Task!$B$5:$B$12)-ROW(Task!$B$4),""),ROW(Task!B5)-ROW(Task!$B$4))),"")

• For the portion of Low Important and Medium Urgent use the following formula in cell F13, press ENTER, and use the AutoFill feature up to cell F16.
=IFERROR(INDEX(Task!$B$5:$B$12,SMALL(IF((LEN(Task!$B$5:$B$12)<>0)*(Task!$C$5:$C$12="Low")*(Task!$D$5:$D$12="Medium"),ROW(Task!$B$5:$B$12)-ROW(Task!$B$4),""),ROW(Task!B5)-ROW(Task!$B$4))),"")

• For the portion of Low Important and Low Urgent use the following formula in cell H13, press ENTER, and use the AutoFill feature up to cell H16.
=IFERROR(INDEX(Task!$B$5:$B$12,SMALL(IF((LEN(Task!$B$5:$B$12)<>0)*(Task!$C$5:$C$12="Low")*(Task!$D$5:$D$12="Low"),ROW(Task!$B$5:$B$12)-ROW(Task!$B$4),""),ROW(Task!B5)-ROW(Task!$B$4))),"")

Note:
Here, we have used the sheet name Task in all our formulas, if you have any other sheet name, then put this name in the formulas.

Regards
Tanjima Hossain

Hello MAREK,
Hope you are doing well. As far as I understand, you wanted to change the position of the background images or hide any row with these images. I think we can do these works as I demonstrated below.
Firstly, in Section 1, you can move the background image by clicking on the cell and then dragging it to your desired position.

• Here, we have dragged the image beside the cell and changed the text written also in this cell.

• In Section 3, you can move the image along with the text to any position by only clicking on this cell and then dragging it.

In this way, we have changed the position.

• Later, we also changed the text.

• If you want to hide any row, then just click on this row, and then Right-click.
• Select the Hide option.

Eventually, we have hidden our desired row.

Hello ANTHONY,
If your second to the last row is situated in Row 11, then you can use the following formula.
=INDEX(‘[Sales.xlsx]Dataset’!$A$11:$H$11,COUNTA(‘[Sales.xlsx]Dataset’!$A$11:$H$11))
You have to just change the reference according to the position of your desired row.

Best Regards
Tanjima Hossain

Hello DARREN,
Hope you are doing well. So, to solve your issue you can follow the stated procedures below.
Here, we have the following three sheets- April, May, June, etc. Using a VBA code, we will print all these sheets into PDF format separately.

• Type the following code in your Visual Basic Editor window.

 Sub SaveAsPDF()
Dim filename As String
Dim ws As Worksheet
Dim mywsname As String
For Each ws In Worksheets
ws.Select
mywsname = ws.Name
filename = "D:\Exceldemy\" & mywsname
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:=filename, _
Quality:=xlQualityStandard, IncludeDocProperties _
:=True, IgnorePrintAreas:=False, openafterpublish:=True
Next ws
End Sub 

Finally, you will get the PDF files in your designated folder.

Also, the PDF files will be opened automatically.

I hope these steps will give your desired results.

Thank you
Tanjima Hossain

Hi RICK,
The maximum number of sheets in a workbook is 255. So, if you have values in rows on basis of which you will split your sheet for more than 255 rows, then you may face a problem.

Hello DOUG,
Hope you are doing well. You can follow the stated technique below to keep the pivot tables intact in a worksheet.
Here, in a range I have used a formula to add up the sales values, besides it, I have a pivot table that I don’t want to change.

• Type the following code.
Sub Remove_Formulas_from_the_Whole_Worksheet()
Sheet_Name = InputBox(“Enter the Name of the Worksheet to Remove Formulas: “)
Dim R As Range
Set R = Application.InputBox(Title:=”Number Format Rule From Cell”, _
Prompt:=”Select the range”, Type:=8)
Worksheets(Sheet_Name).Activate
R.Copy
R.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Application.CutCopyMode = False
End Sub

After running the code, an input box will appear.
• Type the name of the sheet on which you are working (here it is Sheet1) and press OK.

Then, another input box will open.
• Select the range which you want to change and press OK.

After that, the formula from our selected range will be removed.

Thanking you
Tanjima Hossain

Hi SANSHI,
You can use the direct method to calculate the PPF interest easily.
For calculating the PPF interest, we will be using the following dataset. Here, we have the total Balance, Deposits from April to March, and an Interest Rate of 5%.

• For the monthly interest rates use the following formula
=IF(C4=”Before 5th”,($A$2+B4)*(D4/12),($A$2)*(D4/12))

• For the first month of getting the balances, apply the following formula in cell F4.
=$A$2+B4

• To get the rest of the balances type the following formula
=$A$2+SUM($B$4:B5)
• Drag down the Fill Handle tool.

The final output will look like the following figure.

Hi Jeff Blackwell,
Thanks for reaching us. You can use the following code with some adjustments to split a worksheet based on column into some worksheets of the same workbook.
Here, we will split the following worksheet based on the Month column and so the worksheet will be split into 3 different sheets – January, February, March.

• Use the following code. Adjust the starting row number in nRow = 2, the column indicating letter in (the base column on which you will split the worksheet) objWorksheet.Range(“C” & nRow).

Sub SplitSheetIntoMultipleWorkbooksBasedOnColumn()
    Dim objWorksheet As Excel.Worksheet
    Dim nLastRow, nRow, nNextRow As Integer
    Dim strColumnValue As String
    Dim objDictionary As Object
    Dim varColumnValues As Variant
    Dim varColumnValue As Variant
    Dim xWS As Excel.Worksheet
    Set objWorksheet = ActiveSheet
    nLastRow = objWorksheet.Range("A" & objWorksheet.Rows.Count).End(xlUp).Row
    Set objDictionary = CreateObject("Scripting.Dictionary")
    For nRow = 2 To nLastRow
        strColumnValue = objWorksheet.Range("C" & nRow).Value
        If objDictionary.Exists(strColumnValue) = False Then
           objDictionary.Add strColumnValue, 1
        End If
    Next
    varColumnValues = objDictionary.Keys
    For i = LBound(varColumnValues) To UBound(varColumnValues)
        varColumnValue = varColumnValues(i)
       Set xWS = Sheets.Add(after:=Worksheets(Worksheets.Count))
        objWorksheet.Rows(1).EntireRow.Copy
        xWS.Name = varColumnValue
        xWS.Activate
        xWS.Range("A1").Select
        xWS.Paste
        For nRow = 2 To nLastRow
            If CStr(objWorksheet.Range("C" & nRow).Value) = CStr(varColumnValue) Then
               objWorksheet.Rows(nRow).EntireRow.Copy
               nNextRow = xWS.Range("A" & objWorksheet.Rows.Count).End(xlUp).Row + 1
               xWS.Range("A" & nNextRow).Select
               xWS.Paste
               xWS.Columns("A:D").AutoFit
            End If
        Next
    Next
End Sub

• Press F5.
Then, you will have 3 sheets- January, February, March.

Hi Laurene,
Thanks for staying with us. If the net income cash flows reduced, or 0 or negative, whatever it is. The value of the argument finance rate doesn’t depend on it. You must give the rate as input which is paid by you for cash flows. When you select the payment and incomes at specified intervals as the Values argument, the finance rate as the rate paid by you for your income, and finally the reinvestment rate, the MIRR function will calculate the rate by automatically adjusting the values.

Hello Jorge.F,
Thank you so much for your suggestion. I think you might want the following code incorporating your suggested lines. Moreover, I tried to select the range through an input box which may help you select the range.

Sub Insert_Blank_Rows()
Dim rng As Range
Dim CountRow As Integer
Dim i As Integer
Dim n As Integer
Application.CutCopyMode = False
ExcelTitleId = "Exceldemy"
Set rng = Application.Selection
Set rng = Application.InputBox("Select your range", ExcelTitleId, rng.Address, Type:=8)
n = Int(InputBox("Enter the Value of n: "))
k = Int(InputBox("Enter the Number of Blank Rows: "))
CountRow = rng.EntireRow.Count
Selection(1).Activate
For i = 1 To Int(CountRow / n)
    For j = 0 To k - 1
        ActiveCell.Offset(n + j, 0).EntireRow.Insert
    Next j
    ActiveCell.Offset(n + k, 0).Select
Next i

End Sub

Hi Milad,
Thanks for your question.
According to your requirement, I have created a random dataset containing 1M records within 1M rows of a dataset in Excel. Using a VBA code, I will split it into 10 different worksheets each containing 100,000 rows.

• Type the following VBA code. Here, instead of using the InputBox method, we are directly declaring the total range and the split number in the code which will expediate running the code.

Sub SplitExcelSheet_into_MultipleSheets()
Dim WorkRng As Range
Dim xRow As Range
Dim SplitRow As Integer
Dim xWs As Worksheet
On Error Resume Next
Set xWs = Range("A1:A1000000").Parent
Set xRow = Range("A1:A1000000").Rows(1)
Application.ScreenUpdating = False
For i = 1 To 1000000 Step 100000
    resizeCount = 100000
    If (Range("A1:A1000000").Rows.Count - xRow.Row + 1) < 100000 Then resizeCount = Range("A1:A1000000").Rows.Count - xRow.Row + 1
    xRow.Resize(resizeCount).Copy
    Application.Worksheets.Add after:=Application.Worksheets(Application.Worksheets.Count)
    Application.ActiveSheet.Range("A1").PasteSpecial
    Set xRow = xRow.Offset(100000)
Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

• Press F5.
In this way, we have created 10 different sheets each with 100000 records.

Hi Tushar Chauhan,
kindly let me know which code is causing this problem.

Hi Kala,
Thanks for your question. According to your comment, you want to work with a table that’s why I have created the following table.

You can use the following formula
=IF(ROW()=””,””,TODAY())
Here, ROW() will return the corresponding row number for a row; like for Row 5, it will give the value 5, for Row 6 you will have 6.

Then, you can insert the names in the Name column.

Hi Andy,
Thanks for your query. Unfortunately, using the UNIQUE function you cannot do your desired job directly. So, I have come up with an easy alternative way.
Here, I have created the following dataset using your example. Using the PivotTable feature of Excel, I will convert the following three columns into a single column with unique values only.

• Press ALT+D and then P immediately to open up the PivotTable and PivotChart Wizard.

• In Step 1 of this wizard click on the options; Multiple consolidation ranges, PivotTable.
• Click on Next.

• In Step 2a of this wizard click on the Create a single page field for me option.
• Click on Next.

• Now, select the range of the words including a blank column prior to this range in the Range box.
• Select Add to enter the formula of the Range box to the All ranges box.

Afterward, the formula will be entered into the All ranges box, and finally, click on Next.

• In Step 3 of this wizard click on the New worksheet option.
• Click on Finish.

• Now, drag down the Value to the Rows area.
Finally, all of the unique words will be listed in a single column.

Hello Dan,
Thank you so much for your appreciation. Hope you will be benefitted more by staying with our Exceldemy site.

Hello Chandrakant, regarding your problem I have created the following two datasets where in both sheets I have your predefined text 762-V-231. After comparing the range of texts from Sheet1 with Sheet2 I will have the matched texts besides the Existing column.
In Sheet1 I have a range of texts and after the comparison, I will have matched texts in the Similar Text column.

The comparison will be done with Sheet2

To do this comparison you can use the following code

Sub find_text()
Dim source_txt As Range, find_txt As Range
For Each source_txt In Sheets(“Sheet1”).Range(“A2:A6”)
For Each find_txt In Sheets(“Sheet2”).Range(“A2:A6”)
If InStr(1, source_txt, find_txt, vbTextCompare) > 0 Then
source_txt.Offset(0, 1) = find_txt
Exit For
End If
Next
Next
Set source_txt = Nothing
Set find_txt = Nothing
End Sub

After pressing F5, you will have the following result

Hello Gabrielle, after going through your problem, I have understood it like this that you want to highlight those dates which are greater than 30 days (or January month) or 60 days (or February month), or 90 days (or March month) and the corresponding specific cells are blank for these dates. If the specific cells are not blank, then the coloring should be avoided. After understanding this scenario, I have created the following dataset like your sample to illustrate the process.

• Firstly, select the dates, and then go to the Home tab >> Conditional Formatting dropdown >> New Rule option.

• In the opening dialog box, choose the indicated option and then type the following formula in the box
=AND(B2=””,OR(A2>DATEVALUE(“1/30/2022”),A2>DATEVALUE(“2/28/2022”),A2>DATEVALUE(“3/31/2022”)))
• Click on Format

• In the Format Cells dialog box go to the Fill tab >> select your desired color >> press OK.

Then, the following result will appear.

Hello Muizz Shaikh,
Thanks for your question. The maximum number of entries should be within the limit of the maximum row numbers of Excel which is 1,048,576. So, you can merge the files as long as the entries of the combined file support this limit.