Excel MVP needed! Please help with an (Aggregate) RANK

[collegeitdept]

I have a sample workbook (attached by link) that has 5 worksheets of raw data. Column A contains dates (9/3/2013 - 10/24/2013). column headers are categories/variables (ie: sales, units, etc...)

I need a formula that will will give the me the rank for a date (10/24/2013) of of all the aggregate "sales" columns from the 5 worksheets please???


https://dl.dropboxusercontent.com/u/15717201/Excel Question.xlsx

(Caveats... It cannot be broken down into 2 steps... i can't add columns)


An Excel MVP is needed please.

 

[Marcelo Branco]

Harry,

Since it is acceptable the intermediary step.(total table) the OP can chose between the two solutions - macro or formulas.
Both seem to work fine.

I think we did a good job!

Cheers

M.

 

[Harry Flashman]

Harry,

Since it is acceptable the intermediary step.(total table) the OP can chose between the two solutions - macro or formulas.
Both seem to work fine.

I think we did a good job!

Cheers

M.

I think so too. I really don't think it can be done in a single step (or even if there was a way - why would you bother??). If the OP is concerned about presentation then he can sum the values on a hidden sheet, leaving the visible summary page to display just the rank.

And of course a Pivot Table would a have been the most straightforward solution but that would have required reorganizing the data, something the OP was adamant must not happen.

 

[Marcelo Branco]

I think so too. I really don't think it can be done in a single step (or even if there was a way - why would you bother??). If the OP is concerned about presentation then he can sum the values on a hidden sheet, leaving the visible summary page to display just the rank.

And of course a Pivot Table would a have been the most straightforward solution but that would have required reorganizing the data, something the OP was adamant must not happen.

Harry,

Is possible to create easily a Pivot Table without changing the data setup using the Pivot Wizard (Alt+D+P in Excel 2007+)

Take a look at
http://www.contextures.com/xlPivot08.html

(Using your file)


[TABLE="class: grid"]
<TBODY>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

Value​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD][/TD]
[TD]

Sales​

[/TD]
[TD]

Units​

[/TD]
[TD]

Palettes​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

06/09/2013​

[/TD]
[TD]

83​

[/TD]
[TD]

124​

[/TD]
[TD]

33​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

12/09/2013​

[/TD]
[TD]

44​

[/TD]
[TD]

80​

[/TD]
[TD]

17​

[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]

13/09/2013​

[/TD]
[TD]

13​

[/TD]
[TD]

36​

[/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]

19/09/2013​

[/TD]
[TD]

22​

[/TD]
[TD]

60​

[/TD]
[TD]

16​

[/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]

19/09/2013​

[/TD]
[TD]

11​

[/TD]
[TD]

52​

[/TD]
[TD]

14​

[/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]

21/09/2013​

[/TD]
[TD]

30​

[/TD]
[TD]

70​

[/TD]
[TD]

18​

[/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD]

24/09/2013​

[/TD]
[TD]

81​

[/TD]
[TD]

238​

[/TD]
[TD]

29​

[/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD]

24/09/2013​

[/TD]
[TD]

20​

[/TD]
[TD]

72​

[/TD]
[TD]

15​

[/TD]
[/TR]
[TR]
[TD]

13​

[/TD]
[TD]

24/09/2013​

[/TD]
[TD]

13​

[/TD]
[TD]

88​

[/TD]
[TD]

12​

[/TD]
[/TR]
[TR]
[TD]

14​

[/TD]
[TD]

24/09/2013​

[/TD]
[TD]

9​

[/TD]
[TD]

29​

[/TD]
[TD]

17​

[/TD]
[/TR]
[TR]
[TD]

15​

[/TD]
[TD]

24/09/2013​

[/TD]
[TD]

32​

[/TD]
[TD]

36​

[/TD]
[TD]

4​

[/TD]
[/TR]
[TR]
[TD]

16​

[/TD]
[TD]

24/09/2013​

[/TD]
[TD]

6​

[/TD]
[TD]

29​

[/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD]

17​

[/TD]
[TD]

24/09/2013​

[/TD]
[TD]

14​

[/TD]
[TD]

19​

[/TD]
[TD]

5​

[/TD]
[/TR]
[TR]
[TD]

18​

[/TD]
[TD]

24/09/2013​

[/TD]
[TD]

5​

[/TD]
[TD]

10​

[/TD]
[TD]

5​

[/TD]
[/TR]
[TR]
[TD]

