User Posts: Naimul Hasan Arif
0
How to Find Combinations Without Repetition in Excel
0

Combinations in mathematics is a concept that is quite important in various areas of mathematics, particularly in probability and statistics. It is a very ...

0
How to Use Web Scraping with Excel VBA (3 Suitable Examples)
0

Web Scraping is a process of extracting data from websites and converting it into a format that can be easily accessed and analyzed. Microsoft Excel is a ...

0
How to Superscript Text in Excel Graph (3 Quick Ways)
0

In terms of working with Microsoft Excel, you may occasionally need to use superscripts. Superscripts are small numbers or letters that appear slightly above ...

0
How to Create Vehicle Depreciation Calculator in Excel
0

The reduction in the value of an asset over multiple numbers of years is called Depreciation. A business organization purchases assets for its own purpose, The ...

0
Conditional Formatting with Data Bars Based on Another Cell in Excel
0

In Microsoft Excel, there is a feature named Conditional Formatting that allows you to apply formatting to cells based on certain criteria. We can use ...

0
How to Make One to Many Relationship in Excel (2 Simple Ways)
0

In terms of working with Microsoft Excel, we can create a co-relation between various data. We can merge them or even create duplicate data with them. There ...

0
How to Translate Excel File from German to English
0

In terms of working with Microsoft Excel, we might have to deal with a file written in other languages rather than English. There is nothing to worry. We can ...

0
[Fixed!] Excel Hyperlink Is Not Redirecting Properly
0

Sometimes, we use different hyperlinks to express our thoughts in short or to add attachments. Rather than discussing it in detail, we mention other sources ...

0
How to Use QUERY Function of Google Sheets in Excel (3 Common Ways)
0

Google Sheets offers a versatile way to work with data in your spreadsheets. The QUERY function is one of the most powerful functions in Google Sheets. It ...

0
How to Convert Month Name to Number in Excel (5 Suitable Ways)
0

In terms of working with Microsoft Excel, we might face the necessity of converting month name to number. There is no need to panic. We can do it quite simply. ...

0
How to Copy Number Not Formula in Excel (5 Quick Tricks)
0

Regarding working with Microsoft Excel, we use different formulas and multiple features to format cells. Sometimes, we might need to copy just the values, not ...

0
How to Convert Decimal to Whole Number in Excel (10 Easy Ways)
0

In terms of working with Microsoft Excel, we need to work with numbers all the time. It is a common phenomenon that we might need the numbers into whole form ...

0
How to Merge CSV Files in Excel (2 Easy Ways)
0

CSV files are comma-separated values files, and can be used to store tabular data. They can be opened in text editors like Notepad, or in spreadsheet programs ...

0
How to Insert PDF as Image in Excel (2 Simple Ways)
0

In terms of working with Microsoft Excel, we might face the necessity of inserting a PDF file in Excel. It is nothing unusual. We can insert the pdf file as ...

0
How to Change Author Name in Excel Comments (3 Easy Ways)
0

In every Microsoft Excel Worksheet, there is the name of the author of that file. However, we can change the author's name. It’s not a tough task at all. You ...

Browsing All Comments By: Naimul Hasan Arif
  1. Reply
    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
    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
    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
    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.

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

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

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

  8. Reply
    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.

  9. Reply
    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.

  10. Reply
    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.

  11. Reply
    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.

  12. Reply
    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.

  13. Reply
    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.

  14. Reply
    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.

  15. Reply
    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.

  16. Reply
    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!

  17. Reply
    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] 

  18. Reply
    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.

  19. Reply
    Naimul Hasan Arif Sep 25, 2022 at 3:25 PM

    Thanks for sharing your valuable thoughts.

  20. Reply
    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.

  21. Reply
    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.

  22. Reply
    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.

  23. Reply
    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

  24. Reply
    Naimul Hasan Arif Aug 30, 2022 at 12:30 PM

    Thanks for your appreciation and for sharing your modified code.

  25. Reply
    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

  26. Reply
    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.

  27. Reply
    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

  28. Reply
    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.

  29. Reply
    Naimul Hasan Arif Aug 21, 2022 at 12:01 PM

    Thanks for your appreciation. It means a lot.

  30. Reply
    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

  31. Reply
    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,””))

  32. Reply
    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))

  33. Reply
    Naimul Hasan Arif Aug 16, 2022 at 3:34 PM

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

  34. Reply
    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.

  35. Reply
    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.

  36. Reply
    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.

  37. Reply
    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.

  38. Reply
    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.

  39. Reply
    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”.

  40. Reply
    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”.

  41. Reply
    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.

  42. Reply
    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.

  43. Reply
    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.

ExcelDemy
Logo