Access-Excel.tips

New Post has been published on https://access-excel.tips/excel-pivot-table-distinct-count-unique/

Excel Pivot Table Distinct Count to count unique value

This Excel tutorial explains how to use distinct count in Pivot Table to count number of unique value in a column grouped by other fields.

Excel Pivot Table Distinct Count to count unique data

In Excel 2013, there is a new aggregate function in Pivot Table called Distinct Count, which counts number of unique value in a column. For example, if a column contains employee names, you can use the distinct count function to count number of unique employee names in the column such as below.

In this tutorial, I am going to demonstrate how to do distinct count.

Example – distinct count number of unique employee names by department

Suppose we have a staff list below. We want to see how many unique employee names are in the same department.

  Select the concerned data, navigate to Insert > Pivot Table, then in the Create PivotTable dialog, check the box Add this data to the Data Model > OK

This option is very important as Distinct Count function will not be available if you don’t check this box.

  In the Pivot Table, drag Department and Employee Name to the Rows, drag Employee Name to the Values. By default, the aggregate function on the value is Count.

  Click on the arrow next to Count of Employee Name, select Value Field Settings

In the Value Field Settings, select Distinct Count > OK

  Now the Pivot Table displays the distinct count of employee name by department and display each all the names under each department.

Overall, there are a total of 17 staff, as there are two Cathy and two Icy, the distinct count of employee name in the whole company is 15.

  Alternatively, display the distinct count without displaying the employee name.

  Outbound References

New Post has been published on https://access-excel.tips/import-csv-into-excel-workbooks-opentext-method/

Excel VBA Import CSV into Excel using Workbooks.OpenText Method

This Excel VBA tutorial explains how to import CSV into Excel automatically using Workbooks.OpenText Method. You may select different delimiters such as Tab, semicolon, comma, space.

Excel VBA Import CSV into Excel using Workbooks.OpenText Method

In Excel workbook, you can manually import a CSV file into Excel (Data > From Text / CSV). However, you have to select some options in advance such as delimiter. In order to import CSV into Excel automatically, you may use Workbooks.Open Text Method.

Syntax of Workbooks.Open Text Method

Workbooks.OpenText(FileName, Origin , StartRow , DataType , TextQualifier , ConsecutiveDelimiter , Tab , Semicolon , Comma , Space , Other , OtherChar , FieldInfo , TextVisualLayout , DecimalSeparator , ThousandsSeparator , TrailingMinusNumbers , Local)

Name Required/Optional Data type Description FileName Required String Specifies the file name of the text file to be opened and parsed. Origin Optional Variant Specifies the origin of the text file. Can be one of the following xlPlatform constants: xlMacintosh, xlWindows, or xlMSDOS. Additionally, this could be an integer representing the code page number of the desired code page. For example, “1256” would specify that the encoding of the source text file is Arabic (Windows). If this argument is omitted, the method uses the current setting of the File Origin option in the Text Import Wizard. StartRow Optional Variant The row number at which to start parsing text. The default value is 1. DataType Optional Variant Specifies the column format of the data in the file. Can be one of the following XlTextParsingType constants: xlDelimited or xlFixedWidth. If this argument is not specified, Microsoft Excel attempts to determine the column format when it opens the file.

Name Value Description xlDelimited 1 Default. Indicates that the file is delimited by delimiter characters. xlFixedWidth 2 Indicates that the data in the file is arranged in columns of fixed widths.

TextQualifier Optional Variant

Name Value Description xlTextQualifierDoubleQuote 1 Double quotation mark (“). xlTextQualifierNone -4142 No delimiter. xlTextQualifierSingleQuote 2 Single quotation mark (‘).

ConsecutiveDelimiter Optional Variant True to have consecutive delimiters considered one delimiter. The default is False. Tab Optional Variant True to have the tab character be the delimiter (DataType must be xlDelimited). The default value is False. Semicolon Optional Variant True to have the semicolon character be the delimiter (DataType must be xlDelimited). The default value is False. Comma Optional Variant True to have the comma character be the delimiter (DataType must be xlDelimited). The default value is False. Space Optional Variant True to have the space character be the delimiter (DataType must be xlDelimited). The default value is False. Other Optional Variant True to have the character specified by the OtherChar argument be the delimiter (DataType must be xlDelimited). The default value is False. OtherChar Optional Variant (required if Other is True). Specifies the delimiter character when Other is True. If more than one character is specified, only the first character of the string is used; the remaining characters are ignored. FieldInfo Optional Variant An array containing parse information for individual columns of data. The interpretation depends on the value of DataType. When the data is delimited, this argument is an array of two-element arrays, with each two-element array specifying the conversion options for a particular column. The first element is the column number (1-based), and the second element is one of the XlColumnDataType constants specifying how the column is parsed.

Name Value Description xlDMYFormat 4 DMY date format. xlDYMFormat 7 DYM date format. xlEMDFormat 10 EMD date format. xlGeneralFormat 1 General. xlMDYFormat 3 MDY date format. xlMYDFormat 6 MYD date format. xlSkipColumn 9 Column is not parsed. xlTextFormat 2 Text. xlYDMFormat 8 YDM date format. xlYMDFormat 5 YMD date format.

TextVisualLayout Optional Variant The visual layout of the text. DecimalSeparator Optional Variant The decimal separator that Microsoft Excel uses when recognizing numbers. The default setting is the system setting. ThousandsSeparator Optional Variant The thousands separator that Excel uses when recognizing numbers. The default setting is the system setting. TrailingMinusNumbers Optional Variant Specify True if numbers with a minus character at the end should be treated as negative numbers. If False or omitted, numbers with a minus character at the end are treated as text. Local Optional Variant Specify True if regional settings of the machine should be used for separators, numbers and data formatting.

  Example – Import CSV into Excel using Workbooks.OpenText Method

