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

Maumbo · Jan 12, 2021

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

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 · Jan 12, 2021

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 · Jan 12, 2021

alz said:
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 · Jan 12, 2021

Maumbo said:
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 · Jan 12, 2021

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 · Jan 12, 2021

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 · Jan 12, 2021

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

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 · Jan 12, 2021

Maumbo said:
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
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 · Jan 13, 2021

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

1.038.000.308

Budget Expenses Paid

Central AC Maintenance

06/04

-42,82

9

1.038.000.412

Budget Expenses Paid

Central AC Maintenance

06/04

-3,53

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

1.038.001.076

Budget Expenses Paid

Property Taxes

10/01

-1441

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

1.059.000.077

Deficit Deposits

Budget Account

01/01

100

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 · Jan 13, 2021

Sulprobil said:
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" ?

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

Maumbo

New Member

Excel Facts

alz

Board Regular

Maumbo

New Member

alz

Board Regular

Maumbo

New Member

Maumbo

New Member

Maumbo

New Member

alz

Board Regular

Sulprobil

Banned user

Maumbo

New Member

Similar threads

Forum statistics

Share this page

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

New Member

Excel Facts

Board Regular

New Member

Board Regular

New Member

New Member

New Member

Board Regular

Banned user

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock