![Mac Mac](/uploads/1/1/9/5/119561214/334712305.jpg)
Click inside the table to select it. Then, click on the Design tab on Excel's ribbon. On the left side of this menu, find the Table Name box and type in a new name for your table. Make sure that it's a single word (no spaces are allowed in table names.). But using a pivot table to create an Excel frequency distribution Table is the easiest way. This part (way 2 of 7) is part of my mastering Excel pivot table series: Pivot Table Tutorials for Dummies: Learn Excel Pivot Table Step by Step. The following figure shows part of a table. The table has a record of 221 students and their test scores. Create a crosstab query within minutes using the Query Wizard. A crosstab query is a special type of query that calculates a sum, average, or other aggregate function, and then groups the results by two sets of values — one down the left side of the datasheet and the other across the top.
- Points
- 22
- Trophies
- 1
- Posts
- 6
Hi,I have a simple spreadsheet that looks like this:Origin State Dest State Company
AL AL A
AL AR A
AL AZ B
AZ AL A
AZ AR C..and so on. Basically, the first 2 columns have all states/provinces and
column 3 has the company we use. I need to create one matrix with all
states/provinces in Row 1 and in Column 1, and all the cells in between are
populated with the company.Question is How can I do this using MS Excel using Pivot Table. I can do this using Business Objects or Crosstab Query in MS Access but how do I do it in Excel using Pivot Table? This can be done by using INDIRECT and MATCH functions but this involves a couple of steps. Is there any shorter method?I have attached an excel sheet that gives what I have, What I can do using Access or Business Objects and what I get using Pivot Table in Excel. The normal Pivot Table in Excel gives numbers instead of the company name even if I use Max or Min function (which works fine in MS Access query). So what am I missing here?Thanks in anticipation,
Warm Regards,
Kallol- Navigation
- OzGrid
- Forum
- Members
- Options
- Current Location
This site uses cookies. By continuing to browse this site, you are agreeing to our use of cookies.Your browser has JavaScript disabled. If you would like to use all features of this site, it is mandatory to enable JavaScript.
How To Create A Cross Tab Table In Excel For Mac 2016 Version
Good Day,
I am relatively new to the VBA coding arena so please bare with my ignorance.
I am trying to apply some code I found on the internet to take some data from an excel table and create the equivalence of a cross tab query in another excel worksheet within the same workbook. Essentially I am trying to calculate the average of a data value with in the table and to aggregate this information by end user and month. Here is the code I am using:
Sub ApplyCrossTab()
Dim Myconnection As ADODB.Connection
Dim Myrecordset As ADODB.Recordset
Dim Myworkbook As String
Dim strSQL As String
Dim i As Integer
Set Myconnection = New Connection
Set Myrecordset = New Recordset
'Identify the workbook you are referencing
Myworkbook = ActiveWorkbook.Sheets('Completions').Select
'Open connection to the workbook
Myconnection.Open 'Provider=Microsoft.ACE.OLEDB.12.0;' & _
'Data Source=' & Myworkbook & ';' & _
'Extended Properties='Excel 12.0;HDR=YES;';'
'& _
'Persist Security Info=False'
'Build SQL Statement
strSQL = 'TRANSFORM Avg(CycleTime) ' & _
'SELECT cust_nm_top, Product ' & _
'FROM Completions ' & _
'GROUP BY cust_nm_top, Product ' & _
'PIVOT Month'
'Load the Query into a Recordset
Debug.Print strSQL
Myrecordset.Open strSQL, Myconnection, adOpenStatic, adLockOptimistic, adCmdText
'Place the Recordset onto Sheet2
With ActiveWorkbook.Sheets('Sheet2').Range('A1')
.CopyFromRecordset Myrecordset
End With
'Place the Recordset onto Sheet2
With Sheets('Sheet2')
.Range('A2').CopyFromRecordset Myrecordset
'Add column heading names
For i = 1 To Myrecordset.Fields.Count
.Cells(1, i).Value = Myrecordset.Fields(i - 1).Name
Next i
End With
End Sub
When I attempt to run the code I receive the following error box
Run-rime error '-2147217865(80040e37)':
The Microsoft Access database engine could not find the object 'Completions'.
If I use the Provider=Microsoft.Jet.OLEDB.4.0, I also receive the same error.
Any help would be greatly appreciated.
I am relatively new to the VBA coding arena so please bare with my ignorance.
I am trying to apply some code I found on the internet to take some data from an excel table and create the equivalence of a cross tab query in another excel worksheet within the same workbook. Essentially I am trying to calculate the average of a data value with in the table and to aggregate this information by end user and month. Here is the code I am using:
Sub ApplyCrossTab()
Dim Myconnection As ADODB.Connection
Dim Myrecordset As ADODB.Recordset
Dim Myworkbook As String
Dim strSQL As String
Dim i As Integer
Set Myconnection = New Connection
Set Myrecordset = New Recordset
'Identify the workbook you are referencing
Myworkbook = ActiveWorkbook.Sheets('Completions').Select
'Open connection to the workbook
Myconnection.Open 'Provider=Microsoft.ACE.OLEDB.12.0;' & _
'Data Source=' & Myworkbook & ';' & _
'Extended Properties='Excel 12.0;HDR=YES;';'
'& _
'Persist Security Info=False'
'Build SQL Statement
strSQL = 'TRANSFORM Avg(CycleTime) ' & _
'SELECT cust_nm_top, Product ' & _
'FROM Completions ' & _
'GROUP BY cust_nm_top, Product ' & _
'PIVOT Month'
'Load the Query into a Recordset
Debug.Print strSQL
Myrecordset.Open strSQL, Myconnection, adOpenStatic, adLockOptimistic, adCmdText
'Place the Recordset onto Sheet2
With ActiveWorkbook.Sheets('Sheet2').Range('A1')
.CopyFromRecordset Myrecordset
End With
'Place the Recordset onto Sheet2
With Sheets('Sheet2')
.Range('A2').CopyFromRecordset Myrecordset
'Add column heading names
For i = 1 To Myrecordset.Fields.Count
.Cells(1, i).Value = Myrecordset.Fields(i - 1).Name
Next i
End With
End Sub
When I attempt to run the code I receive the following error box
Run-rime error '-2147217865(80040e37)':
The Microsoft Access database engine could not find the object 'Completions'.
If I use the Provider=Microsoft.Jet.OLEDB.4.0, I also receive the same error.
Any help would be greatly appreciated.