Sort Based on Multiple Criteria

R

ravi2628

Board Regular
Joined
Dec 20, 2017
Messages
221
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi Everyone Good Morning/Afternoon/Evening.

I was looking for a Formula to sort the Data based on multiple Criteria columns

Please refer to the following Input & Output for reference.

Input Data

Book5
ABCDEFGH
1Reference NumberStatusCountrySegmentCodeSKUQTYSALE
24RegisteredLebanonOncology30000001Bortezomib 2mg0
31RegisteredLebanonOncology30000002Bortezomib 3.5mg9501
42RegisteredLebanonOncology30000004Carboplatin 150mg516
51RegisteredLebanonOncology30000005Carboplatin 450mg8001
63RegisteredLebanonOncology30000006Cisplatin 10mg01
72RegisteredLebanonOncology30000007Cisplatin 50mg1400
84RegisteredLebanonOncology30000012Cyclophosphamide 500mg0
92RegisteredLebanonOncology30000013Cyclophosphamide 1000mg12150
104RegisteredLebanonOncology30000014Dacarbazine 100mg0
112RegisteredLebanonOncology30000015Dacarbazine 200mg10250
124RegisteredLebanonOncology30000016Dacarbazine 500mg0
132RegisteredLebanonOncology30000017Doxorubicin Hcl 10mg200
142RegisteredLebanonOncology30000018Doxorubicin Hcl 50mg8400
152RegisteredLebanonOncology30000019Dactinomycin 0.5mg2656
162RegisteredLebanonOncology30000020Docetaxel 20mg200
174RegisteredLebanonCritical Care30000062Acetylcystine 1000mg0
181RegisteredLebanonCritical Care30000063Acetylcystine 400mg14001
192RegisteredLebanonCritical Care30000064Acetylcystine 200mg3000
201RegisteredLebanonCritical Care30000068Vecuronium Bromide 10mg5001
211RegisteredLebanonCritical Care30000069Vecuronium Bromide 4mg107001
222RegisteredLebanonCritical Care30000071Vancomycin Hcl 1000mg11050
234RegisteredLebanonCritical Care30000072Vancomycin Hcl 500mg0
242RegisteredLebanonCritical Care30000073Tranexamic Acid 500mg12150
254RegisteredLebanonCritical care30000076Succinylcholine Chloride Injection 500mg0
264RegisteredLebanonCritical care30000077Succinylcholine Chloride Injection 100mg0
274RegisteredLebanonCritical Care30000079Sterile Nor-Adreline Concentrate 4mg0
284RegisteredLebanonCritical Care30000080Propofol Injection 500mg0
294RegisteredLebanonCritical Care30000081Propofol Injection 200mg0
304RegisteredLebanonCritical Care30000082Propofol Injection 100mg0
314RegisteredLebanonCritical Care30000083Polymyxin B Sulphate 500000 IU0
324RegisteredLebanonCritical Care30000087Ondansetron Hydrchloride Injection 8mg0
334RegisteredLebanonCritical care30000088Ondansetron Hydrchloride Injection 4mg0
344RegisteredLebanonCritical care30000089Omeprazole Injection 40mg0
354RegisteredLebanonCritical Care30000090Neostigmine Methylsulfate 2.5mg0
Input
Cell Formulas
RangeFormula
A2:A35A2=IF(AND(B2="Registered",G2>0,H2>0),1,IF(AND(B2="Registered",G2>0,H2=0),2,IF(AND(B2="Registered",G2=0,H2>0),3,IF(AND(B2="Registered",G2=0,H2=0),4,IF(AND(B2="Under Renewal",G2>0,H2>0),5,IF(AND(B2="Under Renewal",G2>0,H2=0),6,IF(AND(B2="Under Renewal",G2=0,H2>0),7,IF(AND(B2="Under Renewal",G2=0,H2=0),8,IF(AND(B2="Fresh",G2>0,H2>0),9,IF(AND(B2="Fresh",G2>0,H2=0),10,IF(AND(B2="Fresh",G2=0,H2>0),11,IF(AND(B2="Fresh",G2=0,H2=0),12,IF(AND(B2="Not Registered",G2>0,H2>0),13,IF(AND(B2="Not Registered",G2>0,H2=0),14,IF(AND(B2="Not Registered",G2=0,H2>0),15,IF(AND(B2="Not Registered",G2=0,H2=0),16,17))))))))))))))))


Required Output:
Book5
ABCDEFGH
1Reference NumberStatusCountrySegmentCodeSKUQTYSALE
21RegisteredLebanonOncology30000002Bortezomib 3.5mg9501
31RegisteredLebanonOncology30000005Carboplatin 450mg8001
42RegisteredLebanonOncology30000013Cyclophosphamide 1000mg12150
52RegisteredLebanonOncology30000015Dacarbazine 200mg10250
62RegisteredLebanonOncology30000018Doxorubicin Hcl 50mg8400
72RegisteredLebanonOncology30000019Dactinomycin 0.5mg2656
82RegisteredLebanonOncology30000007Cisplatin 50mg1400
92RegisteredLebanonOncology30000004Carboplatin 150mg516
102RegisteredLebanonOncology30000017Doxorubicin Hcl 10mg200
112RegisteredLebanonOncology30000020Docetaxel 20mg200
123RegisteredLebanonOncology30000006Cisplatin 10mg01
134RegisteredLebanonOncology30000001Bortezomib 2mg0
144RegisteredLebanonOncology30000012Cyclophosphamide 500mg0
154RegisteredLebanonOncology30000014Dacarbazine 100mg0
164RegisteredLebanonOncology30000016Dacarbazine 500mg0
171RegisteredLebanonCritical Care30000069Vecuronium Bromide 4mg107001
181RegisteredLebanonCritical Care30000063Acetylcystine 400mg14001
191RegisteredLebanonCritical Care30000068Vecuronium Bromide 10mg5001
202RegisteredLebanonCritical Care30000073Tranexamic Acid 500mg12150
212RegisteredLebanonCritical Care30000071Vancomycin Hcl 1000mg11050
222RegisteredLebanonCritical Care30000064Acetylcystine 200mg3000
234RegisteredLebanonCritical Care30000062Acetylcystine 1000mg0
244RegisteredLebanonCritical Care30000072Vancomycin Hcl 500mg0
254RegisteredLebanonCritical care30000076Succinylcholine Chloride Injection 500mg0
264RegisteredLebanonCritical care30000077Succinylcholine Chloride Injection 100mg0
274RegisteredLebanonCritical Care30000079Sterile Nor-Adreline Concentrate 4mg0
284RegisteredLebanonCritical Care30000080Propofol Injection 500mg0
294RegisteredLebanonCritical Care30000081Propofol Injection 200mg0
304RegisteredLebanonCritical Care30000082Propofol Injection 100mg0
314RegisteredLebanonCritical Care30000083Polymyxin B Sulphate 500000 IU0
324RegisteredLebanonCritical Care30000087Ondansetron Hydrchloride Injection 8mg0
334RegisteredLebanonCritical care30000088Ondansetron Hydrchloride Injection 4mg0
344RegisteredLebanonCritical care30000089Omeprazole Injection 40mg0
354RegisteredLebanonCritical Care30000090Neostigmine Methylsulfate 2.5mg0
Output


Sort Logic

First Sort: D Column/Segment (ZtoA)
Second Sort: A Column/Reference Number (Smallest to Largest)
Third Sort: G Column/Qty(Largest to Smallest)


Please help me with the Formula
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
This will only work in 365 but see if this does what you are after:
Excel Formula: 
=SORTBY(Input!$A$2:$H$35,Input!$D$2:$D$35,-1,Input!A2:A35,1,1*Input!$G$2:$G$35,-1)
 
Upvote 0
Solution
Thanks, Alex & Fluff both the logic Work perfectly for me
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Question Question
Mapping strings in cell to value, and then keeping certain rows based on certain criterias
Replies
1
Views
427
Fluff
Fluff
W
  • Locked
  • Question Question
[VBA] Mapping strings in cell to value, and then keeping certain rows based on certain criterias
Replies
1
Views
440
Joe4
Joe4
S
  • Question Question
Unexpected and inconsistent error with ranking rows based on criteria
Replies
2
Views
361
strugglingaccountant
S
P
  • Solved
Select Case in VBA
Replies
3
Views
680
rollis13
rollis13
R
  • Solved
Extract Unique Values from Multiple Sheets
2
Replies
10
Views
1K
Zot
Z

Forum statistics

Threads
1,223,911
Messages
6,175,325
Members
452,635
Latest member
laura12345

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