Need help in Formula

[chesterrae]

Hi All,

Could you please help me in this? Currently I have 20K+ rows of data that's why I need a formula that will return "Tier 2" and "Tier 3" in column (D) Tier.


here are the conditions:

Tier 2, if there are 2 or more unique Order ID within the last 12 months per Account Name.
Tier 3, if there are only 1 unique Order ID within the last 12 months per Account Name.

Thank you so much in advance!

 

[Aladin Akyurek]

Try to post this sample in such a form that the sample can be read directly into Excel.

 

[chesterrae]

Try to post this sample in such a form that the sample can be read directly into Excel.

Hi Sorry, thanks for reminding!

 

[chesterrae]

Please refer on this sample instead.

Excel 2007 32 bit

	A​	B​	C​	D​
1​	Order ID	Account Name	Order Date	Tier
2​	18JBP5008	Robert Downey Jr	10/1/2018​	Tier 3
3​	18JBP5008	Robert Downey Jr	10/1/2018​	Tier 3
4​	18JAK0336	Chris Evans	10/1/2018​	Tier 2
5​	18JAK0336	Chris Evans	10/1/2018​	Tier 2
6​	18JAK0335	Chris Evans	10/1/2018​	Tier 2
7​	18JAP2322	Chris Hemsworth	1/1/2017​
8​	18JAP2322	Chris Hemsworth	11/21/2017​	Tier 3
9​	18JAP2322	Chris Hemsworth	12/21/2017​	Tier 3
10​	18JAP2322	Chris Hemsworth	10/1/2018​	Tier 3
11​	18JAN1058	Scarlett Johansson	10/1/2018​	Tier 3
12​	18JDM5411	Jeremy Renner	8/2/2017​
13​	18JDM5411	Samuel Jackson	8/10/2017​
14​	18JDM5412	Samuel Jackson	10/2/2018​	Tier 3
15​	18JBM3812	Mark Ruffalo	10/2/2018​	Tier 2
16​	18JBM3813	Mark Ruffalo	10/3/2018​	Tier 2
17​	18JBM3814	Mark Ruffalo	10/3/2018​	Tier 2

Sheet: Sheet1

 

[Aladin Akyurek]

Great, thanks. How do we define "last 12 months" -- 12 months back from today until today or are we going to use max current date instead of today?

 

[chesterrae]

Great, thanks. How do we define "last 12 months" -- 12 months back from today until today or are we going to use max current date instead of today?

It should be Today() function.

 

[Aladin Akyurek]

It should be Today() function.

In D2 control=shift+enter, not just enter, and copy down:

=LOOKUP(SUM(IF(FREQUENCY(IF($A$2:$A$17<>"",IF($B$2:$B$17=B2,IF($C$2:$C$17>=EDATE(TODAY(),-12),IF($C$2:$C$17<=TODAY(),MATCH($A$2:$A$17,$A$2:$A$17,0))))),ROW($A$2:$A$17)-ROW($A$2)+1),1)),{0,"";1,"Tier 3";2,"Tier 2"})

 

[chesterrae]

In D2 control=shift+enter, not just enter, and copy down:

=LOOKUP(SUM(IF(FREQUENCY(IF($A$2:$A$17<>"",IF($B$2:$B$17=B2,IF($C$2:$C$17>=EDATE(TODAY(),-12),IF($C$2:$C$17<=TODAY(),MATCH($A$2:$A$17,$A$2:$A$17,0))))),ROW($A$2:$A$17)-ROW($A$2)+1),1)),{0,"";1,"Tier 3";2,"Tier 2"})

I noticed Chris Hemsworth and Samuel Jackson was been populated as Tier 3 which is incorrect. They should be blank since their Order Date was 1/12017 and 8/10/2017 which is not less than 12months from today.

Excel 2007 32 bit

	A​	B​	C​	D​	E​
