Need to add cells from column B to create new total in column D

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Erikka T - 13 Jul 2004 18:44 GMT

This must be elementary, but I am new to VBA. I have a SQL query that returns data to my sheet that is grouped by status, and ordered by date. Here is an example of what is returned:
9/27/2003 17:00    1    Open-Propose
9/27/2003 17:00    1    Open-Review
9/27/2003 17:00    31    Open
10/4/2003 17:00    2    Open-Review
10/4/2003 17:00    38    Open
10/4/2003 17:00    4    Open-Propose

I need to create a chart so that for each given date, a total of all "Open*" is presented, because for charting purposes it doesn't matter what the status is, the overall count is what is important.

I want this returned:

9/27/2003 17:00    33
10/4/2003 17:00    44

I would like to write something that selects the sheet, and then compares the date cell to the next date cell and then total up all the numbers for that same date, and stick it in a new column so that I can chart it.

I suppose I could use a command button, but it would be better if the function could just run if the data on the sheet is changed.

Thanks!

Reply to this Message

Frank Kabel - 13 Jul 2004 18:48 GMT

Hi
a non-VBA solution: Use a pivot table for this. See:
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm

Signature

--
Regards
Frank Kabel
Frankfurt, Germany

> This must be elementary, but I am new to VBA. I have a SQL query that
> returns data to my sheet that is grouped by status, and ordered by
[quoted text clipped - 22 lines]
>
> Thanks!

Reply to this Message

CLR - 14 Jul 2004 01:05 GMT

I would use the Data > Subtotals feature..........

Vaya con Dios,
Chuck, CABGx3

> This must be elementary, but I am new to VBA. I have a SQL query that returns data to my sheet that is grouped by status, and ordered by date.

Here is an example of what is returned:

> 9/27/2003 17:00 1 Open-Propose
> 9/27/2003 17:00 1 Open-Review
[quoted text clipped - 4 lines]
>
> I need to create a chart so that for each given date, a total of all "Open*" is presented, because for charting purposes it doesn't matter what

the status is, the overall count is what is important.

> I want this returned:
>
> 9/27/2003 17:00 33
> 10/4/2003 17:00 44
>
> I would like to write something that selects the sheet, and then compares the date cell to the next date cell and then total up all the numbers for

that same date, and stick it in a new column so that I can chart it.

> I suppose I could use a command button, but it would be better if the function could just run if the data on the sheet is changed.
>
> Thanks!

Reply to this Message

Jamie Collins - 14 Jul 2004 09:49 GMT

> This must be elementary, but I am new to VBA. I have a SQL query that returns data to my sheet that is grouped by status, and ordered by date. Here is an example of what is returned:
> 9/27/2003 17:00    1    Open-Propose
[quoted text clipped - 10 lines]
> 9/27/2003 17:00    33
> 10/4/2003 17:00    44

I think this is the query you require:

SELECT
MyDateTime,
LEFT(MyStatus,4),
SUM(MyAmount) AS MyAmount
FROM
MyTable
GROUP BY
MyDateTime,
LEFT(MyStatus,4)
HAVING
LEFT(MyStatus,4)='Open'
ORDER BY MyDateTime
;

Jamie.

--

Reply to this Message