Aung Shine

About author

Aung Shine completed his bachelor’s in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. It has been almost 2 years since he joined SOFTEKO and actively working on the ExcelDemy project. Currently he works as a Team Leader where he guides his team members to create technical content. He has published 150+ articles and reviewed 50+ articles. He has also solved various user problems before. He has interests in Data Analysis, Power Query, Advanced Excel, Statistics, etc.

Designation

Team Leader at ExcelDemy in SOFTEKO.

Lives in

Dhaka, Bangladesh.

Education

B.sc in Electrical and Electronics Engineering (EEE), BUET.

Expertise

Content Writing, VBA, Matlab, AutoCAD, Microsoft Office.

Experience

  • Technical Content Writing
  • Team Management

Summary

  • Currently working as a Team Leader of ExcelDemy.
  • Started technical content writing of Excel & VBA in January 2022 later prompted as Team Leader for Content Management.

Latest Posts From Aung Shine

0
How to Remove Comma in Excel

The comma makes texts and numbers easier to read by acting as a separator. But when importing data from other file types, Excel might change decimal points to ...

0
How to Calculate Percentage Change in Excel (4 Examples)

The following dataset contains some products (B5:B14) and their sales in January (C5:C14) and February (D5:D14). We have calculated the percentage change ...

0
How to Copy Cell If Condition Is Met in Excel (7 Easy Ways)

Microsoft Excel is a powerful program. We can perform numerous tasks on datasets using Excel tools and features. There are many default Excel functions that we ...

0
How to Open VCF Files in Excel (2 Methods)

Microsoft Excel is a powerful tool for working with datasets but opening VCF (Virtual Contact File) files directly in Excel can be a bit tricky. VCF files ...

0
How to Open CSV with Delimiter in Excel (6 Methods)

Method 1 - Opening CSV with Delimiter Through Excel Power Query Editor The sample dataset below has been created in Notepad and includes Salesman, Product, ...

0
How to Adjust Chart Spacing in Excel Clustered Column

Microsoft Excel is a powerful program. We can perform numerous tasks on datasets using Excel tools and features. There are many default Excel functions that we ...

0
How to Calculate Semi Variance in Excel – 3 Simple Methods

  Semi Variance evaluates the variation among the observations in a sample. Semi-variance deals  with the observations that are below the average of ...

0
How to Calculate Percentage Completed Based on Dates in Excel: 4 Methods

A starting and an ending date are needed. We’ll use the TODAY function for the current date. You can input any date. To illustrate, we’ll use the following ...

0
Create Retirement Budget Worksheet in Excel (with Easy Steps)

Microsoft Excel is a powerful software. We can perform numerous operations on our datasets using Excel tools and features. There are many default Excel ...

0
How to Create Petty Cash Book Format in Excel (With Easy Steps)

  Step 1 - Design Petty Cash Book Input the required headers for the cash book. In the Received column, insert the initial amount the petty ...

0
How to Change Date Alignment in Excel (8 Simple Methods)

Microsoft Excel is a powerful software. We can perform numerous operations on our datasets using Excel tools and features. There are many default Excel ...

0
How to Calculate Monthly Average from Daily Data in Excel

Microsoft Excel is a powerful software. We can perform numerous operations on our datasets using Excel tools and features. There are many default Excel ...

0
[Fixed!] Box and Whisker Plot Not Showing in Excel

Microsoft Excel is a powerful software. We can perform numerous operations on our datasets using tools and features. There are many default Excel Functions ...

0
How to Make a Stacked Histogram in Excel (3 Easy Methods)

Microsoft Excel is a powerful software. We can perform numerous operations on our datasets using Excel tools and features. There are many default Excel ...

0
How to Convert Time to Seconds in Excel (3 Easy Methods)

The following dataset contains 5 different times in a 12-hour time format. We’ll transform them into seconds. You can go through the below methods one by one ...

