How to multi level Sort, Filter and TEXTJOIN using macro?

[Ariful Islam]

I have three sheets in my workbook named Source, Calculation and Dept_Inst.
The range of the source sheet is "Source!B7:J50000" and I want to first sort the data according to the following conditions.

Sort Level-1, Sort By column ?Source!I7:I50000? & Sort Order is- Largest to smallest,
Sort Level-2, Sort By column ?Source!E7:E50000? & Sort Order is- Smallest to Largest,
Sort Level-3, Sort By column ?Source!G7:G50000? & Sort Order is- A To Z,
Sort Level-4, Sort By column ?Source!H7:H50000? & Sort Order is- Dept_Inst!B8:B23,
Sort Level-5, Sort By column ?Source!J7:J50000? & Sort Order is- Dept_Inst!C8:C23,
Sort Level-6, Sort By column ?Source!B7:B50000? & Sort Order is- Smallest to Largest,

Secondly, have to do filter as following, Filter with the Criteria of

Filter Criteria-1, Calculation!C11:CT11 and
Filter Criteria-2 is Calculation!C12:CT12,

Now finally, Join Text on Sorted and Filtered range as using following formula to the output range "Calculation!C14:CT14" e.g beneath the respective column. "=TEXTJOIN("/", TRUE, I7,E7,H7, G7, J7, B7)".

Can anyone help me with this issue?

[Joske920]

Hi,

i hope you use 365 with the new formula's

ive made a powerquery to manage the data and used the new spill formulas to build your overview

give it a look maybe you see something you like and want to use

[Ariful Islam]

I am using Office 2021. However Power Quarry is something I know nothing about it. Your efforts are admirable. I sincerely appreciate it.