I am stuck... using only formulas for multi-level sorting

Maumbo

New Member
Joined
Jan 1, 2021
Messages
16
Office Version
  1. 2013
Platform
  1. Windows
((( quick disclaimer : I want to do this by formula, not by VBA or macros, so there is no "security question complications" with me easily copying the spreadsheet to another machine. ))) I am using Excel 2013 and xl2bb data at bottom of this post.


Hello! Thank you for taking the time to consider my thread. I have been looking on the google, but no answer that I could comprehend yet.

I have figured out the single level formula sorting, but I am not sure how I would nest the multi-level to do a control array formula sort.

I am stuck. I kinda think I know what to do, but not sure. I will keep playing around, but hopefully ya'll can help me save some time here.


I want to do the 2nd level sort with column C, in xl2bb data below.

Would I be adding another countif() within the match() function below? Would I need another helper column to do the 2nd level sort?


Column B has this formula. Column C,D,E also have this formula but the index array (O1:O1338) is different to pull different data.
Excel Formula:
=IFERROR(INDEX('allowance ledger'!$O$1:$O$1338,MATCH($A1,'allowance ledger'!$C$1:$C$1338,0)),"")


C1:C1338 is a helper column in another worksheet that contains all of the rank IDs, generated with countif(), for each transaction. This countif() formula is:
Excel Formula:
=COUNTIFS($O$1:$O$1338,"<="&$O25)+(0.000001*ROW())
The "(0.000001*ROW())" I use to help resolve duplicate entries and also I found it to be a good personal reference when I look at the master transaction table.


Column A, in the xl2bb data shown at bottom, is the "lookup value" for the Index() above. This is the formula I used for column A, in the xl2bb data below.
Excel Formula:
=IFERROR(SMALL($C$1:$C$1338,ROWS($A$1:$A1)+$D$1339),"")
D1339 contains a numeric value of the total number of blank transacations. This is done so the blank rows are at the bottom of the sorted data.


Hope this is clearer than mud!

Thanks again for considering my question.


Income 2020 - Copy.xlsx
ABCDE
11038.000073Budget Expenses PaidBudget Account1/10$5,103.00
21038.000074Budget Expenses PaidProperty Insurance1/10($5,103.00)
31038.000195Budget Expenses PaidGaskill, Pharis, Pharis2/13($93.00)
41038.000305Budget Expenses PaidBudget Account3/26$558.00
51038.000306Budget Expenses PaidBudget Account3/26$46.35
61038.000307Budget Expenses PaidCentral AC Maintenance4/6($558.00)
71038.000308Budget Expenses PaidCentral AC Maintenance4/6($42.82)
81038.000412Budget Expenses PaidCentral AC Maintenance4/6($3.53)
91038.000413Budget Expenses PaidGaskill, Pharis, Pharis5/5($512.50)
101038.000414Budget Expenses PaidBudget Account4/23$1,175.00
111038.000415Budget Expenses PaidIncome Taxes5/7($1,136.00)
121038.000416Budget Expenses PaidIncome Taxes5/7($39.00)
131038.00052Budget Expenses PaidBudget Account5/28$145.00
141038.000521Budget Expenses PaidDentistry5/20($145.00)
151038.001074Budget Expenses PaidBudget Account10/9$3,380.45
161038.001075Budget Expenses PaidProperty Taxes10/15($1,939.49)
171038.001076Budget Expenses PaidProperty Taxes10/15($1,440.96)
181038.001296Budget Expenses PaidBudget Account12/23$5,103.00
191038.001297Budget Expenses PaidProperty Insurance12/23($5,103.00)
201043.000022Credits UsedBudget Account1/13$471.23
211043.000144Credits UsedBudget Account2/1$402.77
221043.000255Credits UsedBudget Account3/1$321.98
231043.000366Credits UsedBudget Account4/1$337.84
241043.000472Credits UsedBudget Account5/12$205.68
251059.000076Deficit DepositsBudget Account1/6$28.02
261059.000077Deficit DepositsBudget Account1/27$100.00
271059.000311Deficit DepositsBudget Account3/2$180.62
281059.000419Deficit DepositsBudget Account4/1$245.80
Sheet2
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

alz

Board Regular
Joined
Jul 17, 2020
Messages
130
Office Version
  1. 2019
Platform
  1. Windows
Hi , Try this

In C1:C1338 is a helper column ,
=COUNTIFS($O$1:$O$1338,"<="&$O25)+(0.000001*ROW()) +(MATCH(LEFT(O1,1),{"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y"},0)/25)

In column A : Use small function
 
Last edited:
Solution

Maumbo

New Member
Joined
Jan 1, 2021
Messages
16
Office Version
  1. 2013
Platform
  1. Windows