Suppose we have a staff list as below in csv file, in which the delimiter is comma with double quotation around text that contains comma (job title). Uur goal is import CSV into Excel and delimit the data automatically.

In the VBA code, for the case of a mix of double quotation and no double  quotation, we can skip the TextQualifier argument. We only have to identify the file path and delimiter as below.

Public Sub OpenCsvFile()   .OpenText Filename:="C:\Users\WYMAN\Desktop\staff list.csv", DataType:=xlDelimited, comma:=True End Sub

Create a new workbook, press ALT+F11 to insert the above procedure and then execute the procedure. The CSV file will open in Excel and the data is delimited properly.

  Note that OpenText Method only opens the CSV in Excel but it is not importing the data into the current workbook.

To do so, we can add some codes to copy the worksheet over to the current workboook .

Public Sub OpenCsvFile() Application.ScreenUpdating = False Workbooks.OpenText Filename:="C:\Users\WYMAN\Desktop\staff list.csv", DataType:=xlDelimited, comma:=True With ActiveWorkbook .ActiveSheet.Copy After:=ThisWorkbook.Sheets(Sheets.Count) .Close End With Cells.Select Cells.EntireColumn.AutoFit Range("A1").Select Application.ScreenUpdating = True End Sub

  Execute the above procedure, now the delimited csv is added to the current workbook in a new worksheet.

  Outbound References

New Post has been published on https://access-excel.tips/excel-vba-convert-text-in-columns-and-rows-into-matrix-table/

Excel VBA Convert Text in Columns and Rows into Matrix Table

This Excel VBA tutorial explains how to convert text in columns and rows into Matrix Table. The Matrix table will display text instead of aggregated numbers.

You may also want to read:

Access Crosstab Query

Excel VBA Convert Text in Columns and Rows into Matrix Table

Using Excel Pivot Table, it is easy convert data into a Matrix Table with the help of Aggregate Functions such as Count, Sum, standard deviation. The information in the Matrix Table is displayed in aggregated numbers. Below is an example of a Matrix Table using aggregated function.

However if you want to create a Matrix Table in which you want to display nominal data (text) instead of aggregated numbers, Pivot Table is not technically possible.

In this tutorial, I am going demonstrate how to convert text in columns and rows into Matrix Table to display nominal data using Excel VBA.

VBA Code – Convert Columns and Rows into Matrix Table

Press ALT+F11 and insert the below VBA Procedure into a Module. Note that the you probably need to customize your own code in order to fit your needs, the below Procedure is just an example to show you how it can be done.

Public Sub convertMatrix() 'assume data worksheet contains employee info, while matrix worksheet is the end result For r = 2 To Worksheets("data").Range("A" & Rows.Count).End(xlUp).Row Name = Worksheets("data").Range("A" & r) dept = Worksheets("data").Range("B" & r) Title = Worksheets("data").Range("C" & r) salary = Worksheets("data").Range("D" & r) grade = Worksheets("data").Range("E" & r) 'search for department column number in the matrix table For c = 1 To Worksheets("matrix").Range("IV" & 1).End(xlToLeft).Column If Worksheets("matrix").Cells(1, c).Value = dept Then matrixCol = c Exit For End If Next c 'search for grade row in the matrix table For g = 2 To Worksheets("matrix").Range("A" & Rows.Count).End(xlUp).Row If Worksheets("matrix").Cells(g, 1) = grade Then matrixRow = g Exit For End If Next g 'Convert columns and rows into matrix table If Worksheets("matrix").Cells(g, c).Value <> "" Then Worksheets("matrix").Cells(g, c).Value = Worksheets("matrix").Cells(g, c).Value & vbCrLf & vbCrLf & Name & vbCrLf & Title & vbCrLf & salary Else Worksheets("matrix").Cells(g, c).Value = Name & vbCrLf & Title & vbCrLf & salary End If Next r End Sub

Example – Convert Columns and Rows into Matrix Table

Suppose data worksheet contains the employee data that you want to convert into matrix table.

The employees highlighted in yellow are in the same department and of the same grade, I will demonstrate how it will display in the matrix table.

  matrix worksheet contains the layout of the matrix, where we want to put the employee data into a matrix of Department and Grade.

  Now execute the Procedure, employees and their info are put into the matrix table.

For employee Cat and Cathy, since they are in the same department and they are of the same grade, both their info are put into Cell B4, separated by a blank row.

Outbound References

New Post has been published on https://access-excel.tips/search-text-in-multiple-workbooks/

Excel VBA search text in multiple Workbooks in folder

This Excel VBA tutorial explains how to search text in multiple Workbooks in a folder and subfolders, and display the result in a summary page, including which workbook, which worksheet, and which Cell contains the text.

You may also want to read:

Excel VBA INSTR Function

Excel loop workbooks in folders and subfolders with FSO

Excel VBA search text in multiple Workbooks in folder

Suppose you have multiple workbooks in a folder, and you want to know if any workbook contains a text you want to look for. The easiest way to do this is to press Ctrl + F in the folder you want to search for the text, then the search result will display.

However this method does not always work for different reasons. If it doesn’t work, then you need to look for an alternate approach. In this post, I will demonstrate how to use Excel VBA to search text in multiple workbooks in a folder and subfolders, and display the result in a summary page, including which workbook, which worksheet, and which Cell contains the text.

