Need help in Formula

C

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

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Sort by date Sort by votes
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
chesterrae said:
It should be Today() function.
Click to expand...

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
Aladin Akyurek said:
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"})
Click to expand...


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
Aladin Akyurek said:
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"}),"")
Click to expand...


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
You must log in or register to reply here.

Similar threads

G
  • Solved
Removing duplicates rows
Replies
7
Views
357
Gcsingh90
G
J
  • Question Question
Water Bill Calculator w/ROI from Percentage Savings
Replies
1
Views
395
Jared016
J
E
  • Solved
Performance issue using a combination of dropdowns & lookups
Replies
9
Views
512
Akuini
Akuini
M
  • Solved
Looking for a non-IF formula to apply a specific formula based on a control cell contents
Replies
9
Views
672
awoohaw
awoohaw
K
  • Solved
Calculating Tiered Commission for Sales People 😁
Replies
9
Views
1K
jdellasala
J

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

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