Data referencing problem

[xxxr]

Hi all, i am a beginner in the vba environment who knows very little compared to most of you. Which is why i am seeking all your wisdom and experience in solving a few problems i'm facing right now.

Firstly, a little background of my problem (as attached):

In Sheet A, I have a table which shows the Names, Type of fruits and Date Purchased.

There are 4 names and 3 Types of fruits in total

Names: Alan, Tom, Alicia, John

Types of fruits: Apples, Pears, Oranges

In Sheet B, I would like to have a table which will sum up all of each type of fruits bought by each Name across all dates. As well as the earliest date that Name bought that type of fruit

For example: How many Apples did Alan buy across all dates
How many Apples did John buy across all dates
How many Oranges did Alicia buy across all dates

I'm hoping to automate the entire process with an assigned button in Sheet A. It is clearer if you look at the file I've attached. Hope my description is clear enough for all you guys to understand. Thanks in advance!

[Bob Phillips]

What's wrong with the formulae that you already have?

[xxxr]

The formula is fine, i'm just looking to automate the entire process as per my boss's request

[georgiboy]

Could you not use a pivot table?

[xxxr]

I've tried, but he seems to want something impressive to make himself look good in front of senior management. is there anyway we can do tt via vba?

[Bob Phillips]

A pivot is hugely impressive, especially whenyou start drilling-down/up and showing different data so easily.

[georgiboy]

Looks very pretty to me, pleasing and attractive to the eye (as pretty as excel gets that is, i do have a partner!)

[Bob Phillips]

Attractive, yeah, but look at those dropdown arrows. They allow you to filter on any on the data items.

And Excel gets far mor attractive than that.

3 clicks ...

[CreganTur]

And Excel gets far mor attractive than that.

Beer goggles, bob?


xxxr,

I only dabble in Excel, but I completely agree with the others here. Why reinvent the wheel when you have an excellent tool at your disposal already? To duplicate via VBA what you can already do with a pivot chart would be a rather massive undertaking.

[xxxr]

thanks for the input lads, worked like a charm and my boss is fairly pleased with it. But i have another issue with the pivot table.

Let's say a particular Name bought Apples across 3 dates, how do i show the minimum of those dates?

[Bob Phillips]

The minimum is the first in the last because it is ordered.