VBA Code – search text in multiple Workbooks in folder

Create a new workbook, press ALT+F11 and insert the below code in a Module. Do not save this workbook in the folder which you want to search the text.

Public Sub searchText() Dim FSO As Object Dim folder As Object, subfolder As Object Dim wb As Object Dim ws As Worksheet searchList = Array("orange", "apple", "pear") 'define the list of text you want to search, case insensitive Set FSO = CreateObject("Scripting.FileSystemObject") folderPath = "C:\test" 'define the path of the folder that contains the workbooks Set folder = FSO.GetFolder(folderPath) Dim thisWbWs, newWS As Worksheet 'Create summary worksheet if not exist For Each thisWbWs In ActiveWorkbook.Worksheets If wsExists("summary") Then counter = 1 End If Next thisWbWs If counter = 0 Then Set newWS = ThisWorkbook.Worksheets.Add(After:=Worksheets(Worksheets.Count)) With newWS .Name = "summary" .Range("A1").Value = "Target Keyword" .Range("B1").Value = "Workbook" .Range("C1").Value = "Worksheet" .Range("D1").Value = "Address" .Range("E1").Value = "Cell Value" End With End If With Application .DisplayAlerts = False .ScreenUpdating = False .EnableEvents = False .AskToUpdateLinks = False End With 'Check each workbook in main folder For Each wb In folder.Files If Right(wb.Name, 3) = "xls" Or Right(wb.Name, 4) = "xlsx" Or Right(wb.Name, 4) = "xlsm" Then Set masterWB = Workbooks.Open(wb) For Each ws In masterWB.Worksheets For Each Rng In ws.UsedRange For Each i In searchList If InStr(1, Rng.Value, i, vbTextCompare) > 0 Then 'vbTextCompare means case insensitive. nextRow = ThisWorkbook.Sheets("summary").Range("A" & Rows.Count).End(xlUp).Row + 1 With ThisWorkbook.Sheets("summary") .Range("A" & nextRow).Value = i .Range("B" & nextRow).Value = Application.ActiveWorkbook.FullName .Range("C" & nextRow).Value = ws.Name .Range("D" & nextRow).Value = Rng.Address .Range("E" & nextRow).Value = Rng.Value End With End If Next i Next Rng Next ws ActiveWorkbook.Close True End If Next 'Check each workbook in sub folders For Each subfolder In folder.SubFolders For Each wb In subfolder.Files If Right(wb.Name, 3) = "xls" Or Right(wb.Name, 4) = "xlsx" Or Right(wb.Name, 4) = "xlsm" Then Set masterWB = Workbooks.Open(wb) For Each ws In masterWB.Worksheets For Each Rng In ws.UsedRange For Each i In searchList If InStr(1, Rng.Value, i, vbTextCompare) > 0 Then nextRow = ThisWorkbook.Sheets("summary").Range("A" & Rows.Count).End(xlUp).Row + 1 With ThisWorkbook.Sheets("summary") .Range("A" & nextRow).Value = i .Range("B" & nextRow).Value = Application.ActiveWorkbook.FullName .Range("C" & nextRow).Value = ws.Name .Range("D" & nextRow).Value = Rng.Address .Range("E" & nextRow).Value = Rng.Value End With End If Next i Next Rng Next ws ActiveWorkbook.Close True End If Next Next With Application .DisplayAlerts = True .ScreenUpdating = True .EnableEvents = True .AskToUpdateLinks = True End With ThisWorkbook.Sheets("summary").Cells.Select ThisWorkbook.Sheets("summary").Cells.EntireColumn.AutoFit ThisWorkbook.Sheets("summary").Range("A1").Select End Sub Function wsExists(wksName As String) As Boolean On Error Resume Next wsExists = CBool(Len(Worksheets(wksName).Name) > 0) On Error GoTo 0 End Function

Enable Microsoft Scripting Runtime

FileSystemObject (FSO) provides an API to access the Windows filesystem such as accessing Drive, TextStram, Folder, File.

You should be able to run FSO in Excel 2013. If you fail to run FSO Object, open VBE (ALT+F11) > Tools > References > Check the box Microsoft Scripting Runtine

Example – search text in multiple Workbooks in folder

Suppose I want to search text “orange”, “apple”, “pear” in all workbooks under c:\test\, plus workbooks in one level down subfolders under c:\test\

I want to return search result even though the search text is a partial text in a Cell.

For example, if Cell A1 value of a workbook is “Orange Juice”, I still want to return the result because it contains “orange”.

Then create a new workbook that contains the VBA code, change the parameters that highlighted in red. Run the Procedure.

New Post has been published on https://access-excel.tips/select-the-first-record-each-group-using-first-function/

MS Access select the first record of each group using First Function

This MS Access tutorial explains how to select the first record of each group using First Function.

MS Access select the first record of each group

When I create human resources report, I have always come across a situation where one employee has more than one records. For example, I want to create a staff list where one employee only has one row of record. If I add emergency contact field to the report and if an employee has more than one emergency contacts, then the Query will return one employees with multi-rows of record as below.

Student ID Name Emergency Contact 1 Apple Mary 1 Apple John 2 Banana Patrick 2 Banana Queenie 3 Cat Anthony

  In order to avoid multiple records of each EE, we have to extract the first record of Emergency Contact for each employee as shown below.

Student ID Name Emergency Contact 1 Apple Mary 2 Banana Patrick 3 Cat Anthony

Although it is not ideal as the other Emergency Contact is lost in the staff list, it is still the best workaround we can have.