Hi , Try this

In C1:C1338 is a helper column ,
=COUNTIFS($O$1:$O$1338,"<="&$O25)+(0.000001*ROW()) +(MATCH(LEFT(O1,1),{"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y"},0)/25)

In column A : Use small function
Thank you Alz! I will try this when I return to my office because I am currently posting from my cellphone.

I think I understand what you are doing with the match() in the C1:C1338 helper column, but I am confused so I have a question.

Array AE1:AE1338 is the source for the xl2bb's Column C.

Should the O1 in that match() rather be AE1 ?
Excel Formula:
+(MATCH(LEFT(AE1,1),{"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y"},0)/25)
 

alz

Board Regular
Joined
Jul 17, 2020
Messages
130
Office Version
  1. 2019
Platform
  1. Windows
Thank you Alz! I will try this when I return to my office because I am currently posting from my cellphone.

I think I understand what you are doing with the match() in the C1:C1338 helper column, but I am confused so I have a question.

Array AE1:AE1338 is the source for the xl2bb's Column C.

Should the O1 in that match() rather be AE1 ?
Excel Formula:
+(MATCH(LEFT(AE1,1),{"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y"},0)/25)
Yes, the formula has to pull the data from source AE1
 

Maumbo

New Member
Joined
Jan 1, 2021
Messages
16
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Wow, crazy this was so simple and I understand exactly why now. I feel silly for thinking this would be difficult. Thank you so much Alz! I very much appreciate this.

Income 2020 - Copy.xlsx
ABCDE
11038.080073Budget Expenses PaidBudget Account1/10$5,103.00
21038.080305Budget Expenses PaidBudget Account3/26$558.00
31038.080306Budget Expenses PaidBudget Account3/26$46.35
41038.080414Budget Expenses PaidBudget Account4/23$1,175.00
51038.08052Budget Expenses PaidBudget Account5/28$145.00
61038.081074Budget Expenses PaidBudget Account10/9$3,380.45
71038.081296Budget Expenses PaidBudget Account12/23$5,103.00
81038.120307Budget Expenses PaidCentral AC Maintenance4/6($558.00)
91038.120308Budget Expenses PaidCentral AC Maintenance4/6($42.82)
101038.120412Budget Expenses PaidCentral AC Maintenance4/6($3.53)
111038.160521Budget Expenses PaidDentistry5/20($145.00)
121038.280195Budget Expenses PaidGaskill, Pharis, Pharis2/13($93.00)
131038.280413Budget Expenses PaidGaskill, Pharis, Pharis5/5($512.50)
141038.360415Budget Expenses PaidIncome Taxes5/7($1,136.00)
151038.360416Budget Expenses PaidIncome Taxes5/7($39.00)
161038.640074Budget Expenses PaidProperty Insurance1/10($5,103.00)
171038.641075Budget Expenses PaidProperty Taxes10/15($1,939.49)
181038.641076Budget Expenses PaidProperty Taxes10/15($1,440.96)
191038.641297Budget Expenses PaidProperty Insurance12/23($5,103.00)
201043.080022Credits UsedBudget Account1/13$471.23
211043.080144Credits UsedBudget Account2/1$402.77
221043.080255Credits UsedBudget Account3/1$321.98
231043.080366Credits UsedBudget Account4/1$337.84
241043.080472Credits UsedBudget Account5/12$205.68
251059.080076Deficit DepositsBudget Account1/6$28.02
261059.080077Deficit DepositsBudget Account1/27$100.00
271059.080311Deficit DepositsBudget Account3/2$180.62
281059.080419Deficit DepositsBudget Account4/1$245.80
Sheet2


You basically just added the array position value to the item's rank value in the helper column. This small addition made each item more unique.

I guess this same approach should work if I wanted to add a 3rd level sort by date, with me simply adding Column D's data source datevalue() to the helper column C. So, voila, the data would then have a 3rd mult-level sort by date. My data is already sorted by date due to the nature of expenses inputed day by day so I will have to change some data to test this.

Would this idea work to do a 3rd level sort by date?
 

Maumbo

New Member
Joined
Jan 1, 2021
Messages
16
Office Version
  1. 2013
Platform
  1. Windows
I spoke too soon. Looks like rows 16 and 19 are not properly sorted. I think I understand what I must do and will try.
 

Maumbo

New Member
Joined
Jan 1, 2021
Messages
16
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

My thinking worked! Thank you Alz for showing me the path.

In the C1:C1338 helper column i changed the formula to this and made it work.
Excel Formula:
=COUNTIFS($O$1:$O$1338,"<="&$O2)+(0.00000001*ROW())
+IF(NOT(COUNTBLANK(AE2)),(COUNTIFS($AE$1:$AE$1338,"<="&$AE2))/100000,0)
I had to do the "/100000" so make the number addition smaller. When I did not use the "/100000", then the addition was too big and this big addition value changed the 1st level sort result created by column B's data source.

