Top 10 Values based on couple of conditions

H

harinsh

Active Member
Joined
Feb 7, 2012
Messages
273
I have source data which copied as values....I am trying to work on some top 10 by values, by region and oldest date....for example assume I have data in column A to D ...A column contains region and B column contains Customer and C column contains Date and D column contains values.....

Looking for some formula to get the Top 10 values by region and oldest to newest date and value from largest to smallest order (top first and low last).

If you could suggest me this it would help me....attached some sample data below.

APDina
APCynthia
APTonya
APArchie
APHironobu
APAndrea
APRyan
APTracinda
APEwelina
APIrina
APNicolas
APLeslie
CAAbdullahi
CASunita
CADebra
CAKonstanti
CAMichael
CADavid
CAJohn
CADante
CAViktor
CANikolay
CAAndra
CADenise
EMWendy
EMSang
EMAbraham
EMMeghan
EMSarah B
EMThanh
EMTiara
EMNa
EMHillary
EMSvitlana
EMChristien
EMSvitlana Q
NADamir
NAJanita
NATrevor
NARalph
NADaniel
NADenis
NAFaye
NATony
NADesiree
NASarah
NAJori
NAChandy

<colgroup><col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:2889;width:59pt" width="79"> <col style="mso-width-source:userset;mso-width-alt:2925;width:60pt" width="80"> <col style="mso-width-source:userset;mso-width-alt:2742;width:56pt" width="75"> </colgroup><tbody>
[TD="class: xl65, width: 64"]Region[/TD]
[TD="class: xl65, width: 79"]Customer[/TD]
[TD="class: xl65, width: 80"]Date[/TD]
[TD="class: xl65, width: 75"]Values[/TD]

[TD="class: xl64"]1-Apr-15[/TD]
[TD="class: xl63, align: right"]$2,242.00 [/TD]

[TD="class: xl64"]1-Apr-15[/TD]
[TD="class: xl63, align: right"]$1,873.00 [/TD]

[TD="class: xl64"]1-Apr-15[/TD]
[TD="class: xl63, align: right"]$1,868.00 [/TD]

[TD="class: xl64"]1-Apr-15[/TD]
[TD="class: xl63, align: right"]$1,479.00 [/TD]

[TD="class: xl64"]1-May-15[/TD]
[TD="class: xl63, align: right"]$1,321.00 [/TD]

[TD="class: xl64"]1-Jun-15[/TD]
[TD="class: xl63, align: right"]$2,165.00 [/TD]

[TD="class: xl64"]1-Jun-15[/TD]
[TD="class: xl63, align: right"]$2,107.00 [/TD]

[TD="class: xl64"]1-Jun-15[/TD]
[TD="class: xl63, align: right"]$1,873.00 [/TD]

[TD="class: xl64"]1-Sep-15[/TD]
[TD="class: xl63, align: right"]$2,833.00 [/TD]

[TD="class: xl64"]1-Sep-15[/TD]
[TD="class: xl63, align: right"]$2,759.00 [/TD]

[TD="class: xl64"]1-Sep-15[/TD]
[TD="class: xl63, align: right"]$2,614.00 [/TD]

[TD="class: xl64"]1-Sep-15[/TD]
[TD="class: xl63, align: right"]$1,528.00 [/TD]

[TD="class: xl64"]1-Apr-15[/TD]
[TD="class: xl63, align: right"]$2,588.00 [/TD]

[TD="class: xl64"]1-Apr-15[/TD]
[TD="class: xl63, align: right"]$1,886.00 [/TD]

[TD="class: xl64"]1-Apr-15[/TD]
[TD="class: xl63, align: right"]$1,849.00 [/TD]

[TD="class: xl64"]1-Apr-15[/TD]
[TD="class: xl63, align: right"]$1,733.00 [/TD]

[TD="class: xl64"]1-Apr-15[/TD]
[TD="class: xl63, align: right"]$1,729.00 [/TD]

[TD="class: xl64"]1-Apr-15[/TD]
[TD="class: xl63, align: right"]$1,653.00 [/TD]

[TD="class: xl64"]1-Apr-15[/TD]
[TD="class: xl63, align: right"]$1,394.00 [/TD]

[TD="class: xl64"]1-May-15[/TD]
[TD="class: xl63, align: right"]$1,761.00 [/TD]

