dear all,
i have a question here.
Attachment:
Exmaple.xls ( Unsorted & Sorted worksheets )
Explanation of worksheets:
1. Unsorted worksheet - contains the raw data, which the following heading:
a. Department - (example: Marketing, Finance, Acccounts, Warehouse etc etc)
b. MonthYear of Purchase - MMYYYY (example: 012007)
c. Purchase Category - (example: miscelleneous, food, repair, welfare etc)
d. Purchase amount - (example: $123.45)
2. Sorted worksheet - contains the final outcome of my sorting
Task:
I need to provide a table, where it would give me an idea of how many different purchase category did the department commit in a particular month and year.
what i have tried:
I used pivot tables, and the count. But i realise it must be distinct values, meaning, each pruchase must be different in order to use the count function meaningfully in pivot tables.
Because i would have an instance where in the month of 022007, marketing dept onli purchase 1 category. How can i do it?
In SQL... it sounds like:
Select distinct ( Product Category )
From Unsorted
Where Month = Month
And Dept = Dept.
Can someone help?
Thanks..