1​	Order ID	Account Name	Order Date		Tier (Should be the result)
2​	18JBP5008	Robert Downey Jr	10/1/2018​	Tier 3	Tier 3
3​	18JBP5008	Robert Downey Jr	10/1/2018​	Tier 3	Tier 3
4​	18JAK0336	Chris Evans	10/1/2018​	Tier 2	Tier 2
5​	18JAK0336	Chris Evans	10/1/2018​	Tier 2	Tier 2
6​	18JAK0335	Chris Evans	10/1/2018​	Tier 2	Tier 2
7​	18JAP2322	Chris Hemsworth	1/1/2017​	Tier 3
8​	18JAP2322	Chris Hemsworth	11/21/2017​	Tier 3	Tier 3
9​	18JAP2322	Chris Hemsworth	12/21/2017​	Tier 3	Tier 3
10​	18JAP2322	Chris Hemsworth	10/1/2018​	Tier 3	Tier 3
11​	18JAN1058	Scarlett Johansson	10/1/2018​	Tier 3	Tier 3
12​	18JDM5411	Jeremy Renner	8/2/2017​
13​	18JDM5411	Samuel Jackson	8/10/2017​	Tier 3
14​	18JDM5412	Samuel Jackson	10/2/2018​	Tier 3	Tier 3
15​	18JBM3812	Mark Ruffalo	10/2/2018​	Tier 2	Tier 2
16​	18JBM3813	Mark Ruffalo	10/3/2018​	Tier 2	Tier 2
17​	18JBM3814	Mark Ruffalo	10/3/2018​	Tier 2	Tier 2

Sheet: Sheet1

 

[Aladin Akyurek]

In D2 control+shift+enter and copy down:

=IF(AND(C2>=EDATE(TODAY(),-12),C2<=TODAY()),LOOKUP(SUM(IF(FREQUENCY(IF($A$2:$A$17<>"",IF($B$2:$B$17=B2,MATCH($A$2:$A$17,$A$2:$A$17,0))),ROW($A$2:$A$17)-ROW($A$2)+1),1)),{0,"";1,"Tier 3";2,"Tier 2"}),"")

 

[chesterrae]

In D2 control+shift+enter and copy down:

=IF(AND(C2>=EDATE(TODAY(),-12),C2<=TODAY()),LOOKUP(SUM(IF(FREQUENCY(IF($A$2:$A$17<>"",IF($B$2:$B$17=B2,MATCH($A$2:$A$17,$A$2:$A$17,0))),ROW($A$2:$A$17)-ROW($A$2)+1),1)),{0,"";1,"Tier 3";2,"Tier 2"}),"")

Just one incorrect, but it's almost!

Excel 2007 32 bit

	A​	B​	C​	D​	E​
1​	Order ID	Account Name	Order Date		Tier (Should be the result)
2​	18JBP5008	Robert Downey Jr	10/1/2018​	Tier 3	Tier 3
3​	18JBP5008	Robert Downey Jr	10/1/2018​	Tier 3	Tier 3
4​	18JAK0336	Chris Evans	10/1/2018​	Tier 2	Tier 2
5​	18JAK0336	Chris Evans	10/1/2018​	Tier 2	Tier 2
6​	18JAK0335	Chris Evans	10/1/2018​	Tier 2	Tier 2
7​	18JAP2322	Chris Hemsworth	1/1/2017​
8​	18JAP2322	Chris Hemsworth	11/21/2017​	Tier 3	Tier 3
9​	18JAP2322	Chris Hemsworth	12/21/2017​	Tier 3	Tier 3
10​	18JAP2322	Chris Hemsworth	10/1/2018​	Tier 3	Tier 3
11​	18JAN1058	Scarlett Johansson	10/1/2018​	Tier 3	Tier 3
12​	18JDM5411	Jeremy Renner	8/2/2017​
13​	18JDM5411	Samuel Jackson	8/10/2017​
14​	18JDM5412	Samuel Jackson	10/2/2018​	Tier 2	Tier 3
15​	18JBM3812	Mark Ruffalo	10/2/2018​	Tier 2	Tier 2
16​	18JBM3813	Mark Ruffalo	10/3/2018​	Tier 2	Tier 2
17​	18JBM3814	Mark Ruffalo	10/3/2018​	Tier 2	Tier 2

Sheet: Sheet1