[TD="class: xl64"]1-Jun-15[/TD]
[TD="class: xl63, align: right"]$2,603.00 [/TD]

[TD="class: xl64"]1-Jun-15[/TD]
[TD="class: xl63, align: right"]$1,998.00 [/TD]

[TD="class: xl64"]1-Sep-15[/TD]
[TD="class: xl63, align: right"]$2,119.00 [/TD]

[TD="class: xl64"]1-Sep-15[/TD]
[TD="class: xl63, align: right"]$1,941.00 [/TD]

[TD="class: xl64"]1-Apr-15[/TD]
[TD="class: xl63, align: right"]$2,604.00 [/TD]

[TD="class: xl64"]1-Apr-15[/TD]
[TD="class: xl63, align: right"]$2,263.00 [/TD]

[TD="class: xl64"]1-Apr-15[/TD]
[TD="class: xl63, align: right"]$1,161.00 [/TD]

[TD="class: xl64"]1-May-15[/TD]
[TD="class: xl63, align: right"]$2,177.00 [/TD]

[TD="class: xl64"]1-May-15[/TD]
[TD="class: xl63, align: right"]$1,561.00 [/TD]

[TD="class: xl64"]1-Jun-15[/TD]
[TD="class: xl63, align: right"]$2,892.00 [/TD]

[TD="class: xl64"]1-Jun-15[/TD]
[TD="class: xl63, align: right"]$2,627.00 [/TD]

[TD="class: xl64"]1-Jun-15[/TD]
[TD="class: xl63, align: right"]$2,366.00 [/TD]

[TD="class: xl64"]1-Sep-15[/TD]
[TD="class: xl63, align: right"]$2,793.00 [/TD]

[TD="class: xl64"]1-Sep-15[/TD]
[TD="class: xl63, align: right"]$2,466.00 [/TD]

[TD="class: xl64"]1-Sep-15[/TD]
[TD="class: xl63, align: right"]$1,854.00 [/TD]

[TD="class: xl64"]1-Sep-15[/TD]
[TD="class: xl63, align: right"]$1,140.00 [/TD]

[TD="class: xl64"]1-Apr-15[/TD]
[TD="class: xl63, align: right"]$2,886.00 [/TD]

[TD="class: xl64"]1-Apr-15[/TD]
[TD="class: xl63, align: right"]$2,787.00 [/TD]

[TD="class: xl64"]1-Apr-15[/TD]
[TD="class: xl63, align: right"]$2,164.00 [/TD]

[TD="class: xl64"]1-Apr-15[/TD]
[TD="class: xl63, align: right"]$1,645.00 [/TD]

[TD="class: xl64"]1-May-15[/TD]
[TD="class: xl63, align: right"]$1,039.00 [/TD]

[TD="class: xl64"]1-Jun-15[/TD]
[TD="class: xl63, align: right"]$1,954.00 [/TD]

[TD="class: xl64"]1-Jun-15[/TD]
[TD="class: xl63, align: right"]$1,589.00 [/TD]

[TD="class: xl64"]1-Jun-15[/TD]
[TD="class: xl63, align: right"]$1,474.00 [/TD]

[TD="class: xl64"]1-Sep-15[/TD]
[TD="class: xl63, align: right"]$2,569.00 [/TD]

[TD="class: xl64"]1-Sep-15[/TD]
[TD="class: xl63, align: right"]$1,659.00 [/TD]

[TD="class: xl64"]1-Sep-15[/TD]
[TD="class: xl63, align: right"]$1,604.00 [/TD]

[TD="class: xl64"]1-Sep-15[/TD]
[TD="class: xl63, align: right"]$1,237.00
[/TD]

</tbody>


APDina
APCynthia
APTonya
APArchie
APHironobu
APAndrea
APRyan
APTracinda
APEwelina

<colgroup><col style="mso-width-source:userset;mso-width-alt:1828;width:38pt" width="50"> <col style="mso-width-source:userset;mso-width-alt:2450;width:50pt" width="67"> <col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:2523;width:52pt" width="69"> </colgroup><tbody>
[TD="class: xl67, width: 250, colspan: 4"]Output for one region and similariy need for all the regions in different cells[/TD]