In this post, I will demonstrate how to do it in MS Access.

MS Access select the first record of each group – using First Function

First of all, Group by Student ID and Name (Home > totals icon), then in the Emergency Contact, select aggregate function First, it means after the data is grouped by Student ID, Name, the first record of each group is returned.

  Then we get the below result.

Student ID Name Emergency Contact 1 Apple Mary 2 Banana Patrick 3 Cat Anthony

If you want to apply sorting before selecting the first record, you should create another Query in advance to sort Student ID and Emergency Contact, then the first Emergency Contact changed.

  There is also another Function called Last, which select the last record of each group.

  Result

Student ID Name Emergency ContactOfLast 1 Apple John 2 Banana Queenie 3 Cat Anthony

Instead of applying First and Last, you may also consider Min and Max Function. When applying Min and Max, the texts are compared in the same way as sorting text in Excel.

Outbound References

New Post has been published on https://access-excel.tips/how-to-change-default-excel-date-format/

How to Change Default Excel Date Format (such as MMDD to DDMM)

This Excel tutorial explains how to change default Excel Date Format from Change Date and Time Format in Control Panel.

How to Change Default Excel Date Format

In the country I live, we normally use date format dd/mm/yyyy. However whenever I work for the US company, the system defaults to mm/dd/yyyy. This is annoying and it causes Excel error. For example, if I mistakenly type UK format 31/12/2019, the date will be recognized as a text because there is no such month as 31. It is even risky if you don’t see this error as you are not aware that you have mistakenly input a wrong date. Although you can simply use Text Function to change the date format, it is just a one time solution and it does not address the issue every time you create a new Workbook.

This tutorial will explain how to change default Excel date format from Windows setting. The change does not just apply to Excel but all the related Windows environment. Note that if you are not a Windows administrator, you may not be able to make this change.

Example – Change Default Excel Date Format in Control Panel

Suppose we want to change default Excel date format from dd/mm/yyyy to mm/dd/yyyy.

  In Windows 10, navigate to Control Panel > Date and Time > Change Date and Time Format

Even if you are using older versions of Windows, the navigation maybe a little bit different, you can still find this option.

  The Short Date format is currently displaying dd/MM/YYYY, now change to MM/dd/yyyy.

  Go back to Excel spreadsheet, the value in column A automatically changed.

This makes complete sense because if the date format doesn’t change, all your existing workbooks will be messed up.

Outbound References

New Post has been published on https://access-excel.tips/how-to-convert-excel-to-pdf/

How to convert Excel to PDF

This Excel tutorial explains how to export Excel to PDF in Microsoft Excel and convert Excel to PDF using different online tools.

How to convert Excel to PDF

Excel spreadsheets are widely being used in today’s world in order to manage data. The ability to organize data effectively has contributed a lot towards the popularity of the Excel files. However, the Excel files are not portable and it is not the best method available for you to send out a document. That’s because there is a possibility for the recipient to edit the Excel file. The formatting that you have on the Excel file can also get changed when you are sending it to someone. That’s where you will come across the requirement to convert Excel to PDF.

When you want to convert Excel to PDF, you are provided with a variety of options to consider. It is up to you to go through these options and select the best one out of them. Then you will be able to get an enhanced experience at the end of the day. In fact, you can easily overcome the hassle and pain that is linked with getting the Excel files converted to PDF.

Here is a list of 3 of the best tools, which are available for you to convert Excel to PDF. You can take a look at these tools and settle down with the best option out of them.

Onlineconvertfree.com

Onlineconvertfree.com can be considered as one of the most impressive and innovative tools available for you to convert Excel to PDF. You will be able to get an enhanced experience out of this tool. However, you need to understand that the primary functionality of this tool is not to help you convert Excel to PDF. Instead, it can be considered as a versatile and an all-around converter. You will be able to convert any kind of a file to a supported file format with the assistance that you are getting from onlineconvertfree.com.

The simplicity of onlineconvertfree.com, which you can experience at the time of converting Excel files to PDF files is impressive. You will love the overall conversion process and how it will be able to deliver quick results to you. Onlineconvertfree.com has got a simple and easy to use interface. You can easily upload the raw files that you have into this file converter. Then you will be able to select the output file format. For example, if you upload an Excel file, you can select the output file format as PDF. Then you will be able to get the file converted with minimum hassle. The Excel to PDF conversion process is instant as well and you will be able to end up with outstanding results at the end of the day.

Nova PDF

Nova PDF can be considered as an addon, which you can download to Excel. You can easily integrate this add-on with Excel and then proceed with the file conversion. It is compatible with all the applications that come under Microsoft Office package, including Microsoft Excel and Microsoft PowerPoint. On the other hand, users are provided with the opportunity to create PDF files directly from the Microsoft Excel interface. This can provide a smooth experience.

Once you have configured Nova PDF with Microsoft Excel, which you have installed on your computer, you will be able to see that as a new menu item. You can visit the menu and then click on the option named as “Save As PDF”. Then you will be able to initiate the PDF conversion. The Excel workbook will immediately be converted into a PDF as well and you will be able to experience a smooth conversion. Hence, it is a great option that you can consider.

Export Excel to PDF in Microsoft Excel

Without downloading and configuring any other tool with Microsoft Excel, you are able to get the PDF conversions done as well.

In Excel, navigate to File > Save As, in the Save As Type,  you can save a file as different file types. Below are the available types in Excel 2013.

  Select PDF, then click on the button Options

  In Options, you can select whether to export Entire workbook to PDF or just Active worksheet(s).

