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

 

[alz]

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

 

[Maumbo]

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 ?

+(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]

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 ?

+(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]

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
	A	B	C	D	E
1	1038.080073	Budget Expenses Paid	Budget Account	1/10	$5,103.00
2	1038.080305	Budget Expenses Paid	Budget Account	3/26	$558.00
3	1038.080306	Budget Expenses Paid	Budget Account	3/26	$46.35
4	1038.080414	Budget Expenses Paid	Budget Account	4/23	$1,175.00
5	1038.08052	Budget Expenses Paid	Budget Account	5/28	$145.00
6	1038.081074	Budget Expenses Paid	Budget Account	10/9	$3,380.45
7	1038.081296	Budget Expenses Paid	Budget Account	12/23	$5,103.00
8	1038.120307	Budget Expenses Paid	Central AC Maintenance	4/6	($558.00)
9	1038.120308	Budget Expenses Paid	Central AC Maintenance	4/6	($42.82)
10	1038.120412	Budget Expenses Paid	Central AC Maintenance	4/6	($3.53)
11	1038.160521	Budget Expenses Paid	Dentistry	5/20	($145.00)
12	1038.280195	Budget Expenses Paid	Gaskill, Pharis, Pharis	2/13	($93.00)
13	1038.280413	Budget Expenses Paid	Gaskill, Pharis, Pharis	5/5	($512.50)
14	1038.360415	Budget Expenses Paid	Income Taxes	5/7	($1,136.00)
15	1038.360416	Budget Expenses Paid	Income Taxes	5/7	($39.00)
16	1038.640074	Budget Expenses Paid	Property Insurance	1/10	($5,103.00)
17	1038.641075	Budget Expenses Paid	Property Taxes	10/15	($1,939.49)
18	1038.641076	Budget Expenses Paid	Property Taxes	10/15	($1,440.96)
19	1038.641297	Budget Expenses Paid	Property Insurance	12/23	($5,103.00)
20	1043.080022	Credits Used	Budget Account	1/13	$471.23
21	1043.080144	Credits Used	Budget Account	2/1	$402.77
22	1043.080255	Credits Used	Budget Account	3/1	$321.98
23	1043.080366	Credits Used	Budget Account	4/1	$337.84
24	1043.080472	Credits Used	Budget Account	5/12	$205.68
25	1059.080076	Deficit Deposits	Budget Account	1/6	$28.02
26	1059.080077	Deficit Deposits	Budget Account	1/27	$100.00
27	1059.080311	Deficit Deposits	Budget Account	3/2	$180.62
28	1059.080419	Deficit Deposits	Budget Account	4/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]

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]

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.

=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
	A	B	C	D	E
1	1038.01143073000	Budget Expenses Paid	Budget Account	1/10	$5,103.00
2	1038.01143305000	Budget Expenses Paid	Budget Account	3/26	$558.00
3	1038.01143306000	Budget Expenses Paid	Budget Account	3/26	$46.35
4	1038.01143414000	Budget Expenses Paid	Budget Account	4/23	$1,175.00
5	1038.01143520000	Budget Expenses Paid	Budget Account	5/28	$145.00
6	1038.01144074000	Budget Expenses Paid	Budget Account	10/9	$3,380.45
7	1038.01144296000	Budget Expenses Paid	Budget Account	12/23	$5,103.00
8	1038.01146307000	Budget Expenses Paid	Central AC Maintenance	4/6	($558.00)
9	1038.01146308000	Budget Expenses Paid	Central AC Maintenance	4/6	($42.82)
10	1038.01146412000	Budget Expenses Paid	Central AC Maintenance	4/6	($3.53)
11	1038.01149521000	Budget Expenses Paid	Dentistry	5/20	($145.00)
12	1038.01189195000	Budget Expenses Paid	Gaskill, Pharis, Pharis	2/13	($93.00)
13	1038.01189413000	Budget Expenses Paid	Gaskill, Pharis, Pharis	5/5	($512.50)
14	1038.01203415000	Budget Expenses Paid	Income Taxes	5/7	($1,136.00)
15	1038.01203416000	Budget Expenses Paid	Income Taxes	5/7	($39.00)
16	1038.01239074000	Budget Expenses Paid	Property Insurance	1/10	($5,103.00)
17	1038.01240297000	Budget Expenses Paid	Property Insurance	12/23	($5,103.00)
18	1038.01242075000	Budget Expenses Paid	Property Taxes	10/15	($1,939.49)
19	1038.01242076000	Budget Expenses Paid	Property Taxes	10/15	($1,440.96)
20	1043.01143022000	Credits Used	Budget Account	1/13	$471.23
21	1043.01143144000	Credits Used	Budget Account	2/1	$402.77
22	1043.01143255000	Credits Used	Budget Account	3/1	$321.98
23	1043.01143366000	Credits Used	Budget Account	4/1	$337.84
24	1043.01143472000	Credits Used	Budget Account	5/12	$205.68
25	1059.01143076000	Deficit Deposits	Budget Account	1/6	$28.02
26	1059.01143077000	Deficit Deposits	Budget Account	1/27	$100.00
27	1059.01143311000	Deficit Deposits	Budget Account	3/2	$180.62
28	1059.01143419000	Deficit Deposits	Budget Account	4/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!

 

[alz]

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.

=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
	A	B	C	D	E
