Need help in Formula

chesterrae

Board Regular
Joined
Dec 23, 2015
Messages
51
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.

25i947t.jpg



Thank you so much in advance!
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Please refer on this sample instead.

Excel 2007 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A
[/th][th]
B
[/th][th]
C
[/th][th]
D
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1
[/td][td]Order ID[/td][td]Account Name[/td][td]Order Date[/td][td]Tier[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2
[/td][td]18JBP5008[/td][td]Robert Downey Jr[/td][td]
10/1/2018​
[/td][td]Tier 3[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3
[/td][td]18JBP5008[/td][td]Robert Downey Jr[/td][td]
10/1/2018​
[/td][td]Tier 3[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4
[/td][td]18JAK0336[/td][td]Chris Evans[/td][td]
10/1/2018​
[/td][td]Tier 2[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5
[/td][td]18JAK0336[/td][td]Chris Evans[/td][td]
10/1/2018​
[/td][td]Tier 2[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6
[/td][td]18JAK0335[/td][td]Chris Evans[/td][td]
10/1/2018​
[/td][td]Tier 2[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7
[/td][td]18JAP2322[/td][td]Chris Hemsworth[/td][td]
1/1/2017​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
8
[/td][td]18JAP2322[/td][td]Chris Hemsworth[/td][td]
11/21/2017​
[/td][td]Tier 3[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
9
[/td][td]18JAP2322[/td][td]Chris Hemsworth[/td][td]
12/21/2017​
[/td][td]Tier 3[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
10
[/td][td]18JAP2322[/td][td]Chris Hemsworth[/td][td]
10/1/2018​
[/td][td]Tier 3[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
11
[/td][td]18JAN1058[/td][td]Scarlett Johansson[/td][td]
10/1/2018​
[/td][td]Tier 3[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
12
[/td][td]18JDM5411[/td][td]Jeremy Renner[/td][td]
8/2/2017​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
13
[/td][td]18JDM5411[/td][td]Samuel Jackson[/td][td]
8/10/2017​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
14
[/td][td]18JDM5412[/td][td]Samuel Jackson[/td][td]
10/2/2018​
[/td][td]Tier 3[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
15
[/td][td]18JBM3812[/td][td]Mark Ruffalo[/td][td]
10/2/2018​
[/td][td]Tier 2[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
16
[/td][td]18JBM3813[/td][td]Mark Ruffalo[/td][td]
10/3/2018​
[/td][td]Tier 2[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
17
[/td][td]18JBM3814[/td][td]Mark Ruffalo[/td][td]
10/3/2018​
[/td][td]Tier 2[/td][/tr]
[/table]
[Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]
 
Upvote 0
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?
 
Upvote 0
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"})
 
Upvote 0
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
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A
[/th][th]
B
[/th][th]
C
[/th][th]
D
[/th][th]
E
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1
[/td][td]Order ID[/td][td]Account Name[/td][td]Order Date[/td][td][/td][td]Tier (Should be the result)[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2
[/td][td]18JBP5008[/td][td]Robert Downey Jr[/td][td]
10/1/2018​
[/td][td]Tier 3[/td][td]Tier 3[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3
[/td][td]18JBP5008[/td][td]Robert Downey Jr[/td][td]
10/1/2018​
[/td][td]Tier 3[/td][td]Tier 3[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4
[/td][td]18JAK0336[/td][td]Chris Evans[/td][td]
10/1/2018​
[/td][td]Tier 2[/td][td]Tier 2[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5
[/td][td]18JAK0336[/td][td]Chris Evans[/td][td]
10/1/2018​
[/td][td]Tier 2[/td][td]Tier 2[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6
[/td][td]18JAK0335[/td][td]Chris Evans[/td][td]
10/1/2018​
[/td][td]Tier 2[/td][td]Tier 2[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7
[/td][td]18JAP2322[/td][td]Chris Hemsworth[/td][td]
1/1/2017​
[/td][td]Tier 3[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
8
[/td][td]18JAP2322[/td][td]Chris Hemsworth[/td][td]
11/21/2017​
[/td][td]Tier 3[/td][td]Tier 3[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
9
[/td][td]18JAP2322[/td][td]Chris Hemsworth[/td][td]
12/21/2017​
[/td][td]Tier 3[/td][td]Tier 3[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
10
[/td][td]18JAP2322[/td][td]Chris Hemsworth[/td][td]
10/1/2018​
[/td][td]Tier 3[/td][td]Tier 3[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
11
[/td][td]18JAN1058[/td][td]Scarlett Johansson[/td][td]
10/1/2018​
[/td][td]Tier 3[/td][td]Tier 3[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
12
[/td][td]18JDM5411[/td][td]Jeremy Renner[/td][td]
8/2/2017​
[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
13
[/td][td]18JDM5411[/td][td]Samuel Jackson[/td][td]
8/10/2017​
[/td][td]Tier 3[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
14
[/td][td]18JDM5412[/td][td]Samuel Jackson[/td][td]
10/2/2018​
[/td][td]Tier 3[/td][td]Tier 3[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
15
[/td][td]18JBM3812[/td][td]Mark Ruffalo[/td][td]
10/2/2018​
[/td][td]Tier 2[/td][td]Tier 2[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
16
[/td][td]18JBM3813[/td][td]Mark Ruffalo[/td][td]
10/3/2018​
[/td][td]Tier 2[/td][td]Tier 2[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
17
[/td][td]18JBM3814[/td][td]Mark Ruffalo[/td][td]
10/3/2018​
[/td][td]Tier 2[/td][td]Tier 2[/td][/tr]
[/table]
[Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]
 
Upvote 0
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"}),"")
 
Upvote 0
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
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A
[/th][th]
B
[/th][th]
C
[/th][th]
D
[/th][th]
E
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1
[/td][td]Order ID[/td][td]Account Name[/td][td]Order Date[/td][td][/td][td]Tier (Should be the result)[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2
[/td][td]18JBP5008[/td][td]Robert Downey Jr[/td][td]
10/1/2018​
[/td][td]Tier 3[/td][td]Tier 3[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3
[/td][td]18JBP5008[/td][td]Robert Downey Jr[/td][td]
10/1/2018​
[/td][td]Tier 3[/td][td]Tier 3[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4
[/td][td]18JAK0336[/td][td]Chris Evans[/td][td]
10/1/2018​
[/td][td]Tier 2[/td][td]Tier 2[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5
[/td][td]18JAK0336[/td][td]Chris Evans[/td][td]
10/1/2018​
[/td][td]Tier 2[/td][td]Tier 2[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6
[/td][td]18JAK0335[/td][td]Chris Evans[/td][td]
10/1/2018​
[/td][td]Tier 2[/td][td]Tier 2[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7
[/td][td]18JAP2322[/td][td]Chris Hemsworth[/td][td]
1/1/2017​
[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
8
[/td][td]18JAP2322[/td][td]Chris Hemsworth[/td][td]
11/21/2017​
[/td][td]Tier 3[/td][td]Tier 3[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
9
[/td][td]18JAP2322[/td][td]Chris Hemsworth[/td][td]
12/21/2017​
[/td][td]Tier 3[/td][td]Tier 3[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
10
[/td][td]18JAP2322[/td][td]Chris Hemsworth[/td][td]
10/1/2018​
[/td][td]Tier 3[/td][td]Tier 3[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
11
[/td][td]18JAN1058[/td][td]Scarlett Johansson[/td][td]
10/1/2018​
[/td][td]Tier 3[/td][td]Tier 3[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
12
[/td][td]18JDM5411[/td][td]Jeremy Renner[/td][td]
8/2/2017​
[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
13
[/td][td]18JDM5411[/td][td]Samuel Jackson[/td][td]
8/10/2017​
[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
14
[/td][td=bgcolor:#E6B9B8]18JDM5412[/td][td=bgcolor:#E6B9B8]Samuel Jackson[/td][td=bgcolor:#E6B9B8]
10/2/2018​
[/td][td=bgcolor:#E6B9B8]Tier 2[/td][td=bgcolor:#E6B9B8]Tier 3[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
15
[/td][td]18JBM3812[/td][td]Mark Ruffalo[/td][td]
10/2/2018​
[/td][td]Tier 2[/td][td]Tier 2[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
16
[/td][td]18JBM3813[/td][td]Mark Ruffalo[/td][td]
10/3/2018​
[/td][td]Tier 2[/td][td]Tier 2[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
17
[/td][td]18JBM3814[/td][td]Mark Ruffalo[/td][td]
10/3/2018​
[/td][td]Tier 2[/td][td]Tier 2[/td][/tr]
[/table]
[Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]
 
Upvote 0

Forum statistics

Threads
1,223,888
Messages
6,175,213
Members
452,618
Latest member
Tam84

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