I also had to add more zeros to the 0.000001 , so now it is 0.00000001

I am convinced this will work to sort by a 3rd level. I will simply just add further small additions to the helper column from the Column D's data source datevalue().

Income 2020 - Copy.xlsx
ABCDE
11038.01143073000Budget Expenses PaidBudget Account1/10$5,103.00
21038.01143305000Budget Expenses PaidBudget Account3/26$558.00
31038.01143306000Budget Expenses PaidBudget Account3/26$46.35
41038.01143414000Budget Expenses PaidBudget Account4/23$1,175.00
51038.01143520000Budget Expenses PaidBudget Account5/28$145.00
61038.01144074000Budget Expenses PaidBudget Account10/9$3,380.45
71038.01144296000Budget Expenses PaidBudget Account12/23$5,103.00
81038.01146307000Budget Expenses PaidCentral AC Maintenance4/6($558.00)
91038.01146308000Budget Expenses PaidCentral AC Maintenance4/6($42.82)
101038.01146412000Budget Expenses PaidCentral AC Maintenance4/6($3.53)
111038.01149521000Budget Expenses PaidDentistry5/20($145.00)
121038.01189195000Budget Expenses PaidGaskill, Pharis, Pharis2/13($93.00)
131038.01189413000Budget Expenses PaidGaskill, Pharis, Pharis5/5($512.50)
141038.01203415000Budget Expenses PaidIncome Taxes5/7($1,136.00)
151038.01203416000Budget Expenses PaidIncome Taxes5/7($39.00)
161038.01239074000Budget Expenses PaidProperty Insurance1/10($5,103.00)
171038.01240297000Budget Expenses PaidProperty Insurance12/23($5,103.00)
181038.01242075000Budget Expenses PaidProperty Taxes10/15($1,939.49)
191038.01242076000Budget Expenses PaidProperty Taxes10/15($1,440.96)
201043.01143022000Credits UsedBudget Account1/13$471.23
211043.01143144000Credits UsedBudget Account2/1$402.77
221043.01143255000Credits UsedBudget Account3/1$321.98
231043.01143366000Credits UsedBudget Account4/1$337.84
241043.01143472000Credits UsedBudget Account5/12$205.68
251059.01143076000Deficit DepositsBudget Account1/6$28.02
261059.01143077000Deficit DepositsBudget Account1/27$100.00
271059.01143311000Deficit DepositsBudget Account3/2$180.62
281059.01143419000Deficit DepositsBudget Account4/1$245.80
Sheet2


Thanks again Alz! I will give you credit for this solution because you showed me the correct path.

I very much appreciate the help! (y):cool:
 
  • Like
Reactions: alz

alz

Board Regular
Joined
Jul 17, 2020
Messages
130
Office Version
  1. 2019
Platform
  1. Windows
My thinking worked! Thank you Alz for showing me the path.

In the C1:C1338 helper column i changed the formula to this and made it work.
Excel Formula:
=COUNTIFS($O$1:$O$1338,"<="&$O2)+(0.00000001*ROW())
+IF(NOT(COUNTBLANK(AE2)),(COUNTIFS($AE$1:$AE$1338,"<="&$AE2))/100000,0)
I had to do the "/100000" so make the number addition smaller. When I did not use the "/100000", then the addition was too big and this big addition value changed the 1st level sort result created by column B's data source.

I also had to add more zeros to the 0.000001 , so now it is 0.00000001

I am convinced this will work to sort by a 3rd level. I will simply just add further small additions to the helper column from the Column D's data source datevalue().

