'Atanas wanted to take his data
| ' Date | Items |
| ' 01.09.2009 | IT004 |
| ' 01.09.2009 | IT004 |
| ' 01.09.2009 | IT005 |
| ' ... | |
| ' 01.09.2009 | IT006 |
'and convert it to this
| ' | 01.09.2009 | 06.09.2009 | 7.09.2009 |
| IT004 | 2 | 2 | 1 |
| IT005 | 1 | 1 | 2 |
| IT006 | 3 | 0 | 1 |
'
'Pivot tables can solve this problem easily.
'The only problem is pivot tables are not so easy.
'At least until one figures out the terminology.
'Then they're not so bad.
xlA1 = 1
xlDatabase = 1
xlDataField = 4
set xl = createObject("excel.application")
set wb = xl.workbooks.add()
set wsData = wb.worksheets(1)
set wsReport = wb.worksheets(2)
xl.visible = true
'add some data to 1st worksheet (wsData)
with wsData
.range("a1:b1" ).value = array("Date" , "Items")
.range("a2:b2" ).value = array("01.09.2009", "IT004")
.range("a3:b3" ).value = array("01.09.2009", "IT004")
.range("a4:b4" ).value = array("01.09.2009", "IT005")
.range("a5:b5" ).value = array("01.09.2009", "IT006")
.range("a6:b6" ).value = array("01.09.2009", "IT006")
.range("a7:b7" ).value = array("01.09.2009", "IT006")
.range("a8:b8" ).value = array("06.09.2009", "IT004")
.range("a9:b9" ).value = array("06.09.2009", "IT004")
.range("a10:b10").value = array("06.09.2009", "IT005")
.range("a11:b11").value = array("07.09.2009", "IT004")
.range("a12:b12").value = array("07.09.2009", "IT005")
.range("a13:b13").value = array("07.09.2009", "IT005")
.range("a14:b14").value = array("07.09.2009", "IT006")
end with
'create pivot table report in 2nd worksheet starting at cell A3
'ITEMS will be row field
'DATE will be column and data field
set rngData = wsData.usedRange
set rngReport = wsReport.range("a3")
set pvtCache = wb.pivotCaches.add(xlDatabase, rngData.address(true, true, xlA1, true))
set pvtTable = pvtCache.createPivotTable(rngReport)
pvtFieldsRow = array("Items")
pvtFieldsCol = array("Date")
pvtTable.addFields pvtFieldsRow, pvtFieldsCol
pvtTable.pivotFields("Date").orientation = xlDataField
'Note:
'In my old version of excel, I had to use this line to make it work
' set pvtCache = wb.pivotCaches.add(xlDatabase, rngData.address(true, true, xlA1, true))
'In Excel 2007, it can be changed to this line
' set pvtCache = wb.pivotCaches.add(xlDatabase, rngData)
' Not sure if my old version is bad or if "that's just the way it is."
[Via]