[TD="class: xl68"]Region[/TD]
[TD="class: xl68"]Customer[/TD]
[TD="class: xl68"]Date[/TD]
[TD="class: xl68"]Values[/TD]

[TD="class: xl66"]1-Apr-15[/TD]
[TD="class: xl65, align: right"]$2,242.00 [/TD]

[TD="class: xl66"]1-Apr-15[/TD]
[TD="class: xl65, align: right"]$1,873.00 [/TD]

[TD="class: xl66"]1-Apr-15[/TD]
[TD="class: xl65, align: right"]$1,868.00 [/TD]

[TD="class: xl66"]1-Apr-15[/TD]
[TD="class: xl65, align: right"]$1,479.00 [/TD]

[TD="class: xl66"]1-May-15[/TD]
[TD="class: xl65, align: right"]$1,321.00 [/TD]

[TD="class: xl66"]1-Jun-15[/TD]
[TD="class: xl65, align: right"]$2,165.00 [/TD]

[TD="class: xl66"]1-Jun-15[/TD]
[TD="class: xl65, align: right"]$2,107.00 [/TD]

[TD="class: xl66"]1-Jun-15[/TD]
[TD="class: xl65, align: right"]$1,873.00 [/TD]

[TD="class: xl66"]1-Sep-15[/TD]
[TD="class: xl65, align: right"]$2,833.00
[/TD]

</tbody>


Thank you,
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Sort by date Sort by votes
See if these, copied down, give you what you want.

Excel Workbook
ABCD
1RegionCustomerDateValues
2APDina1-Apr-152,242.00
3APCynthia1-Apr-151,873.00
4APTonya1-Apr-151,868.00
5APArchie1-Apr-151,479.00
6APHironobu1-May-151,321.00
7APAndrea1-Jun-152,165.00
8APRyan1-Jun-152,107.00
9APTracinda1-Jun-151,873.00
10APEwelina1-Sep-152,833.00
11APIrina1-Sep-152,759.00
12APNicolas1-Sep-152,614.00
13APLeslie1-Sep-151,528.00
14CAAbdullahi1-Apr-152,588.00
15CASunita1-Apr-151,886.00
16CADebra1-Apr-151,849.00
17CAKonstanti1-Apr-151,733.00
18CAMichael1-Apr-151,729.00
19CADavid1-Apr-151,653.00
20CAJohn1-Apr-151,394.00
21CADante1-May-151,761.00
22CAViktor1-Jun-152,603.00
23CANikolay1-Jun-151,998.00
24CAAndra1-Sep-152,119.00
25CADenise1-Sep-151,941.00
26EMWendy1-Apr-152,604.00
27EMSang1-Apr-152,263.00
28EMAbraham1-Apr-151,161.00
29EMMeghan1-May-152,177.00
30EMSarah B1-May-151,561.00
31EMThanh1-Jun-152,892.00
32EMTiara1-Jun-152,627.00
33EMNa1-Jun-152,366.00
34EMHillary1-Sep-152,793.00
35EMSvitlana1-Sep-152,466.00
36EMChristien1-Sep-151,854.00
37EMSvitlana Q1-Sep-151,140.00
38NADamir1-Apr-152,886.00
39NAJanita1-Apr-152,787.00
40NATrevor1-Apr-152,164.00
41NARalph1-Apr-151,645.00
42NADaniel1-May-151,039.00
43NADenis1-Jun-151,954.00
44NAFaye1-Jun-151,589.00
45NATony1-Jun-151,474.00
46NADesiree1-Sep-152,569.00
47NASarah1-Sep-151,659.00
48NAJori1-Sep-151,604.00
49NAChandy1-Sep-151,237.00
50
51
52RegionCustomerDateValues
53APDina1-Apr-152,242.00
54APCynthia1-Apr-151,873.00
55APTonya1-Apr-151,868.00
56APArchie1-Apr-151,479.00
57APHironobu1-May-151,321.00
58APAndrea1-Jun-152,165.00
59APRyan1-Jun-152,107.00
60APTracinda1-Jun-151,873.00
61APEwelina1-Sep-152,833.00
62APIrina1-Sep-152,759.00
Top 10
 
Upvote 0
Hey Peter, WoW formulas ....it works like so perfect and I need small change in the formula currently sort option goes like customer first, date second and third Values....I want to make values second so, that I will get the numbers from largest to smallest ....