Note that Entire workbook means all worksheets are exported to one PDF.

Active sheet(s) means to export the selected sheets to one PDF. Normally Active sheet means the worksheet you are currently viewing before you press Save As, but you can hold down Ctrl to select multiple worksheets in order to export specific worksheets to one PDF.

New Post has been published on https://access-excel.tips/group-dates-ungroup-month-excel-pivot-table/

Group Dates and Ungroup Month in Excel Pivot Table

This Excel tutorial explains how to group dates and ungroup month in Excel Pivot Table to prevent auto grouping of Pivot Table in Excel 2016.

Group Date and Ungroup Month in Excel Pivot Table

Since Excel 2016,  when you drag a date field to a Pivot Table Pivot Table Row, the dates are  automaticlaly grouped by months instead of showing each date. This is extremely annoying because  it is tricky to ungroup the months if it is the first time you encounter this issue.

In this post I am going to explain how to ungroup month in Excel Pivot Table and how to group dates by year or month.

Note that there is an Excel Option called Group dates in the AutoFilter Menu in Excel option, it does not work for Pivot Table Row Grouping. This option is for normal Auto Filter, not Pivot Table, so don’t get confused.

Ungroup Month in Excel Pivot Table

Suppose we have a data source which are dates from 2019 to 2021.

  Insert a Pivot Table, and then drag the date field to Rows,the dates are automatically grouped by Year, Quarter and Month by default.

  In order to display the original dates without grouping, right click on the data, select Group.

  In the dates Grouping options, you can redefine how you want to group the dates. To display the original  dates on each row, highlight Days then OK.

  Now the months are ungrouped and are displayed on each row.

  Alternatively, you can simply right click on the date, then select Ungroup. this will ungroup every grouping.

  Outbound References

New Post has been published on https://access-excel.tips/access-vba-delete-table-records-sql/

Access VBA delete Table records with SQL using DoCMD.RunSQL Method

This Access VBA tutorial explains how to use VBA delete Table records with SQL without alert using DoCMD.RunSQL Method.

You may also want to read:

Access VBA run Query or run Action Query

Access delete Table records

In my previous post, I have explained how to delete Table records without using VBA, below is a quick recap.

Suppose we have created a table called student in Access, which contains 5 student records.

student Student ID Student Name 001 Apple 002 Betty 003 Cathy 004 David 005 Elyse

In order to delete the record (the whole row) of Apple, create a new Query, add student table.

Under Design tab, click on Delete button. This will create a Delete Query.

  Add Student ID to the field, then type “001” in criteria, which is the student ID of Apple.

  To preview the result of Delete Query (which records will be deleted), click on the View button under Design.

To execute the Query (actually delete the records), click on Run button, then click on Yes to confirm delete row.

  Access VBA delete Table records

Similar to executing Delete Query in Access UI, you can delete Access Table records in VBA. First write a standard Delete SQL statement, then use DoCMD.RunSQL Method to execute the SQL.

For example, we have a student Table as below.

student Student ID Student Name 001 Apple 002 Betty 003 Cathy 004 David 005 Elyse

  Press ALT+F11 and insert the below code in Module.

Public Sub deleteRecord()     DoCmd.SetWarnings False     strSQL = "Delete * From [student] WHERE [Student ID]='002'"     DoCmd.RunSQL strSQL     DoCmd.SetWarnings True End Sub

Writing DoCmd.SetWarings False is because we want to avoid Access generating the below Alert so that the subsequent statements can be executed smoothly.

  Run the Sub, then you will see the below result where Student ID 002 is deleted.

student Student ID Student Name 001 Apple 003 Cathy 004 David 005 Elyse

  Outbound References

New Post has been published on https://access-excel.tips/access-roundup-rounddown-function/

VBA Excel Access roundup rounddown Function

This Excel Access tutorial explains how to use VBA to write an Access roundup (round up) and Access rounddown (round down) Function to simulate that in Excel. These roundup and rounddown Functions can also be used in Access and Excel VBA.

Excel Access roundup rounddown Function

In Excel Worksheet Function, there are round, roundup, rounddown, mround Functions.

In Excel VBA, there is only round Function but no roundup, rounddown, mround Functions. The round Function is not the same as that in Excel Worksheet Function, the round logic that is being used is called Round-To-Even logic. However, you can still access Worksheet Functions in Excel VBA with WorksheetFunction Method as below.

Application.WorksheetFunction.RoundUp(1.23, 1)

In Microsoft Access (VBA / application) there is only round function that uses Round-To-Even logic. Therefore, there is a need to recreate round, roundup, rounddown, mround Functions in VBA, no matter in Access or Excel VBA. In my previous posts, I have written VBA Function to simulate Excel mround and round Functions. In this post, I will write roundup and rounddown Functions.

VBA Code – Excel Access roundup Function

Public Function wRoundUp(pValue, digit) As Double     ExpandedValue = Abs(pValue) * (10 ^ digit) 'Retrieve integer part of the number     wRoundUp = Sgn(pValue) * Int(ExpandedValue + 0.99999999) / 10 ^ digit End Function

Explanation

This Excel Access roundup Function performs two major actions. First, the target number is expanded to the decimal places which you want to round up, and then add 0.9999999 to the number, so that it goes up to the next integer. Finally use INT Function to extract the integer part. The sign of the number is ignored during round up and down to simulate the behavior in Excel, then it is added back after rounding.

