Excel delete duplicated data in consecutive rows

Posted on February 26, 2017 by Wyman W

This Excel tutorial explains how to delete duplicated data in consecutive rows (delete the value, not delete row). You may also want to read: Excel delete blank rows Remove duplicates in text Excel delete duplicated data in consecutive rows In conventional Pivot Table layout (Tabular), all data are grouped together and duplicated data would not repeat. Division Department Div1 Dept1 Div2 Dept2 Dept3 Dept4 Div3 Dept5 Dept6 ...

Create Cartesian product with Excel Query

Posted on February 15, 2017February 15, 2017 by Wyman W

This Excel tutorial explains how to create Cartesian product with Excel Query. You may also want to read: Microsoft Access create Cartesian product with Cross Join Create Excel Query and update Query Create Cartesian product with Excel Query In SQL, there are several types of Table Join: Left Join, Right Join, Inner Join and Cross Join. Cross Join is to produce all possible combination of records in joined Tables which are unrelated (without common key), the result is known as Cart...

Create Excel Query and update Query

Posted on February 14, 2017March 9, 2019 by Wyman W

This Microsoft Excel tutorial explains how to create Excel Query, create Join Table, update Query, add Query criteria. You may also want to read: Excel automatically select specific columns using Custom Views and Query Microsoft Excel create Query Similar to Microsoft Access Query, Excel allows users to create Query through graphical user interface, which means you don't need to have technical skills to write any SQL statement. Although Microsoft Excel has the capability to do that, Acce...

Microsoft Access produce Cartesian product with Cross Join

Posted on February 8, 2017February 15, 2017 by Wyman W

This Microsoft Access tutorial explains how to produce Cartesian product with Cross Join in Access Query. You may also want to read: Create Cartesian product with Excel Query Microsoft Access Cartesian product with Cross Join In SQL, there are several types of Table Join: Left Join, Right Join, Inner Join and Cross Join. Cross Join is to produce all possible combination of records in joined Tables which are unrelated (without common key), the result is known as Cartesian product. Most...

Excel VBA use Inputbox to select options

Posted on February 6, 2017 by Wyman W

This Excel tutorial explains how to use VBA Inputbox to select options using SELECT CASE. Excel VBA use Inputbox to select options In my previous post, I demonstrated how to use VBA Inputbox to input a value and create Msgbox to return the value. In this post I will create an Inputbox for users to select options with the help of Select Case. In the below example, I create three options in the Inputbox for users to select: 1. Mary 2. May 3. Susan If users select something else, a...

Solution to Access Error 3047 Record is too large

Posted on February 5, 2017September 24, 2017 by Wyman W

This Microsoft Access tutorial explains how to work around the error 3047 Record is too large when using DoCmd.TransferSpreadSheet Method. Access Error 3047 Record is too large Although Microsoft Access is a database management system, unfortunately it does not allow users to put too much data in the database. According to Access 2016 specifications , Access Table record is limited to 4000 characters, and the total size of Access is 2GB. If you create a Table that contains a record with ...

Access replace Crosstab Query with Expression

Posted on February 5, 2017 by Wyman W

This Microsoft Access tutorial explains how to replace Crosstab Query with Expression in order to add multiple aggregate values. Access replace Crosstab Query with Expression In previous post, I have introduced how to use Crosstab Query to build a matrix report. However, Crosstab Query has a limitation that it can only add one aggregate value in the report, which is the Sum of amount in the above example. I also don't like the fact that I need to define the order of the month in the Pro...

Excel delete all comments in Workbook

Posted on February 5, 2017 by Wyman W

This Excel tutorial explains how to delete all Comments in Workbook using VBA and without using VBA. You may also want to read: Excel show all comments in worksheet Excel consolidate all Comments in the workbook Excel - delete all Comments in Worksheet In Excel spreadsheet, if you know where the comments locate, you can delete multiple Comments by selecting multiple Cells, then right click on any selected Cells > select Delete Comment. If you are not certain where ...

Excel compare time or apply condition on timestamps

Posted on February 4, 2017 by Wyman W

This Excel tutorial explains how to compare time or apply condition on timestamps in Excel spreadsheet Excel compare time or apply condition on timestamps When we have two timestamps (a date with time) on the same date, we can use simple subtraction on the two timestamps to compare. If the two timestamps have different dates, we can still minus the timestamps to compare. But if we only want to compare the time only without considering the date, we need to extract the time portion of time...

Access VBA change Query criteria using QueryDef.SQL Property

Posted on February 1, 2017 by Wyman W

This Access VBA tutorial explains how to change Query criteria in VBA using QueryDef.SQL Property. You may also want to read: Create Query using CreateQueryDef Access VBA - change Query criteria It is easy to change Query criteria in Query View, but sometimes your criteria may depend on a variable that is not a constant. In Access VBA, you can change the Query criteria using QueryDef.SQL Property. In fact, QueryDef.SQL does not really update just the update the criteria of the statement,...