1	1038.01143073000	Budget Expenses Paid	Budget Account	1/10	$5,103.00
2	1038.01143305000	Budget Expenses Paid	Budget Account	3/26	$558.00
3	1038.01143306000	Budget Expenses Paid	Budget Account	3/26	$46.35
4	1038.01143414000	Budget Expenses Paid	Budget Account	4/23	$1,175.00
5	1038.01143520000	Budget Expenses Paid	Budget Account	5/28	$145.00
6	1038.01144074000	Budget Expenses Paid	Budget Account	10/9	$3,380.45
7	1038.01144296000	Budget Expenses Paid	Budget Account	12/23	$5,103.00
8	1038.01146307000	Budget Expenses Paid	Central AC Maintenance	4/6	($558.00)
9	1038.01146308000	Budget Expenses Paid	Central AC Maintenance	4/6	($42.82)
10	1038.01146412000	Budget Expenses Paid	Central AC Maintenance	4/6	($3.53)
11	1038.01149521000	Budget Expenses Paid	Dentistry	5/20	($145.00)
12	1038.01189195000	Budget Expenses Paid	Gaskill, Pharis, Pharis	2/13	($93.00)
13	1038.01189413000	Budget Expenses Paid	Gaskill, Pharis, Pharis	5/5	($512.50)
14	1038.01203415000	Budget Expenses Paid	Income Taxes	5/7	($1,136.00)
15	1038.01203416000	Budget Expenses Paid	Income Taxes	5/7	($39.00)
16	1038.01239074000	Budget Expenses Paid	Property Insurance	1/10	($5,103.00)
17	1038.01240297000	Budget Expenses Paid	Property Insurance	12/23	($5,103.00)
18	1038.01242075000	Budget Expenses Paid	Property Taxes	10/15	($1,939.49)
19	1038.01242076000	Budget Expenses Paid	Property Taxes	10/15	($1,440.96)
20	1043.01143022000	Credits Used	Budget Account	1/13	$471.23
21	1043.01143144000	Credits Used	Budget Account	2/1	$402.77
22	1043.01143255000	Credits Used	Budget Account	3/1	$321.98
23	1043.01143366000	Credits Used	Budget Account	4/1	$337.84
24	1043.01143472000	Credits Used	Budget Account	5/12	$205.68
25	1059.01143076000	Deficit Deposits	Budget Account	1/6	$28.02
26	1059.01143077000	Deficit Deposits	Budget Account	1/27	$100.00
27	1059.01143311000	Deficit Deposits	Budget Account	3/2	$180.62
28	1059.01143419000	Deficit Deposits	Budget Account	4/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!

Glad I can help!

 

[Sulprobil]

A stable three level sort can also be established with:

MrExcel_using only formulas for multi-level sorting.xlsx
	A	B	C	D	E	F
1						Rank
2	1.038.000.073	Budget Expenses Paid	Budget Account	10/01	5103	2
3	1.038.000.074	Budget Expenses Paid	Property Insurance	10/01	-5103	16
4	1.038.000.195	Budget Expenses Paid	Gaskill, Pharis, Pharis	02/01	-93	12
5	1.038.000.305	Budget Expenses Paid	Budget Account	03/01	558	5
6	1.038.000.306	Budget Expenses Paid	Budget Account	03/01	46,35	6
7	1.038.000.307	Budget Expenses Paid	Central AC Maintenance	06/04	-558	8
8	1.038.000.308	Budget Expenses Paid	Central AC Maintenance	06/04	-42,82	9
9	1.038.000.412	Budget Expenses Paid	Central AC Maintenance	06/04	-3,53	10
10	1.038.000.413	Budget Expenses Paid	Gaskill, Pharis, Pharis	05/05	-512,5	13
11	1.038.000.414	Budget Expenses Paid	Budget Account	04/01	1175	3
12	1.038.000.415	Budget Expenses Paid	Income Taxes	07/05	-1136	14
13	1.038.000.416	Budget Expenses Paid	Income Taxes	07/05	-39	15
14	103.800.052	Budget Expenses Paid	Budget Account	05/01	145	7
15	1.038.000.521	Budget Expenses Paid	Dentistry	05/01	-145	11
16	1.038.001.074	Budget Expenses Paid	Budget Account	09/10	3380,45	1
17	1.038.001.075	Budget Expenses Paid	Property Taxes	10/01	-1939,5	18
18	1.038.001.076	Budget Expenses Paid	Property Taxes	10/01	-1441	19
19	1.038.001.296	Budget Expenses Paid	Budget Account	12/01	5103	4
20	1.038.001.297	Budget Expenses Paid	Property Insurance	12/01	-5103	17
21	1.043.000.022	Credits Used	Budget Account	01/01	471,23	20
22	1.043.000.144	Credits Used	Budget Account	01/02	402,77	21
23	1.043.000.255	Credits Used	Budget Account	01/03	321,98	22
24	1.043.000.366	Credits Used	Budget Account	01/04	337,84	23
25	1.043.000.472	Credits Used	Budget Account	12/05	205,68	24
26	1.059.000.076	Deficit Deposits	Budget Account	06/01	28,2	27
27	1.059.000.077	Deficit Deposits	Budget Account	01/01	100	28
28	1.059.000.311	Deficit Deposits	Budget Account	02/03	180,62	26
29	1.059.000.419	Deficit Deposits	Budget Account	01/04	245,8	25
Sheet2

Cell Formulas
Range	Formula
F2:F29	F2	=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]

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" ?