SORT - an Extract List based on criteria - BUT 2019 Version ONLY - NOT 365

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
8,757
Office Version
  1. 365
Platform
  1. MacOS
I have had some help here previously, but using 365 functions and the solutions have been great
However , now i'm trying to use 2019 version functions only
The person i'm helping is very pleased with the functionality of the 365 version , And in US I'm in UK - so a bit of a timezone issue

BUT last weekend they upgraded to the online model - and have been told the online version is 2019 and the desktop app is 365 , so when they are online, they get a #NAME error

They have flagged with IT that this is a major compatibility issue and will impact a lot of people , but this is the first issue - they may sort out in few weeks

Does not make sense to me, as I was under the impression all online was now 365, various subscriptions for Business

She has the Apps on the desktop still 365 version and online 2019 version to share with other workers

But she gets a #NAME error for any 365 functions I have used !!!!!!! - Textbefore() & filter() , LET() etc

I have updated the formulas to use ONLY 2019 versions - BUT she also wants to sort the summary extraction by the NAME , which is not working - Looks up and returns the same order of name in the list , unless the source data is sorted, which they do not want to do - It maybe not possible, which is fine

In my example - just for clarity
I have 2 helper columns AA and AB
AA is just to get the 1st line of a cell - as the main one will have a lot of char(10) - so thats AA , just the first line, and i can pull the data from that column
Then we have a criteria to show only rows below a certain % based on column P - BUT i could not make that work - so i have another helper column AB which simply uses the <=% in the summary and flags the row as a 1
so i then use the 1 as a criteria in that column AB to pull over the rows i need - Those 2 columns are also in a table because , she deletes rows and inserts rows - to archive info

And that took a lot of thought as I'm so used to the easier functions now in 365

I have used
=IFERROR(INDEX(Active!$AA$2:$AA$500,SMALL(IF(Active!$AB$2:$AB$500=1,ROW($K$2:$K$500)),ROW(1:1))-1,1),"")
to Pull out the name in column AA , based on the criteria of a 1 in AB column
This works fine and pulls out the names

Then I use
=IF($E3="","",INDEX(Active!K$2:K$500,MATCH(Summary!$E3,Active!$AA$2:$AA$500,0)))
to lookup column K based on the name

I know there will be an issue if the same name is duplicated , and the wrong info will be pulled over , but that does seem to be a high risk , and WIP for a unique ID for each row

Then I use the same formula in the other columns on summary to pull over the other columns needed

Everything works fine

UNTIL

The Summary sheet cannot be sorted - as the names remain in the same order , they are in the data sheet , but the other columns do sort and so the WRONG associated data is extracted
I know she should probably sort the raw data sheet , and then the names will be extracted in the correct order

BUT wondered if there was a formula to replace
=IFERROR(INDEX(Active!$AA$2:$AA$500,SMALL(IF(Active!$AB$2:$AB$500=1,ROW($K$2:$K$500)),ROW(1:1))-1,1),"")

I have used google and searched various forums , also looking through all my previous examples
Problem now of course with FILTER being available from 2021 version and 365 - MOST google results tend to show 365 functions

I did come across a couple of other formulas - BUT they work the same - looking at the rows, so bring back in order on data sheet

maybe its NOT possible to do this just in the summary and the only way is the Data sheet being sorted, which is fine, if thats the reality

DATA SHEET - named ACTIVE
Wayne FORUM Help2.xlsx
AKLMNOPZAAAB
1HeaderAHeaderKheaderLHeader MHeader NHeader OHeader PHeaderAABelow threshold
2Flinstone, Fred 123456789 abced ef g$2,000.00$0.00$20.00$200.00$2,220.00-11.000%Flinstone, Fred 1
3A--name-1 -------2nd line$10,000.00$9,000.00$10.00$0.00$1,010.0089.900%A--name-1 1
4Z--name-10 ------ line 2$10,000.00$10,000.00$0.00$0.00$0.00100.000%Z--name-10 1
5Y--name-11 ------- line 2 ---------line 3$10,000.00$2,300.00$0.00$0.00$7,700.0023.000%Y--name-11 1
6D--name-12$10,000.00$0.00$0.00$12.00$10,012.00-0.120%D--name-121
Active
Cell Formulas
RangeFormula
O2:O6O2=IF(A2="","",SUM(K2)-(L2-M2)+(N2))
P2:P6P2=IFERROR(IF(K2="Closed","Closed",((L2-M2-N2)/K2)),"")
AA2:AA6AA2=IF(A2="","",IFERROR(LEFT(A2,FIND(CHAR(10),A2,1)),A2))
AB2:AB6AB2=IF(A2="","",IF(P2<=Summary!$F$1/100,1,0))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
L2:O19,L21:O500Expression=$K2="closed"textNO
A2:Q19,A21:Q500,A20:J20,Q20Expression=$P2="closed"textYES
A2:Q19,A20:J20,Q20,A21:Q500Expression=$N2>0textYES
A2:Q19,A20:J20,Q20,A21:Q500Expression=AND($L2<>"",($L2-$M2)<=0)textNO
A2:Q19,A20:J20,Q20,A21:Q500Expression=AND($P2<>"",$P2<=0.25)textNO
A2:Q19,A20:J20,Q20,A21:Q500Expression=AND($P2<>"",$P2<=0.5)textNO
A2:Q19,A20:J20,Q20,A21:Q500Expression=AND($P2<>"",$P2<=0.75)textNO


