Serach for numbers starting with for example 49****???

D

Daniel

Hi all,

i have a small problem. And it follows like this:

I create a new workbook, wich will contain a couple of charts based
upon data from 10 other workbooks.

I need to search two columns for 2 things. one should search for a
month, and then count all numbers in the other column, within that
month wich starts with 49!!

Example:

A b
4900 Jan
3800 Jan
4901 Feb
4911 Feb
2000 Feb

So the code should in this case display:
Jan Feb
1 2

How do I this???

Would appreciate any help, Daniel
 
A

arno

Hi Daniel,

first you need to create a table that contains the data you really need. In
your case add column c with the formula
=left(a2, 2)
which will give you 49, 38, 49....

Then you make out of your table a Pivot-Table with colum c in rows, colum b
in columns and number of records (any columns from a, b, c you like) as the
value.

you'll get a table like this:

jan feb
38 1 1
49 3 2
etc.

You can then make some finetuning:
- show in column c only what you want to see, or
- hide unwanted data in the pivot table.

regards

arno
 
Top