Large function

ripdaman

New Member
Joined
Mar 11, 2023
Messages
8
Office Version
  1. 365
Platform
  1. Windows
I have a set of random numbers in two columns A and B. I want to arrange the sum of these two columns in descending order. However, if the sum of two numbers in Column A and B is equal to another row(s), then the descending order should give priority to the greater number in Column A. For example, in the table below, sum of two numbers in Column A and B in first two rows is equal (38). However, the descending order in Column C should keep row 2 as No.1 as row 2 in Column A has the greater number (22) as compared to row 1 in Column A (20).

Thanks in advance.

AB
2018
2216
1922
1526
1823
2314
2611
2515
2718
299
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
If you are arranging the sums in descending order, why does it matter which it comes from, it's the same number?
=SORT(A1:A10+B1:B10,,-1)

20​
18​
45​
22​
16​
41​
19​
22​
41​
15​
26​
41​
18​
23​
40​
23​
14​
38​
26​
11​
38​
25​
15​
38​
27​
18​
37​
29​
9​
37​
 
Upvote 0
If you are arranging the sums in descending order, why does it matter which it comes from, it's the same number?
=SORT(A1:A10+B1:B10,,-1)

20​
18​
45​
22​
16​
41​
19​
22​
41​
15​
26​
41​
18​
23​
40​
23​
14​
38​
26​
11​
38​
25​
15​
38​
27​
18​
37​
29​
9​
37​
This is required as values in Columns A and B are readings on a particular day. If the sum of two values are equal then the index function gives the same date for similar values.
 
Upvote 0
Are you just looking to sort your table? If so, add a formula in column C, =A1+B1 , and copy down. Then use the Sort tool to sort the range by column C, then by column A.

If you want to do it with a formula, you can do something like:

Book1
ABCDEFGHI
1ABDateABDate
220181/1/202427181/9/202445
322161/2/202419221/3/202441
419221/3/202418231/5/202441
515261/4/202415261/4/202441
618231/5/202425151/8/202440
723141/6/20242991/10/202438
826111/7/202422161/2/202438
925151/8/202420181/1/202438
1027181/9/202426111/7/202437
112991/10/202423141/6/202437
Sheet5
Cell Formulas
RangeFormula
F2:H11F2=SORTBY(A2:C11,A2:A11+B2:B11,-1,A2:A11,-1)
I2:I11I2=INDEX(F2#,0,1)+INDEX(F2#,0,2)
Dynamic array formulas.


The I formula is optional.
 
Upvote 0
Are you just looking to sort your table? If so, add a formula in column C, =A1+B1 , and copy down. Then use the Sort tool to sort the range by column C, then by column A.

If you want to do it with a formula, you can do something like:

Book1
ABCDEFGHI
1ABDateABDate
220181/1/202427181/9/202445
322161/2/202419221/3/202441
419221/3/202418231/5/202441
515261/4/202415261/4/202441
618231/5/202425151/8/202440
723141/6/20242991/10/202438
826111/7/202422161/2/202438
925151/8/202420181/1/202438
1027181/9/202426111/7/202437
112991/10/202423141/6/202437
Sheet5
Cell Formulas
RangeFormula
F2:H11F2=SORTBY(A2:C11,A2:A11+B2:B11,-1,A2:A11,-1)
I2:I11I2=INDEX(F2#,0,1)+INDEX(F2#,0,2)
Dynamic array formulas.


The I formula is optional.
Thank you very much for your perfect response to my query. One additional query: If my data has zero values and i want to arrange my data in ascending order excluding zeroes, is there a way i can do it?
 
Upvote 0
Ascending order, as opposed to descending like the example? It's easy enough to do with a formula, we just add a FILTER to it to strip out the zero values. If you want to do it without a formula, we could use the Filter tool, then copy the results to another range, then use the sort tool. If you have an example of what you'd like, I'll take a look at it.
 
Upvote 0
Ascending order, as opposed to descending like the example? It's easy enough to do with a formula, we just add a FILTER to it to strip out the zero values. If you want to do it without a formula, we could use the Filter tool, then copy the results to another range, then use the sort tool. If you have an example of what you'd like, I'll take a look at it.
My Apologies for the delayed response.

This is a sample data I want in ascending order excluding zeroes.

Thanks

12-25-202211.10
01-12-202111.10
12-27-201911.10
12-29-201911.10
01-16-202211.00
01-18-202211.00
01-10-202410.80
01-09-202410.50
01-21-20249.40
12-25-20199.00
12-26-20198.80
01-24-20248.40
01-01-20270.00
01-02-20270.00
01-03-20270.00
01-04-20270.00
01-05-20270.00
01-06-20270.00
01-07-20270.00
01-08-20270.00
 
Upvote 0
With a formula:

Book1
ABCDE
112/25/202211.101/24/20248.40
21/12/202111.1012/26/20198.80
312/27/201911.1012/25/20199.00
412/29/201911.101/21/20249.40
51/16/202211.001/9/202410.50
61/18/202211.001/10/202410.80
71/10/202410.801/16/202211.00
81/9/202410.501/18/202211.00
91/21/20249.4012/25/202211.10
1012/25/20199.001/12/202111.10
1112/26/20198.8012/27/201911.10
121/24/20248.4012/29/201911.10
131/1/20270.00
141/2/20270.00
151/3/20270.00
161/4/20270.00
171/5/20270.00
181/6/20270.00
191/7/20270.00
201/8/20270.00
Sheet5
Cell Formulas
RangeFormula
D1:E12D1=SORT(FILTER(A1:B20,B1:B20<>0),2,1)
Dynamic array formulas.


If you want to do it without a formula, I'd just filter column B to show only the non-zero rows, copy that, paste it to another range, then sort that by the second column.
 
Upvote 0
Solution

Forum statistics

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

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