I am still not sure how this formula working with lots of functions ...if you could explain it would help me for any customization later stage....Thank you very much ....
 
Upvote 0
harinsh said:
.. I need small change in the formula currently sort option goes like customer first, date second and third Values....I want to make values second so, that I will get the numbers from largest to smallest ....
Click to expand...
I don't understand what you are now asking. My formula produces the expected result that you gave in your original post. Were the expected results that you gave incorrect? If so, can you now give the expected results for that first data?

Otherwise, please give another set of sample data where my current formulas fail and give your expected results for that data.



harinsh said:
I am still not sure how this formula working with lots of functions ...if you could explain it would help me for any customization later stage....Thank you very much ....
Click to expand...
Not much point yet if my formulas are not doing what you want. ;)
In the first place, have you looked up any Help on the AGGREGATE function? If you do that it should put you well on the way yo understanding the formulas given so far.
 
Upvote 0
Here is the require ouput....only change what I mentioned ...values should be largest to smallest...


[TABLE="width: 250"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Region[/TD]
[TD]Customer[/TD]
[TD]Date[/TD]
[TD]Values[/TD]
[/TR]
[TR]
[TD]AP[/TD]
[TD]Ewelina[/TD]
[TD]1-Sep-15[/TD]
[TD="align: right"]$2,833.00[/TD]
[/TR]
[TR]
[TD]AP[/TD]
[TD]Irina[/TD]
[TD]1-Sep-15[/TD]
[TD="align: right"]$2,759.00[/TD]
[/TR]
[TR]
[TD]AP[/TD]
[TD]Nicolas[/TD]
[TD]1-Sep-15[/TD]
[TD="align: right"]$2,614.00[/TD]
[/TR]
[TR]
[TD]AP[/TD]
[TD]Dina[/TD]
[TD]1-Apr-15[/TD]
[TD="align: right"]$2,242.00[/TD]
[/TR]
[TR]
[TD]AP[/TD]
[TD]Andrea[/TD]
[TD]1-Jun-15[/TD]
[TD="align: right"]$2,165.00[/TD]
[/TR]
[TR]
[TD]AP[/TD]
[TD]Ryan[/TD]
[TD]1-Jun-15[/TD]
[TD="align: right"]$2,107.00[/TD]
[/TR]
[TR]
[TD]AP[/TD]
[TD]Cynthia[/TD]
[TD]1-Apr-15[/TD]
[TD="align: right"]$1,873.00[/TD]
[/TR]
[TR]
[TD]AP[/TD]
[TD]Tracinda[/TD]
[TD]1-Jun-15[/TD]
[TD="align: right"]$1,873.00[/TD]
[/TR]
[TR]
[TD]AP[/TD]
[TD]Tonya[/TD]
[TD]1-Apr-15[/TD]
[TD="align: right"]$1,868.00[/TD]
[/TR]
[TR]
[TD]AP[/TD]
[TD]Leslie[/TD]
[TD]1-Sep-15[/TD]
[TD="align: right"]$1,528.00[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
OK, I now understand the changed requirement, thanks.

Same layout as before:

Excel Workbook
ABCD
52RegionCustomerDateValues
53APEwelina1-Sep-152,833.00
54APIrina1-Sep-152,759.00
55APNicolas1-Sep-152,614.00
56APDina1-Apr-152,242.00
57APAndrea1-Jun-152,165.00
58APRyan1-Jun-152,107.00
59APCynthia1-Apr-151,873.00
60APTracinda1-Jun-151,873.00
61APTonya1-Apr-151,868.00
62APLeslie1-Sep-151,528.00
Top 10 (2)
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
How to replace weekend and capture the next business day
Replies
3
Views
611
Joe4
Joe4
P
  • Question Question
VBA remove decimals
Replies
5
Views
1K
Panoos64
P
L
  • Question Question
Count values between two dates (show sum per day)
Replies
2
Views
551
Llupo01
L
L
  • Question Question
find the highest value in a column and match the top 3 highest value in another column
2
Replies
11
Views
1K
Marcelo Branco
M
P
  • Question Question
Drop Down List
Replies
0
Views
476
Panoos64
P

Forum statistics

Threads
1,223,893
Messages
6,175,244
Members
452,622
Latest member
Laura_PinksBTHFT

Share this page

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
Back
Top