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
 

Maumbo

New Member
Joined
Jan 1, 2021
Messages
16
Office Version
  1. 2013
Platform
  1. Windows
My apologies Sulprobil. After I took more thought about this, I misunderstood the formula. This does not add an extra helper column.

This code, your column F, would replace my helper column C1:1338.

I like the result's simplicity, smaller numbers.

I need to study this further.
Excel Formula:
=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))

Currently, when I add a new item, takes about 10 seconds for the sorted table to be updated. Some of my accounts can have up to about 8,000 transactions. I will see if this makes the process faster.

Thank you Sulprobil! I appreciate this. (y):cool:
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Maumbo

New Member
Joined
Jan 1, 2021
Messages
16
Office Version
  1. 2013
Platform
  1. Windows
Suprobil, I love it! I got done studying your code.

Now I realized how silly I am for not seeing the simplicity of your code. The code simply lines up the array, multiplies the arrays 1s and 0s, and sums of the final total of 1s to give the cell's "rank" amongst the others in the array.

I apologize Suprobil for reacting too quick in my previous post. I love this solution!

I will see if I can give credit for 2 solutions in a thread.

Bah, I can't give credit for 2 solutions in a thread.

Thank you Suprobil! I very much appreciate this. I am always learning something new. :)
 

Sulprobil

Board Regular
Joined
May 12, 2020
Messages
141
Office Version
  1. 2016
Platform
  1. Windows
Stable means that any two records with identical sort criteria will stay in their original order. Quicksort is not stable, for example.
My approach avoids using some ROW()/10000 part or similar which might fail if your number of records is exceeding the denominator.
The formula is computationally expensive, though. For several thousands of records I would recommend to use the built-in sort via VBA.
 

Maumbo

New Member
Joined
Jan 1, 2021
Messages
16
Office Version
  1. 2013
Platform
  1. Windows
Stable means that any two records with identical sort criteria will stay in their original order. Quicksort is not stable, for example.
Thanks for the clarification. I understand now.

My approach avoids using some ROW()/10000 part or similar which might fail if your number of records is exceeding the denominator.
I agree. This is why I don't like using static data in my formulas.

To make it dynamic, then I would've done
Excel Formula:
10^len(rows($A$1:$A$1500))
to replace the 10000 and a variant of this to replace the 0.00000001.

I simply used 10000 since I knew I would never have more than 10000 transactions within 5 years for my personal spreadsheet.

So, basically, if the arrays ever needed to be expanded, then I would do a "find/replace" on my first A1 row to change "1500" to "20000" or whatever then autofill the remainder down toward 20000 or whatever.

The formula is computationally expensive, though. For several thousands of records I would recommend to use the built-in sort via VBA.
I agree and have experienced this.

Everytime I made a change to the data then I would have processing wait time of about 20 seconds when I did a test data run with 10000 records. However, I did not have the processing wait time when I did the test data run with 2000 records.

My workaround is to delete all those sort records, except for the A1 row that contains all the formulas, and then do an auto-fill down the spreadsheet when I need a sorted Account Journal report.

I appreciate the VBA recommendation but I'm trying to avoid VBA for now since the "security warning" question is annoying when working on this spreadsheet on other PCs. Maybe I could do my Account Journal report from a seperate workbook so to avoid this problem.


Thanks again Sulprobil, this is great! (y):cool:
 

Watch MrExcel Video

Forum statistics

Threads
1,123,015
Messages
5,599,351
Members
414,306
Latest member
Dennis_vdw

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