Compare data quickly and effectively...

[Matty]

Dear All,

Since joining this forum earlier this year, I have picked up many tips and tricks and I thank everyone who has helped me in the past.

Now to my current quandary...

I am currently in the process of designing a report to compare demand requirements for various materials, for various customers, from one week to the next.

I want the report to be able to quickly establish against which materials the largest changes have occurred, and whom (i.e. which customer) is responsible for them.

I have so far gone down the Pivot Table/Pivot Chart route (which is the way I want to go), but I am struggling to present the data in a simple, user-friendly format.

The reason for my post is just to see whether anyone can offer me some ideas as to how I can do this.

All suggestions welcome!

Kind regards,

Matty

 

[dave3009]

Hi Matty

Any chance of a shot of you data so we can better understand the full quandry. Ideally some kind of results might be useful too

Dave

 

[Matty]

Hello,

Here's some sample data (the real data consists of thousands of Rows!):

Book2
	A	B	C	D	E	F
1	CustomerID	MaterialNumber	MaterialDescription	Month	SnapshotWeek	Data
2	1	12345	Apple	Feb	1	10
3	1	12346	Banana	Feb	1	15
4	1	12347	Apricot	Feb	1	5
5	1	12348	Strawberry	Feb	1	25
6	2	12345	Apple	Feb	1	15
7	2	12346	Banana	Feb	1	10
8	2	12347	Apricot	Feb	1	30
9	2	12348	Strawberry	Feb	1	5
10	1	12345	Apple	Feb	2	5
11	1	12346	Banana	Feb	2	14
12	1	12347	Apricot	Feb	2	6
13	1	12348	Strawberry	Feb	2	5
14	2	12345	Apple	Feb	2	20
15	2	12346	Banana	Feb	2	18
16	2	12347	Apricot	Feb	2	4
17	2	12348	Strawberry	Feb	2	1
Sheet1

I am not interested in displaying the data in the report as it is; what I want to display are the differences, biggest first, between Snapshot Week 1 and Snapshot Week 2.

Hope this helps to understand what I am trying to do.

Thanks,

Matty

 

[dave3009]

So something like

=SUMPRODUCT(--($A$2:$A$3000=CustID),--($C$2:$C$3000="Apples"), MAX($F$2:$F$3000))

Try it and let me know


KR


Dave