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

[Maumbo]

((( 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.

=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:

=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.

=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
	A	B	C	D	E
1	1038.000073	Budget Expenses Paid	Budget Account	1/10	$5,103.00
2	1038.000074	Budget Expenses Paid	Property Insurance	1/10	($5,103.00)
3	1038.000195	Budget Expenses Paid	Gaskill, Pharis, Pharis	2/13	($93.00)
4	1038.000305	Budget Expenses Paid	Budget Account	3/26	$558.00
5	1038.000306	Budget Expenses Paid	Budget Account	3/26	$46.35
6	1038.000307	Budget Expenses Paid	Central AC Maintenance	4/6	($558.00)
7	1038.000308	Budget Expenses Paid	Central AC Maintenance	4/6	($42.82)
8	1038.000412	Budget Expenses Paid	Central AC Maintenance	4/6	($3.53)
9	1038.000413	Budget Expenses Paid	Gaskill, Pharis, Pharis	5/5	($512.50)
10	1038.000414	Budget Expenses Paid	Budget Account	4/23	$1,175.00
11	1038.000415	Budget Expenses Paid	Income Taxes	5/7	($1,136.00)
12	1038.000416	Budget Expenses Paid	Income Taxes	5/7	($39.00)
13	1038.00052	Budget Expenses Paid	Budget Account	5/28	$145.00
14	1038.000521	Budget Expenses Paid	Dentistry	5/20	($145.00)
15	1038.001074	Budget Expenses Paid	Budget Account	10/9	$3,380.45
16	1038.001075	Budget Expenses Paid	Property Taxes	10/15	($1,939.49)
17	1038.001076	Budget Expenses Paid	Property Taxes	10/15	($1,440.96)
18	1038.001296	Budget Expenses Paid	Budget Account	12/23	$5,103.00
19	1038.001297	Budget Expenses Paid	Property Insurance	12/23	($5,103.00)
20	1043.000022	Credits Used	Budget Account	1/13	$471.23
21	1043.000144	Credits Used	Budget Account	2/1	$402.77
22	1043.000255	Credits Used	Budget Account	3/1	$321.98
23	1043.000366	Credits Used	Budget Account	4/1	$337.84
24	1043.000472	Credits Used	Budget Account	5/12	$205.68
25	1059.000076	Deficit Deposits	Budget Account	1/6	$28.02
26	1059.000077	Deficit Deposits	Budget Account	1/27	$100.00
27	1059.000311	Deficit Deposits	Budget Account	3/2	$180.62
28	1059.000419	Deficit Deposits	Budget Account	4/1	$245.80
Sheet2

 

[Maumbo]

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.

=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.

 

[Maumbo]

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]

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]

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

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!