19​

[/TD]
[TD]

24/09/2013​

[/TD]
[TD]

28​

[/TD]
[TD]

75​

[/TD]
[TD]

5​

[/TD]
[/TR]
[TR]
[TD]

20​

[/TD]
[TD]

24/09/2013​

[/TD]
[TD]

2​

[/TD]
[TD]

5​

[/TD]
[TD]

4​

[/TD]
[/TR]
[TR]
[TD]

21​

[/TD]
[TD]

24/09/2013​

[/TD]
[TD]

13​

[/TD]
[TD]

24​

[/TD]
[TD]

6​

[/TD]
[/TR]
[TR]
[TD]

22​

[/TD]
[TD]

25/09/2013​

[/TD]
[TD]

13​

[/TD]
[TD]

29​

[/TD]
[TD]

11​

[/TD]
[/TR]
[TR]
[TD]

23​

[/TD]
[TD]

26/09/2013​

[/TD]
[TD]

32​

[/TD]
[TD]

68​

[/TD]
[TD]

19​

[/TD]
[/TR]
[TR]
[TD]

24​

[/TD]
[TD]

26/09/2013​

[/TD]
[TD]

50​

[/TD]
[TD]

84​

[/TD]
[TD]

13​

[/TD]
[/TR]
[TR]
[TD]

25​

[/TD]
[TD]

27/09/2013​

[/TD]
[TD]

56​

[/TD]
[TD]

107​

[/TD]
[TD]

35​

[/TD]
[/TR]
[TR]
[TD]

26​

[/TD]
[TD]

27/09/2013​

[/TD]
[TD]

16​

[/TD]
[TD]

39​

[/TD]
[TD]

32​

[/TD]
[/TR]
[TR]
[TD]

27​

[/TD]
[TD]

27/09/2013​

[/TD]
[TD]

33​

[/TD]
[TD]

56​

[/TD]
[TD]

12​

[/TD]
[/TR]
[TR]
[TD]

28​

[/TD]
[TD]

28/09/2013​

[/TD]
[TD]

12​

[/TD]
[TD]

19​

[/TD]
[TD]

24​

[/TD]
[/TR]
[TR]
[TD]

29​

[/TD]
[TD]

29/09/2013​

[/TD]
[TD]

40​

[/TD]
[TD]

107​

[/TD]
[TD]

12​

[/TD]
[/TR]
</TBODY>[/TABLE]


IMPORTANT: select only columns B-E on each sheet (do NOT select column A)

M.

 

[Harry Flashman]

Harry,

Is possible to create easily a Pivot Table without changing the data setup using the Pivot Wizard (Alt+D+P in Excel 2007+)

Take a look at
http://www.contextures.com/xlPivot08.html


M.

Thank you Marcelo, I have learned something new.

Yes you're right does the job easily - and it can sum and rank the data in a single step. I have never used multiple consolidation ranges for their intended purpose - I sometimes use them to turn a matrix into a flat table (so that I may then use a pivot table) but that is all. I don't know why but a long time ago when I first experimented with them I could never get them to work in a satisfactory way and hence never bothered with them again, but in this case with the sample data - it works perfectly.

I have a feeling that the OPs actual data might not be so consistent. In the other thread I think he said something about there being different numbers of columns on each worksheet. If it were not for that then a multiple consolidation pivot table could be the idea solution.

 

[collegeitdept]

Hi You're right... I can't guarantee consistency across sheets. but column headers are the same.

Thank you Marcelo, I have learned something new.

Yes you're right does the job easily - and it can sum and rank the data in a single step. I have never used multiple consolidation ranges for their intended purpose - I sometimes use them to turn a matrix into a flat table (so that I may then use a pivot table) but that is all. I don't know why but a long time ago when I first experimented with them I could never get them to work in a satisfactory way and hence never bothered with them again, but in this case with the sample data - it works perfectly.

I have a feeling that the OPs actual data might not be so consistent. In the other thread I think he said something about there being different numbers of columns on each worksheet. If it were not for that then a multiple consolidation pivot table could be the idea solution.

 

[collegeitdept]

Thank you - you guys!


I just got to work. And I will try your formulas... Unfortunately we're not allowed to use macros due to security concern.

I will keep you guys posted!

Harry,

Since it is acceptable the intermediary step.(total table) the OP can chose between the two solutions - macro or formulas.
Both seem to work fine.

I think we did a good job!

Cheers

M.