Income 2020 - Copy.xlsx
ABCDE
11038.01143073000Budget Expenses PaidBudget Account1/10$5,103.00
21038.01143305000Budget Expenses PaidBudget Account3/26$558.00
31038.01143306000Budget Expenses PaidBudget Account3/26$46.35
41038.01143414000Budget Expenses PaidBudget Account4/23$1,175.00
51038.01143520000Budget Expenses PaidBudget Account5/28$145.00
61038.01144074000Budget Expenses PaidBudget Account10/9$3,380.45
71038.01144296000Budget Expenses PaidBudget Account12/23$5,103.00
81038.01146307000Budget Expenses PaidCentral AC Maintenance4/6($558.00)
91038.01146308000Budget Expenses PaidCentral AC Maintenance4/6($42.82)
101038.01146412000Budget Expenses PaidCentral AC Maintenance4/6($3.53)
111038.01149521000Budget Expenses PaidDentistry5/20($145.00)
121038.01189195000Budget Expenses PaidGaskill, Pharis, Pharis2/13($93.00)
131038.01189413000Budget Expenses PaidGaskill, Pharis, Pharis5/5($512.50)
141038.01203415000Budget Expenses PaidIncome Taxes5/7($1,136.00)
151038.01203416000Budget Expenses PaidIncome Taxes5/7($39.00)
161038.01239074000Budget Expenses PaidProperty Insurance1/10($5,103.00)
171038.01240297000Budget Expenses PaidProperty Insurance12/23($5,103.00)
181038.01242075000Budget Expenses PaidProperty Taxes10/15($1,939.49)
191038.01242076000Budget Expenses PaidProperty Taxes10/15($1,440.96)
201043.01143022000Credits UsedBudget Account1/13$471.23
211043.01143144000Credits UsedBudget Account2/1$402.77
221043.01143255000Credits UsedBudget Account3/1$321.98
231043.01143366000Credits UsedBudget Account4/1$337.84
241043.01143472000Credits UsedBudget Account5/12$205.68
251059.01143076000Deficit DepositsBudget Account1/6$28.02
261059.01143077000Deficit DepositsBudget Account1/27$100.00
271059.01143311000Deficit DepositsBudget Account3/2$180.62
281059.01143419000Deficit DepositsBudget Account4/1$245.80
Sheet2


Thanks again Alz! I will give you credit for this solution because you showed me the correct path.

I very much appreciate the help! (y):cool:
Glad I can help! :)
 

Sulprobil

Board Regular
Joined
May 12, 2020
Messages
141
Office Version
  1. 2016
Platform
  1. Windows
A stable three level sort can also be established with:
MrExcel_using only formulas for multi-level sorting.xlsx
ABCDEF
1Rank
21.038.000.073Budget Expenses PaidBudget Account10/0151032
31.038.000.074Budget Expenses PaidProperty Insurance10/01-510316
41.038.000.195Budget Expenses PaidGaskill, Pharis, Pharis02/01-9312
51.038.000.305Budget Expenses PaidBudget Account03/015585
61.038.000.306Budget Expenses PaidBudget Account03/0146,356
71.038.000.307Budget Expenses PaidCentral AC Maintenance06/04-5588
81.038.000.308Budget Expenses PaidCentral AC Maintenance06/04-42,829
91.038.000.412Budget Expenses PaidCentral AC Maintenance06/04-3,5310
101.038.000.413Budget Expenses PaidGaskill, Pharis, Pharis05/05-512,513
111.038.000.414Budget Expenses PaidBudget Account04/0111753
121.038.000.415Budget Expenses PaidIncome Taxes07/05-113614
131.038.000.416Budget Expenses PaidIncome Taxes07/05-3915
14103.800.052Budget Expenses PaidBudget Account05/011457
151.038.000.521Budget Expenses PaidDentistry05/01-14511
161.038.001.074Budget Expenses PaidBudget Account09/103380,451
171.038.001.075Budget Expenses PaidProperty Taxes10/01-1939,518
181.038.001.076Budget Expenses PaidProperty Taxes10/01-144119
191.038.001.296Budget Expenses PaidBudget Account12/0151034
201.038.001.297Budget Expenses PaidProperty Insurance12/01-510317
211.043.000.022Credits UsedBudget Account01/01471,2320
221.043.000.144Credits UsedBudget Account01/02402,7721
231.043.000.255Credits UsedBudget Account01/03321,9822
241.043.000.366Credits UsedBudget Account01/04337,8423
251.043.000.472Credits UsedBudget Account12/05205,6824
261.059.000.076Deficit DepositsBudget Account06/0128,227
271.059.000.077Deficit DepositsBudget Account01/0110028
281.059.000.311Deficit DepositsBudget Account02/03180,6226
291.059.000.419Deficit DepositsBudget Account01/04245,825
Sheet2
Cell Formulas
RangeFormula
F2:F29F2=SUMPRODUCT(--(B2>$B$2:$B$29))+SUMPRODUCT(--(B2=$B$2:$B$29),--(C2>$C$2:$C$29))+SUMPRODUCT(--(B2=$B$2:$B$29),--(C2=$C$2:$C$29),--(D2>$D$2:$D$29))+SUMPRODUCT(--(B2=$B$2:$B2),--(C2=$C$2:$C2),--(D2=$D$2:$D2))
 

Maumbo

New Member
Joined
Jan 1, 2021
Messages
16
Office Version
  1. 2013
Platform
  1. Windows
A stable three level sort can also be established with:

Thanks Sulprobil. Your solution requires an extra helper column that I did not need to have.

What do you mean by "stable" ?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,006
Messages
5,599,318
Members
414,304
Latest member
sarao18592

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
Top