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]

Please try not quote large chunks while replying...

Try...

{=IF(AND(C2>=EDATE(TODAY(),-12),C2<=TODAY()),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]

Please try not quote large chunks while replying...

Try...

{=IF(AND(C2>=EDATE(TODAY(),-12),C2<=TODAY()),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"}),"")}

This one is perfect! Thank you so much Aladin! You're a genius.

 

[Aladin Akyurek]

You are welcome.

 

[chesterrae]

You are welcome.

I tried using the formula on the actual report which contains a great number of rows. Unfortunately, the calculating process is taking so much time and I didn't even tried to finish it coz' I already thought it was not the appropriate way to get the output needed. I'll try posting again looking for vba code with for loops maybe and see if will work. Anyway, I thank you so much for helping me since you're the only one here who took time to think for a solution on my query.

Have a great day and God bless!

 

[Aladin Akyurek]

Are there records beyond today in your data? Today is October 10, 2018. That is, is it possible to have records tagged with dates > today?

 

[chesterrae]

Are there records beyond today in your data? Today is October 10, 2018. That is, is it possible to have records tagged with dates > today?

There won't be any records beyond today().

 

[Aladin Akyurek]

There won't be any records beyond today().

Let's define Today in Formulas | Name Manager as referring to:

=TODAY()

and A2:A17 as OrderID, B2:B17 as Account, C2:C17 as Date, and Ivec as referring to:

=ROW(OrderID)-ROW(INDEX(OrderID,1,1))+1


Let's now try the shorter formula: Control+shift+enter, and copy down...

=IF(C2>=EDATE(Today,-12),LOOKUP(SUM(IF(FREQUENCY(IF((Account=B2)*(Date>=EDATE(Today,-12)),MATCH(OrderID,OrderID,0)),Ivec),1)),{0,"";1,"Tier 3";2,"Tier 2"}),"")

Hope the performance with this will be bearable.