Sorting dynamic array filter by date then by property number

[Apie1975]

Good morning
This is my first time asking for help please.

I am stuck on a dynamic array filter.

All data where it is pulled from is in date order but I need the filter to stay in date order but then if there is a second entry for the specific property number it must group them together. Please see current image. The two numbers on 3 November must just be together.

[#2]

@Apie1975 

You may use SORTBY function (microsoft.com)

[#3]

Thank you Sergei could you please if possible show it in the formula I am using as per the screenshot please.

[#4]

@Apie1975 

@Sergei Baklan is pretty good but I doubt even he can sort a .png file, an .xlsx would have helped.

Assuming you have named the columns or use structured referencing, something along the lines

= SORTBY(Table,
    DateImported,1,
    PlanNo,1)

would allow you to control the sort order of the output array.

[#5]

@Apie1975 

In addition to @Peter Bartholomew  explanation. In your case that could be like

=LET(
  f,     FILTER(<current expresssion>),
  sortA, INDEX(f,0,3),
  sortB, INDEX(f,0,4),
  SORTBY(f, sortA, 1, sortB, 1) )

[#6]

@Peter Bartholomew 

@Sergei Baklan 

My apologies, I wasn't sure what is the easiest, but here is the file.   Thank you for your kind help it is really appreciated.

[#7]

@Apie1975 

See if this satisfies your requirements:

[#8]

THANK YOU SO MUCH - I do have it working now but you will see I have a name in F3 and if I change the name of the person it should be picking up that portion of the name from column U which has the email addresses therefore filtering different people - so in my mind it is an additional criteria which needs to be added - would you be able to help me to add that to the filter please

[#9]

I cannot add another file or a screenshot.

The formula that is working is :
=SORT('https://crockersnz.sharepoint.com/Body Corporate/Portfolio Spreadsheets/Portfolio 1/[Crockers - Invoices .xlsx]Rep Nov 21'!$A$2:$S$10000,{1,2},{1,1})

the formula I had was:
=FILTER('https://crockersnz.sharepoint.com/Body Corporate/Portfolio Spreadsheets/Portfolio 1/[Crockers - Invoices .xlsx]Rep Nov 21'!$A$1:$S$9928,ISNUMBER(SEARCH(F3,'https://crockersnz.sharepoint.com/Body Corporate/Portfolio Spreadsheets/Portfolio 1/[Crockers - Invoices .xlsx]Rep Nov 21'!$S$1:$S$9928))*(F3<>""),"No Invoices")

it filtered all the right names but didn't sort by date and then plan number - now I am sorting by date and plan number but just can't get the second part of the formula from the isnumber to work with the sort - to look up on the the transactions for the specific person.

[#10 · Accepted solution]

@Apie1975 

Try this formula, (Don't forget to adjust the ranges before changing the name):

=SORTBY(FILTER('https://crockersnz.sharepoint.com/Body Corporate/Portfolio Spreadsheets/Portfolio 1/[Crockers - Invoices .xlsx]Rep Nov 21'!$A$10:$V$49,ISNUMBER(SEARCH(LEFT('https://crockersnz.sharepoint.com/Body Corporate/Portfolio Spreadsheets/Portfolio 1/[Crockers - Invoices .xlsx]Rep Nov 21'!$U$10:$U$49,LEN('https://crockersnz.sharepoint.com/Body Corporate/Portfolio Spreadsheets/Portfolio 1/[Crockers - Invoices .xlsx]Rep Nov 21'!F3)),'https://crockersnz.sharepoint.com/Body Corporate/Portfolio Spreadsheets/Portfolio 1/[Crockers - Invoices .xlsx]Rep Nov 21'!F3))),'https://crockersnz.sharepoint.com/Body Corporate/Portfolio Spreadsheets/Portfolio 1/[Crockers - Invoices .xlsx]Rep Nov 21'!$C$10:$C$49,1,'Inv Nov'!$D$10:$D$49,1)

Cheers

[#11]

Fabulous thank you so much for the help!!

It is working and I am super excited.

Take care

[#12]

@Apie1975 

If you want to spill it only by Columns and not the whole table rows, try this formula:

=FILTER(SORTBY(FILTER('Inv Nov'!$A$10:$V$49,ISNUMBER(SEARCH(LEFT('Inv Nov'!$U$10:$U$49,LEN('Inv Nov'!$F$3)),'Inv Nov'!$F$3))),'Inv Nov'!$C$10:$C$49,1,'Inv Nov'!$D$10:$D$49,1),('Inv Nov'!$D$10:$D$49='Inv Nov'!D16)*('Inv Nov'!$G$10:$G$49='Inv Nov'!G16))

Don't forget to adjust the range and change these:

'Inv Nov'!

To these:

'https://crockersnz.sharepoint.com/Body Corporate/Portfolio Spreadsheets/Portfolio 1/[Crockers - Invoices .xlsx]Rep Nov 21'!

cheers

[#13]

Thank you so much - I wish I was as good as all you. Trying to get there slowly but surely.

[#14]

Last formula I am trying to figure out if you could please help me.

=IF(B55-C55=0,"1",B55-C55)

all I need to add to the above formula is that if B55 is blank then A55 must also be blank If you could please help me

[#15]

which cell does this formula reside
=IFS(ISBLANK(B55),"",B55-C55)

=IFS(ISBLANK(B55),"",B55-C55=0,"1",ISNUMBER(B55),B55-C55,NOT(ISNUMBER(B55)),"Must be a number")

[#16]

Fabulous, you are really fast thank you !!

The only think it with my original formula - I added that if B55-C55=0,"1" , but if I add that portion to the formula it gives me a you have entered too many arguments

This was the original formula - I need to keep the part where if it is 0 it will be a 1 as I am calculating days and if it is today less today it must count as 1 day