Searching 2 columns for dates and text problem
 

I have two worksheets in a single workbook.

WS1

Col A contains dates; Apr-06 thru Mar-07 over a 1,000 rows

Col B is populated with 15 unique category descriptions over the 1,000 rows.
(lets say the descriptions are Cat1, Cat2, Cat3€¦Cat15)

Col C is a currency

Im looking for a VBA solution to satisfy the following€¦

For each occurrence of the current month in Col A, If true... for every
occurrence of a category match in Col B, roll-up the values in Col C and
place the SUM in Cell A1 of WS2.

Hope this makes sense.

Any ideas appreciated.
Paul

Searching 2 columns for dates and text problem
 

You can do this without code using a Pivot Table and grouping on Month.

--
Regards,
Tom Ogilvy



"Paulc" wrote:

I have two worksheets in a single workbook.

WS1

Col A contains dates; Apr-06 thru Mar-07 over a 1,000 rows

Col B is populated with 15 unique category descriptions over the 1,000 rows.
(lets say the descriptions are Cat1, Cat2, Cat3€¦Cat15)

Col C is a currency

Im looking for a VBA solution to satisfy the following€¦

For each occurrence of the current month in Col A, If true... for every
occurrence of a category match in Col B, roll-up the values in Col C and
place the SUM in Cell A1 of WS2.

Hope this makes sense.

Any ideas appreciated.
Paul

Searching 2 columns for dates and text problem
 

Tom, thanks for your response.

i did consider a pivot table. If i can get around the need for excel to
format my dates as dd/mm/yy then the pivot route would work for me. Else im
faced with individual dates as opposed to grouping by month.


"Tom Ogilvy" wrote:

You can do this without code using a Pivot Table and grouping on Month.

--
Regards,
Tom Ogilvy



"Paulc" wrote:

I have two worksheets in a single workbook.

WS1

Col A contains dates; Apr-06 thru Mar-07 over a 1,000 rows

Col B is populated with 15 unique category descriptions over the 1,000 rows.
(lets say the descriptions are Cat1, Cat2, Cat3€¦Cat15)

Col C is a currency

Im looking for a VBA solution to satisfy the following€¦

For each occurrence of the current month in Col A, If true... for every
occurrence of a category match in Col B, roll-up the values in Col C and
place the SUM in Cell A1 of WS2.

Hope this makes sense.

Any ideas appreciated.
Paul