Counting rows by the different date

I have a column of dates and a column of items.

Hi Everyone I want to count the number of items for a certain date, how many of them are per day. Column 1 Date - Column 2 - Items

01.09.2009  IT004  
01.09.2009  IT004
01.09.2009  IT005
01.09.2009  IT006
01.09.2009  IT006
01.09.2009  IT006
06.09.2009  IT004
06.09.2009  IT004
06.09.2009  IT005
07.09.2009  IT004
07.09.2009  IT005
开发者_Go百科07.09.2009  IT005
07.09.2009  IT006

---

              01.09.2009   06.09.2009   07.09.2009
For It004         2            2             1
For It005         1            1             2
For It006         3            0             1

Any help would be greatly appreciated and many thanks in advance.

Atanas

---

If that's all the table has, this should work:

SELECT datecol, count(*) FROM table GROUP BY datecol

---

You can use Pivot Table to do this.

Use Items as Row Field.

Use Date as Column and Data Field (Count of Date).

Using VBA

Assuming that Items and Date are in worksheet "Sheet1" starting in cell A1, the pivot table is created in a worksheet "Sheet2" starting in cell A3 in the active workbook.

Sub createPivotTableReport()

  Set wsData = ActiveWorkbook.Worksheets("Sheet1")
  Set wsReport = ActiveWorkbook.Worksheets("Sheet2")

  Set rngData = wsData.UsedRange
  Set rngReport = wsReport.Range("a3")

  Set pvtCache = ActiveWorkbook.PivotCaches.Add(xlDatabase, rngData.Address(True, True, 1, True))
  Set pvtTable = pvtCache.CreatePivotTable(rngReport)

  pvtFieldsRow = Array("Items")
  pvtFieldsCol = Array("Date")

  pvtTable.AddFields pvtFieldsRow, pvtFieldsCol
  pvtTable.PivotFields("Date").Orientation = xlDataField

End Sub

Note:

In some (newer?) versions of Excel, the line

Set pvtCache = ActiveWorkbook.PivotCaches.Add(xlDatabase, rngData.Address(True, True, 1, True))

can be changed to

Set pvtCache = ActiveWorkbook.PivotCaches.Add(xlDatabase, rngData)

Or maybe my old version of Excel is just bad. I don't know.

I also posted a .vbs solution here:

http://gallery.technet.microsoft.com/ScriptCenter/en-us/dde1e5e2-f5f6-4053-84fb-b820f01f1fdf

---

How about using Excel's DCOUNTA function? Have two tables, where the top table is the criteria for the DSUM function.

EX)

Date         Items
01.09.2009   IT004

Date         Items
01.09.2009   IT004
01.09.2009   IT004
01.09.2009   IT005

So on a line below the bottom table, you'd put the DCOUNTA function:

=DCOUNTA(A3:B6,"items",A1:B2)

Always look for built-in functionality first :)