Top 10 Values based on couple of conditions

[harinsh]

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.

Region	Customer	Date	Values
AP	Dina	1-Apr-15	$2,242.00
AP	Cynthia	1-Apr-15	$1,873.00
AP	Tonya	1-Apr-15	$1,868.00
AP	Archie	1-Apr-15	$1,479.00
AP	Hironobu	1-May-15	$1,321.00
AP	Andrea	1-Jun-15	$2,165.00
AP	Ryan	1-Jun-15	$2,107.00
AP	Tracinda	1-Jun-15	$1,873.00
AP	Ewelina	1-Sep-15	$2,833.00
AP	Irina	1-Sep-15	$2,759.00
AP	Nicolas	1-Sep-15	$2,614.00
AP	Leslie	1-Sep-15	$1,528.00
CA	Abdullahi	1-Apr-15	$2,588.00
CA	Sunita	1-Apr-15	$1,886.00
CA	Debra	1-Apr-15	$1,849.00
CA	Konstanti	1-Apr-15	$1,733.00
CA	Michael	1-Apr-15	$1,729.00
CA	David	1-Apr-15	$1,653.00
CA	John	1-Apr-15	$1,394.00
CA	Dante	1-May-15	$1,761.00
CA	Viktor	1-Jun-15	$2,603.00
CA	Nikolay	1-Jun-15	$1,998.00
CA	Andra	1-Sep-15	$2,119.00
CA	Denise	1-Sep-15	$1,941.00
EM	Wendy	1-Apr-15	$2,604.00
EM	Sang	1-Apr-15	$2,263.00
EM	Abraham	1-Apr-15	$1,161.00
EM	Meghan	1-May-15	$2,177.00
EM	Sarah B	1-May-15	$1,561.00
EM	Thanh	1-Jun-15	$2,892.00
EM	Tiara	1-Jun-15	$2,627.00
EM	Na	1-Jun-15	$2,366.00
EM	Hillary	1-Sep-15	$2,793.00
EM	Svitlana	1-Sep-15	$2,466.00
EM	Christien	1-Sep-15	$1,854.00
EM	Svitlana Q	1-Sep-15	$1,140.00
NA	Damir	1-Apr-15	$2,886.00
NA	Janita	1-Apr-15	$2,787.00
NA	Trevor	1-Apr-15	$2,164.00
NA	Ralph	1-Apr-15	$1,645.00
NA	Daniel	1-May-15	$1,039.00
NA	Denis	1-Jun-15	$1,954.00
NA	Faye	1-Jun-15	$1,589.00
NA	Tony	1-Jun-15	$1,474.00
NA	Desiree	1-Sep-15	$2,569.00
NA	Sarah	1-Sep-15	$1,659.00
NA	Jori	1-Sep-15	$1,604.00
NA	Chandy	1-Sep-15	$1,237.00

<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>
</tbody>

Output for one region and similariy need for all the regions in different cells
Region	Customer	Date	Values
AP	Dina	1-Apr-15	$2,242.00
AP	Cynthia	1-Apr-15	$1,873.00
AP	Tonya	1-Apr-15	$1,868.00
AP	Archie	1-Apr-15	$1,479.00
AP	Hironobu	1-May-15	$1,321.00
AP	Andrea	1-Jun-15	$2,165.00
AP	Ryan	1-Jun-15	$2,107.00
AP	Tracinda	1-Jun-15	$1,873.00
AP	Ewelina	1-Sep-15	$2,833.00

<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>
</tbody>

Thank you,

 

[Peter_SSs]

See if these, copied down, give you what you want.

Excel Workbook
	A	B	C	D