For example, we have a number 14.56 and we want to round up to 1 decimal places. 14.56 is first expanded to 145.6, and then add 0.9999999, so that it becomes something like 146.6. Finally extract the integer part (146), divided by 100 to become 14.6.

VBA Code – Excel Access rounddown Function

Public Function wRoundDown(pValue, digit) As Double     ExpandedValue = Abs(pValue) * (10 ^ digit) 'Retrieve integer part of the number     wRoundDown = Sgn(pValue) * Int(ExpandedValue) / 10 ^ digit End Function

Explanation

This Excel Access rounddown Function performs two major actions. First, the target number is expanded to the decimal places which you want to round up, then we use INT Function to extract the integer part. The sign of the number is ignored during round up and down to simulate the behavior in Excel, then it is added back after rounding.

For example, we have a number 14.56 and we want to round down to 1 decimal places. 14.56 is first expanded to 145.6, and then we extract only 145. Finally divided by 10 to become 14.5.

Example – roundup and rounddown

New Post has been published on https://access-excel.tips/excel-vba-generate-random-password/

Access Excel VBA generate random password or random characters

This Access Excel VBA tutorial explains how to generate random password or random characters with random number, random letters and random symbols.

You may also want to read:

Excel VBA generate non-duplicated random number

Excel VBA Rnd Function to generate random number

Access Excel VBA generate random password or random characters

If you are a system administrator, very likely you need to genrate login password for users. Some people who are not good at Excel generate password combination that has a pattern which is predictable by other users. In order to generate a truly random password that cannot be guessed by anyone, it is easier to write a VBA custom Function.

In my previous post, I have explained how to generate random characters, so in this post I will focus on generate random password.

Gather requirement of random password

Many companies have password policy, the most common one is password must have 8 characeters with at least 1 upper & lower alphabets + number + symbol.

To generate such combination, the easiest way is to define which character is what character type. For example, the combination can be like this:

1st char 2nd char 3rd char 4th char 5th char 6th char 7th char 8th char any a-z A-Z 0-9 symbol any any any

Now the question is how we generate each character type.

Access Excel VBA Custom Function – generate random password

The custom function below allows you to select which random character type you want to generate.

Public Function wRandomPassword(Optional rndType = 1) As String Randomize Select Case rndType Case 1 'generate any characters wRandomPassword= Chr(Int((126 - 33 + 1) * Rnd + 33)) Case 2 'generate a-z or A-Z randVariable = Int((122 - 65 + 1) * Rnd + 65) Do While randVariable > 90 And randVariable < 97 randVariable = Int((122 - 65 + 1) * Rnd + 65) Loop wRandomPassword= Chr(randVariable) Case 3 'generate a-z wRandomPassword= Chr(Int((122 - 97 + 1) * Rnd + 97)) Case 4 'generate A-Z wRandomPassword= Chr(Int((90 - 65 + 1) * Rnd + 65)) Case 5 'generate 0-9 wRandomPassword= Chr(Int((57 - 48 + 1) * Rnd + 48))   Case 6 'generate symbols wRandomPassword= Chr(Int((47 - 33 + 1) * Rnd + 33)) End Select End Function

Syntax of wRandomPassword – generate random password

wRandomPassword(Optional rndType = 1)

RndType Explanation 1 generate any characters 2 generate a-z or A-Z 3 generate a-z 4 generate A-Z 5 generate 0-9 6 generate symbols

Example of wRandomPassword – generate random password

Back to the previous password requirement below

1st char 2nd char 3rd char 4th char 5th char 6th char 7th char 8th char any a-z A-Z 0-9 symbol any any any

In Excel worksheet, we can type the below formula

=wRandomPassword()&wRandomPassword(3)&wRandomPassword(4)&wRandomPassword(5)&wRandomPassword(6)&wRandomPassword()&wRandomPassword()&wRandomPassword()

Result

  Generate Password without password policy requirement

If you don’t have any requirement on the password, then we can

Public Function wRandomPassword() As String    Randomize    For i = 1 To 8      tempStr = tempStr & Chr(Int((126 - 33 + 1) * Rnd + 33))    Next i   wRandomPassword = tempStr End Function

Result

  Outbound References

New Post has been published on https://access-excel.tips/select-specific-columns-custom-views-query/

Excel automatically select specific columns using Custom Views and Query

This Excel tutorial explains how to select specific columns in a worksheet with many columns using Custom Views and Query.

You may also want to read:

Create Excel Query and update Query

Excel automatically select specific columns using Custom Views and Query

Many people including myself like creating a master report with many columns and then send it to users for them to manually select columns they need. The reason is that it is time consuming to customize a lot of reports and it is difficult to maintian when report conditions update. With a single source of data, we can simply maintain the master report.

However, many users complain that there are too many columns in the report that they do not need. To cope with this difficulty, Excel Custom Views function allows useres to select specific columns to save as a template. So everytime they receive the master files, the columns can be automatically selected.

You can also use Custom Views to save hidden rows,  filter,  window settings,  print settings, print areas. The focus of this post is to demonsate how to hide columns.

Create Custom Views

Lets say we have a master file with columns A to G. Now we want to select column A C F only.

  Manually Hide columns B D E G

  Select View Tab > Custom Views > Add

  Type a name for the View > OK

  Apply Custom Views

Let’s say you receive the master list in the next month. Copy the contents from the master list to the exact worksheet that you previously set Custom Views. Custom Views won’t work your data is not in the worksheet which you created Custom Views for. It also doesn’t work if you delete the original worksheet and then rename to the same worksheet name.

  Navigate to View tab > Custom Views > Show

  Only columns A C F are displayed.

  Select specific columns using Query

Another better solution to automatically select specific columns is to create a Query in Excel. The reason is that Query recognizes the column header name to select, not the actual column order such as A B C. You may refer to the details in my previous post on how to create Excel Query,  but I will quickily demonstrate how to do it in this post.

Let’s say we have saved the master list on the Desktop.

Create a new workbook, select Data tab > from Other Sources > from Microsoft Query

  Select Excel Files > OK

  Select the master list location to import, then select field headers 1, 3, 6

  Select next step until finish. Now columns 1, 3, 6 are imported.

  Now whenever you update the master list, refresh this Table  (Data > Refresh All) to get the latest dta from master list.

If you are unable to refersh the data, go to Trust Center Settings to configure the Trusted Location and Extenal Content.

Outbound References

New Post has been published on https://access-excel.tips/excel-percentile-percentrank-exc/

Excel find the percentile of a number using PERCENTRANK.EXC function

This Excel tutorial explains how to find the percentile of a number from an array using PERCENTRANK.EXC function.

Excel Percentile

Percentile is a statistics term used to describe the Nth observation in a data set after sorting in ascending order. For example, 40th percentile (P40) refers to the approximately 40th data of the 100 data. If the data set is not exactly 100, say 200, we find the location of observation at 40% of total data.

Percentile is commonly used in salary survey to find out the 25th 50th, 75th, 90th percentile. 50% percentile is known as median, human resources department always compares P50 salary with employees’ salary so that they know who are below market compensation. Some companies may have salary guideline of P75, which means they generally pay better than the market in order to attract and retain talents. Percentile is a better measure than average because extreme data are disregarded using percentile.

Find the percentile of a number using PERCENTRANK.EXC function

In my previous post, I have demonstrated how to calculate the number given a percentile.

Suppose data set 1, 3, 5, 7 lie in Range A1:A4. To find tne number at Percentile 75,

= PERCENTILE.EXC(A1:A4,0.75)

= 6.5

In this post I am going to the opposite. Function PERCENTRANK.exc (Percent Rank) is to claculate percentile given a number.

Syntax of PERCENTRANK.EXC function

PERCENTRANK.EXC(array,x,[significance])

Array Required. The array or range of data with numeric values that defines relative standing X Required. The value for which you want to know the rank. Significance Optional. A value that identifies the number of significant digits for the returned percentage value. If omitted, PERCENTRANK.EXC uses three digits (0.xxx).

Example of PERCENTRANK.EXC function

Let’s say our company has 4 staff, their salary are as follows

I want to know if salary $25000 is high or low in the company.

=PERCENTRANK.EXC(B2:B5,25000)

= 0.5   (50th percentile)

New Post has been published on http://access-excel.tips/copy-contents-of-protected-worksheet/

Excel VBA copy contents of protected worksheet

This Excel VBA tutorial explains how to copy contents of protected worksheet and then paste contents in another worksheet.

You may also want to read:

Excel VBA protect worksheet with password

Excel VBA hide worksheet with password (xlVeryHidden)

Copy contents of protected worksheet

Some authors would protect worksheets with password so that other people cannot modify and not even able to select the cells. I usually do it when I setup templates for users to fill in the data but I don’t want them to mess up my formula, so I protect my formula cells and allow them to modify other cells.

There is absolutely no way to modify the protected worksheet without knowing the password, and you cannot manually copy the contents because you are not allowed select the cells.

However, with the help of VBA, you can easily copy the contents and paste to another worksheet without clicking on the cells.

VBA Code – Copy contents of protected worksheet

Suppose you want to copy all contents and formats from a worksheet called “protected ws”, and then paste to a worksheet called “new ws”, then use the below VBA code.

Public Sub copyContents() Sheets("protected ws").Cells.Copy Sheets("new ws").Range("A1").Select ActiveSheet.Paste End Sub

New Post has been published on http://access-excel.tips/excel-vba-convert-date-period-to-date-row/

Excel VBA convert date period to date row

This Excel VBA tutorial explains how to convert date period to date row.

Excel VBA convert date period into date row

Different systems store date transactions differently. For example, if you apply for annual leave from Jan 1 to Jan 3, some system store the data in data base using date period

Begin Date End Date 1/1/2018 1/3/2018

However some system store one date per row.

Begin Date End Date 1/1/2018 1/1/2018 2/1/2018 2/1/2018 3/1/2018 1/3/2018

To convert from one format to another is extremely difficult and time consuming. In this tutorial, I will demonstrate how to convert date period to date row, because I personally think this data structure is easier to handle.

VBA code – convert date period to date row

Press ALT+F11, insert the code into a Module.

Public Sub convertDate() beginDtCol = "B" 'column that contains begin date period endDtCol = "C" 'column that contains end date period For r = Range(beginDtCol & Rows.Count).End(xlUp).Row To 2 Step -1 If Range(endDtCol & r) - Range(beginDtCol & r) > 0 Then For i = 1 To Range(endDtCol & r) - Range(beginDtCol & r) Rows(r).EntireRow.Copy Range("A" & Range(beginDtCol & Rows.Count).End(xlUp).Row + 1).Select ActiveSheet.Paste Range(beginDtCol & Range(beginDtCol & Rows.Count).End(xlUp).Row) = Range(beginDtCol & r) + i Range(endDtCol & Range(endDtCol & Rows.Count).End(xlUp).Row) = Range(beginDtCol & r) + i Next i Range(endDtCol & r) = Range(beginDtCol & r) End If Next Range("A1").AutoFilter 'sort column A ActiveSheet.AutoFilter.Sort.SortFields.Add Key:=Range("A:A"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal ActiveSheet.AutoFilter.Sort.Apply End Su

  Example – convert date period to date row

Suppose we have two leave transactions below.

  Run the Macro, date period is converted to date row. In addition, all the other column values are copied.

New Post has been published on http://access-excel.tips/excel-vba-filter-value-then-copy-data-to-new-worksheet/

Excel VBA filter value then copy data to new worksheet

This Excel VBA tutorial explains how to automate AutoFilter to filter value and then copy data to new worksheet or copy data  to new workbook.

You may also want to read:

Excel VBA copy each worksheet to new workbook

Access Excel Scripting Dictionary store unique array item

Filter value then copy data to new worksheet

I find it very difficult to come up with a suitable title and key words for this post. I will try to describe what this Macro does with an example.

Suppose you have a staff list as below.

Now you want to create one worksheet for each department.

Worksheet “HR”

Department Staff ID Name HR 1 Peter HR 2 Apple HR 3 Banana

Worksheet “IT”

Department Staff ID Name IT 2 John IT 6 Judy

Worksheet “CS”

Department Staff ID Name CS 4 Cat CS 5 David

  If you have a hundred of unique department, it will take you forever to manually copy the data over to the new worksheet. What I want to achieve is to create a Macro to automatically filter each department and then copy the contents to a new worksheet.

VBA Code – Filter value then copy data to new worksheet

To use this Macro:

1) Copy and paste the below code in a Module

2) Modify the 3rd row of code where the targetCol is the column that contains the department you want to break into separate worksheets

3) Select the worksheet that contains the staff list

4) Run the Macro

Public Sub FilterThenCopy()    Dim ws, newWS, currentWS As Worksheet    targetCol = 1   'define which column you want to break    Dim objDict As Variant    Set objDict = CreateObject("Scripting.Dictionary")    Set currentWS = ActiveSheet 'Add unique value in targetCol to the dictionary    Application.DisplayAlerts = False    For r = 2 To Cells(Rows.Count, targetCol).End(xlUp).Row      If Not objDict.exists(Cells(r, targetCol).Value) Then        objDict.Add Cells(r, targetCol).Value, Cells(r, targetCol).Value      End If    Next r   If currentWS.AutoFilterMode = True Then      currentWS.UsedRange.AutoFilter End If   currentWS.UsedRange.AutoFilter   For Each k In objDict.Keys     currentWS.UsedRange.AutoFilter field:=1, Criteria1:=objDict.Item(k)    'delete worksheet if worksheet of item(k) exist     For Each ws In ActiveWorkbook.Worksheets       If wsExists(objDict.Item(k)) Then         Sheets(objDict.Item(k)).Delete       End If     Next ws    'crate worksheet using item(k) name     Set newWS = ThisWorkbook.Worksheets.Add(After:=Worksheets(Worksheets.Count))     newWS.Name = objDict.Item(k)     'copy filtered contents to new worksheet     currentWS.Cells.Copy     newWS.Range("A1").Select     Sheets(objDict.Item(k)).Paste   Next k   currentWS.Activate   currentWS.AutoFilterMode = False   Application.DisplayAlerts = True End Sub Function wsExists(wksName As String) As Boolean    On Error Resume Next    wsExists = CBool(Len(Worksheets(wksName).Name) > 0)    On Error GoTo 0 End Function

Result

After you run the Macro, 3 new worksheets are created. The format should also be copied from the master staff list.

  Filter value then copy to new workbook

New Post has been published on http://access-excel.tips/access-excel-vba-sort-array-items/

Access Excel VBA sort Array items

This Access Excel VBA tutorial explains how to sort Array items in VBA in ascending order / descending order.

You may also want to read:

Use VBA Excel Function to sort data in ascending order

Access Excel VBA sort Array items

In an Array, you may have stored items that you want to sort. For example, if you store a name list, you may want to sort them in alphabetical order (Apple, Banana, Cat…).  If you store a number list, you may want to sort them in ascending order (100, 200, 400, 700…). Since there is no built in VBA function to sort Array items, we have to write a sorting logic manually to do the job. In this post, I write a Procedure that uses Bubble Sort method to do the sorting.

After sorting the Array items, you may want to know the ranking (Index) of specific Array items. Read my previous post to learn more.

Sort Array items in ascending order

The sorting sequence is:  blank > symbol > number > alphabet (disregard capital letter or not)

VBA Code

Public Sub sortAscending(arr as variant) Dim arr As Variant arr = Array(30, 70, 50, 99) lngMin = LBound(arr) lngMax = UBound(arr) For i = lngMin To lngMax - 1 For j = i + 1 To lngMax If arr(i) > arr(j) Then strTemp = arr(i) arr(i) = arr(j) arr(j) = strTemp End If Next j Next i End Sub

Example

Public Sub test()    Dim arr As Variant    arr = Array(30, 70, 50, 99)    Call sortAscending(arr)    MsgBox (arr(0) & " " & arr(1) & " " & arr(2) & " " & arr(3)) End Sub Public Sub sortAscending(arr As Variant)    lngMin = LBound(arr)    lngMax = UBound(arr)    For i = lngMin To lngMax - 1      For j = i + 1 To lngMax        If arr(i) > arr(j) Then          strTemp = arr(i)          arr(i) = arr(j)          arr(j) = strTemp        End If      Next j    Next i End Sub

Result

  Sort Array items in descending order

Simply replace  arr(i) > arr(j) with  arr(i) < arr(j) to sort Array in descending order.