SUMMARY SHEET
Wayne FORUM Help2.xlsx
EFGHIJK
1Clients under100% <- enter the Number - NOT a Percent - EG for 35%, just type in 35 - Trust Balance to Minimum Retainer % required to extract the summary below
2headerAA HeaderK headerL Header M Header N Header O Header P
3Flinstone, Fred $ 2,000.00$ -$ 20.00$ 200.00$ 2,220.00-11%
4A--name-1 $ 10,000.00$ 9,000.00$ 10.00$ -$ 1,010.0090%
5Z--name-10 $ 10,000.00$ 10,000.00$ -$ -$ -100%
6Y--name-11 $ 10,000.00$ 2,300.00$ -$ -$ 7,700.0023%
7D--name-12$ 10,000.00$ -$ -$ 12.00$ 10,012.000%
Summary
Cell Formulas
RangeFormula
E3:E7E3=IFERROR(INDEX(Active!$AA$2:$AA$500,SMALL(IF(Active!$AB$2:$AB$500=1,ROW($K$2:$K$500)),ROW(1:1))-1,1),"")
F3:K7F3=IF($E3="","",INDEX(Active!K$2:K$500,MATCH(Summary!$E3,Active!$AA$2:$AA$500,0)))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E3:K9,E15:K201,E10:E14Expression=$E3<>""textNO


I only leave the file on the share for a fewdays
 
Last edited:

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Is the below what you are looking for?
Wayne FORUM Help2.xlsx
EFGHIJK
1Clients under100% <- enter the Number - NOT a Percent - EG for 35%, just type in 35 - Trust Balance to Minimum Retainer % required to extract the summary below
2headerAA HeaderK headerL Header M Header N Header O Header P
3Flinstone, Fred $ 2,000.00$ -$ 20.00$ 200.00$ 2,220.00-11%
4D--name-12$ 10,000.00$ -$ -$ 12.00$ 10,012.000%
5Y--name-11 $ 10,000.00$ 2,300.00$ -$ -$ 7,700.0023%
6A--name-1 $ 10,000.00$ 9,000.00$ 10.00$ -$ 1,010.0090%
7Z--name-10 $ 10,000.00$ 10,000.00$ -$ -$ -100%
Summary
Cell Formulas
RangeFormula
E3:E7E3=IFERROR(INDEX(Active!$AA$2:$AA$500,MATCH(SMALL(IF(Active!$AB$2:$AB$500=1,Active!$P$2:$P$500),ROW()-2),Active!$P$2:$P$500,0),1),"")
F3:K7F3=IF($E3="","",INDEX(Active!K$2:K$500,MATCH(Summary!$E3,Active!$AA$2:$AA$500,0)))
Press CTRL+SHIFT+ENTER to enter array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F3:K9,E8:E14,E15:K201Expression=$E3<>""textNO
 
Upvote 0
thanks foir the reply
No
I was looking for the names to be sorted alpha

A--name-1
D--name-12
Flinstone, Fred
Y--name-11
Z--name-10

and associated data to also work

its a shame, because with 365 - its quite easy to do exactly as i want ...... but not in this older 2019 version
but thanks for the reply
 
Upvote 0
Maybe the below:
Cell Formulas
RangeFormula
E3:E6E3=IFERROR(INDEX(Active!$AA$2:$AA$6,MATCH(SMALL(IF(Active!$AB$2:$AB$6=1,IF(ISBLANK(Active!$AA$2:$AA$6),"",IF(COUNTIF($E$2:E2,Active!$AA$2:$AA$6)=0,IF(ISNUMBER(Active!$AA$2:$AA$6),COUNTIF(Active!$AA$2:$AA$6,"<"&Active!$AA$2:$AA$6),COUNTIF(Active!$AA$2:$AA$6,"<"&Active!$AA$2:$AA$6)+SUM(1*ISNUMBER(Active!$AA$2:$AA$6))+1),"")),""),1),IF(Active!$AB$2:$AB$6=1,IF(ISBLANK(Active!$AA$2:$AA$6),"",IF(COUNTIF($E$2:E2,Active!$AA$2:$AA$6)=0,IF(ISNUMBER(Active!$AA$2:$AA$6),COUNTIF(Active!$AA$2:$AA$6,"<"&Active!$AA$2:$AA$6),COUNTIF(Active!$AA$2:$AA$6,"<"&Active!$AA$2:$AA$6)+SUM(1*ISNUMBER(Active!$AA$2:$AA$6))+1),"")),""),0)),"")
F3:K6F3=IF($E3="","",INDEX(Active!K$2:K$500,MATCH(Summary!$E3,Active!$AA$2:$AA$500,0)))
Press CTRL+SHIFT+ENTER to enter array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E15:K201,E3:K3,F4:K9,E4:E14Expression=$E3<>""textNO
 
Upvote 0
Solution
wow - thanks you so much - that seems to work OK on the real data
brilliant
 
Upvote 0
You are welcome, it will sort Alpha and Numeric values and should also skip blanks. You have a few ranges to adjust ;)
 
Upvote 0
yes, thanks i did adjust range - I now need to ask whats the maximum rows she would ever use , as it does slow down with 500
as the difference in speed between 100rows and 500 on my M1 Macbook with 16GB ram - slows down - who knows what spec they are using
 
Upvote 0

Forum statistics

Threads
1,224,351
Messages
6,178,058
Members
452,822
Latest member
MtC

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top