1	Region	Customer	Date	Values
2	AP	Dina	1-Apr-15	2,242.00
3	AP	Cynthia	1-Apr-15	1,873.00
4	AP	Tonya	1-Apr-15	1,868.00
5	AP	Archie	1-Apr-15	1,479.00
6	AP	Hironobu	1-May-15	1,321.00
7	AP	Andrea	1-Jun-15	2,165.00
8	AP	Ryan	1-Jun-15	2,107.00
9	AP	Tracinda	1-Jun-15	1,873.00
10	AP	Ewelina	1-Sep-15	2,833.00
11	AP	Irina	1-Sep-15	2,759.00
12	AP	Nicolas	1-Sep-15	2,614.00
13	AP	Leslie	1-Sep-15	1,528.00
14	CA	Abdullahi	1-Apr-15	2,588.00
15	CA	Sunita	1-Apr-15	1,886.00
16	CA	Debra	1-Apr-15	1,849.00
17	CA	Konstanti	1-Apr-15	1,733.00
18	CA	Michael	1-Apr-15	1,729.00
19	CA	David	1-Apr-15	1,653.00
20	CA	John	1-Apr-15	1,394.00
21	CA	Dante	1-May-15	1,761.00
22	CA	Viktor	1-Jun-15	2,603.00
23	CA	Nikolay	1-Jun-15	1,998.00
24	CA	Andra	1-Sep-15	2,119.00
25	CA	Denise	1-Sep-15	1,941.00
26	EM	Wendy	1-Apr-15	2,604.00
27	EM	Sang	1-Apr-15	2,263.00
28	EM	Abraham	1-Apr-15	1,161.00
29	EM	Meghan	1-May-15	2,177.00
30	EM	Sarah B	1-May-15	1,561.00
31	EM	Thanh	1-Jun-15	2,892.00
32	EM	Tiara	1-Jun-15	2,627.00
33	EM	Na	1-Jun-15	2,366.00
34	EM	Hillary	1-Sep-15	2,793.00
35	EM	Svitlana	1-Sep-15	2,466.00
36	EM	Christien	1-Sep-15	1,854.00
37	EM	Svitlana Q	1-Sep-15	1,140.00
38	NA	Damir	1-Apr-15	2,886.00
39	NA	Janita	1-Apr-15	2,787.00
40	NA	Trevor	1-Apr-15	2,164.00
41	NA	Ralph	1-Apr-15	1,645.00
42	NA	Daniel	1-May-15	1,039.00
43	NA	Denis	1-Jun-15	1,954.00
44	NA	Faye	1-Jun-15	1,589.00
45	NA	Tony	1-Jun-15	1,474.00
46	NA	Desiree	1-Sep-15	2,569.00
47	NA	Sarah	1-Sep-15	1,659.00
48	NA	Jori	1-Sep-15	1,604.00
49	NA	Chandy	1-Sep-15	1,237.00
50
51
52	Region	Customer	Date	Values
53	AP	Dina	1-Apr-15	2,242.00
54	AP	Cynthia	1-Apr-15	1,873.00
55	AP	Tonya	1-Apr-15	1,868.00
56	AP	Archie	1-Apr-15	1,479.00
57	AP	Hironobu	1-May-15	1,321.00
58	AP	Andrea	1-Jun-15	2,165.00
59	AP	Ryan	1-Jun-15	2,107.00
60	AP	Tracinda	1-Jun-15	1,873.00
61	AP	Ewelina	1-Sep-15	2,833.00
62	AP	Irina	1-Sep-15	2,759.00
Top 10

 

[harinsh]

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 ....

 

[Peter_SSs]

.. 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 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.

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 ....

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.

 

[harinsh]

Here is the require ouput....only change what I mentioned ...values should be largest to smallest...

Region	Customer	Date	Values
AP	Ewelina	1-Sep-15	$2,833.00
AP	Irina	1-Sep-15	$2,759.00
AP	Nicolas	1-Sep-15	$2,614.00
AP	Dina	1-Apr-15	$2,242.00
AP	Andrea	1-Jun-15	$2,165.00
AP	Ryan	1-Jun-15	$2,107.00
AP	Cynthia	1-Apr-15	$1,873.00
AP	Tracinda	1-Jun-15	$1,873.00
AP	Tonya	1-Apr-15	$1,868.00
AP	Leslie	1-Sep-15	$1,528.00

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

 

[Peter_SSs]

OK, I now understand the changed requirement, thanks.

Same layout as before:

Excel Workbook
	A	B	C	D
52	Region	Customer	Date	Values
53	AP	Ewelina	1-Sep-15	2,833.00
54	AP	Irina	1-Sep-15	2,759.00
55	AP	Nicolas	1-Sep-15	2,614.00
56	AP	Dina	1-Apr-15	2,242.00
57	AP	Andrea	1-Jun-15	2,165.00
58	AP	Ryan	1-Jun-15	2,107.00
59	AP	Cynthia	1-Apr-15	1,873.00
60	AP	Tracinda	1-Jun-15	1,873.00
61	AP	Tonya	1-Apr-15	1,868.00
62	AP	Leslie	1-Sep-15	1,528.00
Top 10 (2)

 

[harinsh]

Its working fine....thank you very much

 

[Peter_SSs]

Its working fine....thank you very much

You're welcome.

 

[harinsh]

You're welcome.

Hi Peter,

I hope your doing good, the above formula helped me a lot and similar to this I have another one...there should slightly need to modify the formula but I could not succeed.

Could you please help me the formula for below tread...

https://www.mrexcel.com/forum/excel...d-date-along-required-fields.html#post5230039

Thank you,