Dynamic array to replace pivot table (sorting problem)

[Noneto]

Hello Excel comunity,

I started playing with dynamic arrays to replace PivotTables. It s working well but I am struggling to order the data in the way I need.
I wonder if is possible to make this Dynamic Array table without using the Auxiliar Table.

Here is an example:

Formulas:
1)=UNIQUE(Data[Customer])
2)=SUMIF(Data[Customer],D4#,Data[Amount])

3)=SORTBY(D4#,E4#,-1)
4)=SUMIF(Data[Customer],D14#,Data[Amount])

Thanks,
Nicolas.

 

[Radoslaw Poprawski]

did you try SORT formula?

with :

1, put ur whole table in.
2. select by which column from ur table you want to sort.
3. ascending or descending

I wonder if is possible to make this Dynamic Array table without using the Auxiliar Table.

kinda, select whole table and press f9, then enter

 

[Noneto]

did you try SORT formula?

with :
View attachment 41631
1, put ur whole table in.
2. select by which column from ur table you want to sort.
3. ascending or descending


kinda, select whole table and press f9, then enter

Thanks for your comment.

I am using that formula in the final table already.
The problem is that I can't avoid creating the AUX table first to Combine and sum the rows with the same customer name.

 

[Peter_SSs]

Try this. Column G formula provided as a marginally shorter version of the col D formula if you have the LET function.

Noneto.xlsm
	A	B	C	D	E	F	G	H
1
2
3	Customer	Amount
4	Customer1	42		Customer4	709		Customer4	709
5	Customer2	487		Customer2	487		Customer2	487
6	Customer1	312		Customer5	432		Customer5	432
7	Customer4	653		Customer7	423		Customer7	423
8	Customer5	432		Customer1	354		Customer1	354
9	Customer4	31
10	Customer7	423
11	Customer4	25
12
Sheet1

Cell Formulas
Range	Formula
D4:D8	D4	=SORTBY(UNIQUE(Data[Customer]),SUMIF(Data[Customer],UNIQUE(Data[Customer]),Data[Amount]),-1)
E4:E8,H4:H8	E4	=SUMIF(Data[Customer],D4#,Data[Amount])
G4:G8	G4	=LET(cust,Data[Customer],uc,UNIQUE(cust),SORTBY(uc,SUMIF(cust,uc,Data[Amount]),-1))
Dynamic array formulas.

 

[Noneto]

Try this. Column G formula provided as a marginally shorter version of the col D formula if you have the LET function.

Noneto.xlsm
	A	B	C	D	E	F	G	H
1
2
3	Customer	Amount
4	Customer1	42		Customer4	709		Customer4	709
5	Customer2	487		Customer2	487		Customer2	487
6	Customer1	312		Customer5	432		Customer5	432
7	Customer4	653		Customer7	423		Customer7	423
8	Customer5	432		Customer1	354		Customer1	354
9	Customer4	31
10	Customer7	423
11	Customer4	25
12
Sheet1

Cell Formulas
Range	Formula
D4:D8	D4	=SORTBY(UNIQUE(Data[Customer]),SUMIF(Data[Customer],UNIQUE(Data[Customer]),Data[Amount]),-1)
E4:E8,H4:H8	E4	=SUMIF(Data[Customer],D4#,Data[Amount])
G4:G8	G4	=LET(cust,Data[Customer],uc,UNIQUE(cust),SORTBY(uc,SUMIF(cust,uc,Data[Amount]),-1))
Dynamic array formulas.

Thanks A LOT. You not only found the solution, you found 2 ways to do it.

 

[Peter_SSs]

Thanks A LOT. You not only found the solution, you found 2 ways to do it.

You are welcome. Thanks for the follow-up.

 

[Fluff]

For future reference

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: https://www.excelforum.com/excel-ge...y-to-replace-pivot-table-sorting-problem.html
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Noneto]

For future reference

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Dynamic array to replace pivot table (sorting problem) [SOLVED]
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Got It. Thanks for letting me know.