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
Required 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
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 | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | |||
1 | Reference Number | Status | Country | Segment | Code | SKU | QTY | SALE | ||
2 | 4 | Registered | Lebanon | Oncology | 30000001 | Bortezomib 2mg | 0 | |||
3 | 1 | Registered | Lebanon | Oncology | 30000002 | Bortezomib 3.5mg | 950 | 1 | ||
4 | 2 | Registered | Lebanon | Oncology | 30000004 | Carboplatin 150mg | 516 | |||
5 | 1 | Registered | Lebanon | Oncology | 30000005 | Carboplatin 450mg | 800 | 1 | ||
6 | 3 | Registered | Lebanon | Oncology | 30000006 | Cisplatin 10mg | 0 | 1 | ||
7 | 2 | Registered | Lebanon | Oncology | 30000007 | Cisplatin 50mg | 1400 | |||
8 | 4 | Registered | Lebanon | Oncology | 30000012 | Cyclophosphamide 500mg | 0 | |||
9 | 2 | Registered | Lebanon | Oncology | 30000013 | Cyclophosphamide 1000mg | 12150 | |||
10 | 4 | Registered | Lebanon | Oncology | 30000014 | Dacarbazine 100mg | 0 | |||
11 | 2 | Registered | Lebanon | Oncology | 30000015 | Dacarbazine 200mg | 10250 | |||
12 | 4 | Registered | Lebanon | Oncology | 30000016 | Dacarbazine 500mg | 0 | |||
13 | 2 | Registered | Lebanon | Oncology | 30000017 | Doxorubicin Hcl 10mg | 200 | |||
14 | 2 | Registered | Lebanon | Oncology | 30000018 | Doxorubicin Hcl 50mg | 8400 | |||
15 | 2 | Registered | Lebanon | Oncology | 30000019 | Dactinomycin 0.5mg | 2656 | |||
16 | 2 | Registered | Lebanon | Oncology | 30000020 | Docetaxel 20mg | 200 | |||
17 | 4 | Registered | Lebanon | Critical Care | 30000062 | Acetylcystine 1000mg | 0 | |||
18 | 1 | Registered | Lebanon | Critical Care | 30000063 | Acetylcystine 400mg | 1400 | 1 | ||
19 | 2 | Registered | Lebanon | Critical Care | 30000064 | Acetylcystine 200mg | 3000 | |||
20 | 1 | Registered | Lebanon | Critical Care | 30000068 | Vecuronium Bromide 10mg | 500 | 1 | ||
21 | 1 | Registered | Lebanon | Critical Care | 30000069 | Vecuronium Bromide 4mg | 10700 | 1 | ||
22 | 2 | Registered | Lebanon | Critical Care | 30000071 | Vancomycin Hcl 1000mg | 11050 | |||
23 | 4 | Registered | Lebanon | Critical Care | 30000072 | Vancomycin Hcl 500mg | 0 | |||
24 | 2 | Registered | Lebanon | Critical Care | 30000073 | Tranexamic Acid 500mg | 12150 | |||
25 | 4 | Registered | Lebanon | Critical care | 30000076 | Succinylcholine Chloride Injection 500mg | 0 | |||
26 | 4 | Registered | Lebanon | Critical care | 30000077 | Succinylcholine Chloride Injection 100mg | 0 | |||
27 | 4 | Registered | Lebanon | Critical Care | 30000079 | Sterile Nor-Adreline Concentrate 4mg | 0 | |||
28 | 4 | Registered | Lebanon | Critical Care | 30000080 | Propofol Injection 500mg | 0 | |||
29 | 4 | Registered | Lebanon | Critical Care | 30000081 | Propofol Injection 200mg | 0 | |||
30 | 4 | Registered | Lebanon | Critical Care | 30000082 | Propofol Injection 100mg | 0 | |||
31 | 4 | Registered | Lebanon | Critical Care | 30000083 | Polymyxin B Sulphate 500000 IU | 0 | |||
32 | 4 | Registered | Lebanon | Critical Care | 30000087 | Ondansetron Hydrchloride Injection 8mg | 0 | |||
33 | 4 | Registered | Lebanon | Critical care | 30000088 | Ondansetron Hydrchloride Injection 4mg | 0 | |||
34 | 4 | Registered | Lebanon | Critical care | 30000089 | Omeprazole Injection 40mg | 0 | |||
35 | 4 | Registered | Lebanon | Critical Care | 30000090 | Neostigmine Methylsulfate 2.5mg | 0 | |||
Input |
Cell Formulas | ||
---|---|---|
Range | Formula | |
A2:A35 | A2 | =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 | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | |||
1 | Reference Number | Status | Country | Segment | Code | SKU | QTY | SALE | ||
2 | 1 | Registered | Lebanon | Oncology | 30000002 | Bortezomib 3.5mg | 950 | 1 | ||
3 | 1 | Registered | Lebanon | Oncology | 30000005 | Carboplatin 450mg | 800 | 1 | ||
4 | 2 | Registered | Lebanon | Oncology | 30000013 | Cyclophosphamide 1000mg | 12150 | |||
5 | 2 | Registered | Lebanon | Oncology | 30000015 | Dacarbazine 200mg | 10250 | |||
6 | 2 | Registered | Lebanon | Oncology | 30000018 | Doxorubicin Hcl 50mg | 8400 | |||
7 | 2 | Registered | Lebanon | Oncology | 30000019 | Dactinomycin 0.5mg | 2656 | |||
8 | 2 | Registered | Lebanon | Oncology | 30000007 | Cisplatin 50mg | 1400 | |||
9 | 2 | Registered | Lebanon | Oncology | 30000004 | Carboplatin 150mg | 516 | |||
10 | 2 | Registered | Lebanon | Oncology | 30000017 | Doxorubicin Hcl 10mg | 200 | |||
11 | 2 | Registered | Lebanon | Oncology | 30000020 | Docetaxel 20mg | 200 | |||
12 | 3 | Registered | Lebanon | Oncology | 30000006 | Cisplatin 10mg | 0 | 1 | ||
13 | 4 | Registered | Lebanon | Oncology | 30000001 | Bortezomib 2mg | 0 | |||
14 | 4 | Registered | Lebanon | Oncology | 30000012 | Cyclophosphamide 500mg | 0 | |||
15 | 4 | Registered | Lebanon | Oncology | 30000014 | Dacarbazine 100mg | 0 | |||
16 | 4 | Registered | Lebanon | Oncology | 30000016 | Dacarbazine 500mg | 0 | |||
17 | 1 | Registered | Lebanon | Critical Care | 30000069 | Vecuronium Bromide 4mg | 10700 | 1 | ||
18 | 1 | Registered | Lebanon | Critical Care | 30000063 | Acetylcystine 400mg | 1400 | 1 | ||
19 | 1 | Registered | Lebanon | Critical Care | 30000068 | Vecuronium Bromide 10mg | 500 | 1 | ||
20 | 2 | Registered | Lebanon | Critical Care | 30000073 | Tranexamic Acid 500mg | 12150 | |||
21 | 2 | Registered | Lebanon | Critical Care | 30000071 | Vancomycin Hcl 1000mg | 11050 | |||
22 | 2 | Registered | Lebanon | Critical Care | 30000064 | Acetylcystine 200mg | 3000 | |||
23 | 4 | Registered | Lebanon | Critical Care | 30000062 | Acetylcystine 1000mg | 0 | |||
24 | 4 | Registered | Lebanon | Critical Care | 30000072 | Vancomycin Hcl 500mg | 0 | |||
25 | 4 | Registered | Lebanon | Critical care | 30000076 | Succinylcholine Chloride Injection 500mg | 0 | |||
26 | 4 | Registered | Lebanon | Critical care | 30000077 | Succinylcholine Chloride Injection 100mg | 0 | |||
27 | 4 | Registered | Lebanon | Critical Care | 30000079 | Sterile Nor-Adreline Concentrate 4mg | 0 | |||
28 | 4 | Registered | Lebanon | Critical Care | 30000080 | Propofol Injection 500mg | 0 | |||
29 | 4 | Registered | Lebanon | Critical Care | 30000081 | Propofol Injection 200mg | 0 | |||
30 | 4 | Registered | Lebanon | Critical Care | 30000082 | Propofol Injection 100mg | 0 | |||
31 | 4 | Registered | Lebanon | Critical Care | 30000083 | Polymyxin B Sulphate 500000 IU | 0 | |||
32 | 4 | Registered | Lebanon | Critical Care | 30000087 | Ondansetron Hydrchloride Injection 8mg | 0 | |||
33 | 4 | Registered | Lebanon | Critical care | 30000088 | Ondansetron Hydrchloride Injection 4mg | 0 | |||
34 | 4 | Registered | Lebanon | Critical care | 30000089 | Omeprazole Injection 40mg | 0 | |||
35 | 4 | Registered | Lebanon | Critical Care | 30000090 | Neostigmine Methylsulfate 2.5mg | 0 | |||
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