Browsing All Comments By: Aung Shine
  1. Hi KEVIN,
    Thanks for reaching out to us. To get only the parameters you mentioned, adjust the XPath expressions used to select the specific elements from the XML response. Here’s the modified code:

    
    Option Explicit
    
    Function ReverseGeocode(lati As Double, longi As Double) As String
        On Error GoTo ErrorHandler
        
        Dim xD As New MSXML2.DOMDocument
        Dim URL As String
        
        xD.async = False
        URL = "https://nominatim.openstreetmap.org/reverse?lat=" & CStr(lati) & _
              "&lon=" & CStr(longi)
        
        xD.Load (URL)
        
        If xD.parseError.ErrorCode <> 0 Then
            ' Handle parsing errors
            ReverseGeocode = "Error: " & xD.parseError.reason
        Else
            ' Set XPath selection language
            xD.SetProperty "SelectionLanguage", "XPath"
            
            ' Select specific elements using XPath
            Dim country As MSXML2.IXMLDOMElement
            Dim state As MSXML2.IXMLDOMElement
            Dim city As MSXML2.IXMLDOMElement
            Dim county As MSXML2.IXMLDOMElement
            Dim zipCode As MSXML2.IXMLDOMElement
            
            Set country = xD.SelectSingleNode("/reversegeocode/addressparts/country")
            Set state = xD.SelectSingleNode("/reversegeocode/addressparts/state")
            Set city = xD.SelectSingleNode("/reversegeocode/addressparts/city")
            Set county = xD.SelectSingleNode("/reversegeocode/addressparts/county")
            Set zipCode = xD.SelectSingleNode("/reversegeocode/addressparts/postcode")
            
            ' Build the result string in usual address manner
            ReverseGeocode = country.Text & ", " & state.Text & ", " & city.Text & ", " & _
                             county.Text & ", " & zipCode.Text
        End If
        Exit Function
    ErrorHandler:
        ' Handle runtime errors
        ReverseGeocode = "Error: " & Err.Description
    End Function
    

    Hope this helped. Let us know your further queries.
    Regards,
    Aung
    Team ExcelDemy

  2. Hello ALEKS,
    Thanks for reaching out to us. Make sure that all the cells in the date column of the Pivot table are in the accurate date format. Only then the date filter will work properly. Moreover, ensure the Excel option mentioned in the second solution of the article is ticked. Hope this helped.
    Reach out to us again for any further queries.
    Regards,
    Aung

  3. Hello,
    Thanks a lot for letting us know about this process. We’ve added the new method.
    Regards,
    Aung
    ExcelDemy

  4. Hello, thanks for reaching out.

    I am not sure which step you’re referring to. If it’s the first drop-down under the Edit the Rule Description, please choose Format only cell with: Cell Value from the options. However, you should see that being selected by default.
    Kindly let us know your further queries.

    Regards,
    Aung
    ExcelDemy

  5. Thanks for reaching out.

    You can follow method 3 of this article to accomplish your task. Go to the destination sheet and click a cell where you want to paste the names. Insert this formula:
    =FILTER(Source!A2:B20, “Y”=Source!C2:C20)
    Press Enter to get the results. Here, “Source” is the sheet name of your source sheet where the names are present along with Yes/No. A2:B20 is the cell range of the first and last names. Change it according to your dataset. C2:C20 is the cell range with Yes/No.

    This should work. Hope this helped.
    Regards,
    Aung
    ExcelDemy.

  6. Thanks for reaching out.

    Suppose, the technician’s name is Jim. You can follow method 1 of this article to manually copy the row after applying a filter for Jim. Then you can paste it in the sheet for Jim.

    You can also follow sub-method 2.2 of method 2 of this article to apply VBA to perform your task. In the code, you just have to change the sheet names according to your requirement. You should also modify the range to look for the technician name: in your case it’s column D.
    Suppose, the main sheet name is Sheet1 and the destination sheet name is Jim, our technician. The code will be:

    
    Sub MoveRow_KeepOriginal()
    Dim rg As Range
    Dim xc As Range
    Dim p As Long
    Dim q As Long
    Dim r As Long
    p = Worksheets("Sheet1").UsedRange.Rows.Count
    q = Worksheets("Jim").UsedRange.Rows.Count
    If q = 1 Then
    If Application.WorksheetFunction.CountA(Worksheets("Jim").UsedRange) = 0 Then q = 0
    End If
    Set rg = Worksheets("Sheet1").Range("D1:D100" & p)
    On Error Resume Next
    Application.ScreenUpdating = False
     For r = 1 To rg.Count
     If CStr(rg(r).Value) = "Jim" Then
     rg(r).EntireRow.Copy Destination:=Worksheets("Jim").Range("A" & q + 1)
     q = q + 1
    End If
    Next
    Application.ScreenUpdating = True
    End Sub
    

    This code should do the work. You can try this by changing the technician name. Hope this helped.
    Regards,
    Aung
    ExcelDemy

  7. Thank you Michael for your comment. We made adjustments in the Excel file. Please find the added VBA code for the last example you mentioned.
    Regards,
    Aung

  8. Thank you very much. This article shows how you can pull data from different worksheets present in a workbook. However, if you want to extract data from sheets present in different workbooks, kindly go through the article linked below. It will guide you through the complete procedure.

    https://www.exceldemy.com/excel-macro-extract-data-from-multiple-excel-files/

    Moreover, you can create a simple formula:

    =’D:\SOFTEKO\[task_problems.xlsx]Sheet1′!$C$18

    Where D is the drive location, SOFTEKO is the folder name, task_problems is the desired excel file, Sheet1 is the worksheet, and C18 is the required cell value.

    Here, make changes according to your requirements.

    Good luck.

  9. Thank you very much. This article shows how you can pull data from different worksheets present in a workbook. However, if you want to extract data from sheets present in different workbooks, kindly go through the article linked below. It will guide you through the complete procedure.

    https://www.exceldemy.com/excel-macro-extract-data-from-multiple-excel-files/

    Good luck.

  10. We’re very glad to hear that we could be of help to you as well.

    Good luck.

  11. Thank you very much for correcting us. We removed the reference as it’s not really necessary. You just have to color the cells E5 and E6 in blue and orange respectively. This is for the purpose of taking reference in the argument of the function we inserted in cells F5 and F6.

    Thank you again.

  12. We’re happy to help you out. Kindly refresh the worksheet if you change the cell colors. You’ll find the Refresh button in the Data tab. However, if we can improve the code to update it automatically, we’ll let you know.

    Good luck.

  13. We’re glad to know that we could help you out. In the case of the VBA method, kindly refresh the worksheet after you change the color of the cells. You’ll find the Refresh button in the Data tab. However, if we can improve the code to update it automatically, we’ll let you know.

    Good luck.

  14. Thanks for reaching out to us. Please go through the article linked below. I believe that will help to solve your issue. You can also mail us your Excel file at [email protected] and state your conditions.

    https://www.exceldemy.com/merge-two-columns-in-excel/

    Good luck.

  15. Thank you so much for pointing out the issue. We have updated the article. Now it’s alright. Please check.

  16. Thanks for reaching out. Kindly mail me your Excel file and queries at [email protected]
    I’ll be happy to help.

  17. Thanks, glad we could help you.

  18. Welcome ASIM, glad we could help you.

  19. Thanks, LOUIS for reaching out to us. Please drop your excel file and queries at [email protected]
    This is Aung, one of the writers of ExcelDemy. I’d like to help to solve your problem, but I didn’t exactly understand your criteria. I hope you elaborate a bit further.
    Thanks.

  20. Hello SHERRY,

    This is Aung, one of the writers/developers in ExcelDmey. Thank you for pointing out the issue. We have re-uploaded the excel file. Now you can download it without any problem.

    Good luck.

  21. Hello BRIAN,

    This is Aung, one of the writers/developers in ExcelDmey. I’ve looked into your matter. I understand you want to split the cell values. Kindly send me your excel file at [email protected]

    I’ll make the necessary changes and explain them to you.

    Good luck.

  22. Hello BRYAN,

    This is Aung, one of the writers/developers in ExcelDmey. I’ve looked into your matter. What you need to do is just modify your formula. Choose any one from the below 2 formulas:

    =SUMPRODUCT(AA3:AA43,(–(TEXT(I3:I43,”mmmm”)=TEXT(B1,”mmmm”))))

    =SUMPRODUCT(AA3:AA43,(–(MONTH(I3:I43)=MONTH(B1))))

    Both should work perfectly fine.

    Good luck.

  23. Thank you Roger for the beautiful comment.

  24. Thank you ROGER for reaching out to us. Sure you can send me your files at my mail: [email protected]
    I’ll be happy to help you.

  25. Welcome WILLIAM, it’s always a pleasure to hear that we were of any help to you.
    Thanks for the nice comment. Good luck.

  26. Hello SHAWN,
    Thank you for letting us know your queries. Yes, there’s a way to attach a button to run your desired macro. Just insert any shape (Insert > Illustrations > Shapes) in the worksheet. Then, right-click on the shape to get the Context Menu. There, select the Assign Macro option. Hence, it’ll return a dialog box. Choose your desired macro and press OK. In this way, you don’t have to go to the VBA window to press the Run key.
    Hope you can perform the task. Please reach out to me at: [email protected] for further queries.
    Good luck.

  27. Hello COEN,
    Thanks for reaching out to us. Regarding your issue, select the range A1:I30 of Sheet1 first. Then, go to Sheet2. Press the Ctrl key, and select the range A1:I20 simultaneously. Thus, you’ll have selected your desired ranges from both sheets. Now, follow the steps in method 3. In this way, you’ll have your required email body.
    Good luck.

  28. Hello JKS,
    Thank you so much for pointing out the mistake. We’ve uploaded the accurate VBA codes in methods 2 & 3.
    And about your other issue regarding the screenshots, the codes are really long. That’s why we have demonstrated the upper portions only. But the entire code is there.
    Lastly, we are grateful for your feedback. It helps us to grow.
    Good luck.

  29. Thank you Hendry for reaching out.
    I’ve looked into your matter. You can easily carry out the mentioned operation using the 5th method in this article i.e. VBA method.
    Kindly try with VBA and if you still can’t get the job done, feel free to email me. My mail: [email protected]
    Good luck.

  30. Hello DARRYL,
    I’m not sure what you tried to mean by missing colors. The Headers are still blue in the email body. Please reach out to me at: [email protected] for any further queries. I’ll be happy to help.
    Good luck.

  31. Welcome SHAUL. It’s always a pleasure to hear that we were of any help to you.
    Good luck.

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo