Excel convert data from column to row

Posted on September 27, 2017October 26, 2017 by Wyman W

This Excel VBA tutorial explains how to convert data from column to row (transform one column data to one row). You may also want to read: Excel VBA consolidate multiple workbooks into one workbook Excel VBA combine worksheets columns into one worksheet Excel convert data from column to row Different database have different structure. Some database put similar data in the same column with different rows, while some database put data in different columns. .Let's take an example. Typ...

Add sorting in Access Table field

Posted on September 21, 2017 by Wyman W

This Access tutorial explains how to add sorting in Access Table field. You may also want to read: Sort Data in Access Report Add Auto number in Access Query Add sorting in Access Table field It is easy to add sorting in Access Query field in Query Design View (set in the "sort" row), but to add sorting in Access Table field is done differently. Let's say we have the following employee table, where the Employee ID is not sorted. Although you can click on the arrow next the...

Excel Exact Function – compare case sensitive text / case sensitive lookup

Posted on September 17, 2017 by Wyman W

This Excel tutorial explains how to compare case sensitive text using Excel Exact Function and how to perform case sensitive lookup / vlookup. Excel Exact Function to Compare case sensitive text / case sensitive lookup (vlookup) In Microsoft Excel, you cannot perform case sensitive comparison directly using equal sign. For example, "Peter" is considered same as "peter" in Excel. Vlookup also shows matching result regardless of case sensitivity. In order to compare case sensitive text, we...

Access Case Sensitive Join Table (Inner Join, Left Join)

Posted on September 16, 2017September 16, 2017 by Wyman W

This Access tutorial explains how to perform case sensitive join table, such as case sensitive inner join and case sensitive left join. You may also want to read:Access StrComp Function to Compare text (case sensitive comparison) Access Case Sensitive Join Table In Microsoft Access, comparison of text is not case sensitive. For example, Access considers "AAA" and "aaa" are the same. To compare String in Expression, please refer to my previous post about how to use StrComp Function. The m...

Access StrComp Function to Compare text (case sensitive comparison)

Posted on September 15, 2017September 16, 2017 by Wyman W

This Access tutorial explains how to use Access StrComp Function to compare text, both case sensitive and case insensitive comparison. You may also want to read: Access Case Sensitive Join Table (Inner Join, Left Join) Access StrComp Function to Compare text (case sensitive comparison) In Microsoft Excel, you cannot perform case sensitive comparison directly using equal sign. For example, if you type formula ="A"="a" The formula returns TRUE. Vlookup also shows matching result even tho...

Access Excel VBA convert number to English words

Posted on September 8, 2017 by Wyman W

This Access Excel VBA tutorial explains how to convert number to English words in VBA. Access Excel VBA convert number to English words I want to share a custom Function I found in Microsoft Support website, which is to convert number to English words using Excel VBA (also applicable to Access). I want to highlight that the article is unlike other articles written by other contributors, this article has no author and I believe this Function was created by the Microsoft engineers, so this custo...

Access VBA import workbook to Access using Transferspreadsheet

Posted on August 28, 2017 by Wyman W

This Access tutorial explains how to import workbook to Access using DoCmd.Transferspreadsheet Method, such as importing specific worksheet to Access, importing multiple worksheets to Access. You may also want to read: Access VBA DoCmd.OutputTo Method Access Export all Queries to Excel Access VBA import workbook to Access using DoCmd.Transferspreadsheet Access VBA DoCmd.Transferspreadsheet Method is used to import workbook to Access and export Access to workbook, I have briefly demo...

Access VBA run Query or run Action Query

Posted on August 25, 2017 by Wyman W

This Access VBA tutorial explains how to run Query and run Action Query in Access VBA. Access VBA run Query or Action Query Imagine you have to run 10 Action Query (e.g. Create Table, Update, Delete Table) in a row to get the final result, you have to run each Action Query one by one and the most annoying part is to click the confirmation dialogs. With Access VBA we can run a series of Action Query automatically and skip all the confirmation dialogs, saving a lot of time. In the following e...

Excel delete all pictures or delete all pictures in specific Cells

Posted on August 23, 2017 by Wyman W

This Excel tutorial explains how to delete all pictures in a worksheet or delete all pictures in specific Cells. Excel delete all pictures When you copy contents from website to Excel, it is unavoidable to also copy unwanted pictures. As some websites contains dozens of pictures, it is a waste of time to delete pictures one by one. In this tutorial, I will demonstrate how to delete all pictures at once. Let's say I want to copy the below table from a website. In the Country of Origin field...

Excel assign sequence number to duplicate records

Posted on July 10, 2017 by Wyman W

This Excel tutorial explains how to assign sequence number to duplicate records using COUNTIF Function. Excel assign sequence number to duplicate records Suppose you have two sales transactions on the same date but you don't have any transaction timestamp (but records are in ascending order of time), you may want to add a number to each transaction so that you can easily identify and filter the first transaction, 2nd transaction, etc. Assigning sequence number can also facilitate to use SQL to...