Compare data quickly and effectively...

Matty

Well-known Member
Joined
Feb 17, 2007
Messages
3,710
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
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,025
Office Version
  1. 365
  2. 2016
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

Well-known Member
Joined
Feb 17, 2007
Messages
3,710
Hello,

Here's some sample data (the real data consists of thousands of Rows!):
Book2
ABCDEF
1CustomerIDMaterialNumberMaterialDescriptionMonthSnapshotWeekData
2112345AppleFeb110
3112346BananaFeb115
4112347ApricotFeb15
5112348StrawberryFeb125
6212345AppleFeb115
7212346BananaFeb110
8212347ApricotFeb130
9212348StrawberryFeb15
10112345AppleFeb25
11112346BananaFeb214
12112347ApricotFeb26
13112348StrawberryFeb25
14212345AppleFeb220
15212346BananaFeb218
16212347ApricotFeb24
17212348StrawberryFeb21
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

Well-known Member
Joined
Jun 23, 2006
Messages
7,025
Office Version
  1. 365
  2. 2016
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,783
Messages
5,833,671
Members
430,222
Latest member
Nickkarl

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top