Replacing array listing formula (INDEX(MIN...) with more efficient solution

[lindstroem]

Been a while but now i've once more hit a calculation efficiency wall. It's always sad that the most elegant and compact formula decisions that often leads to higher calculation times.

File-description and end goal:
Inputdata consisting of up to 50.000 data rows contains a maximum of 5000 data points that is to be found and listed in a separate sheet. The datarow of interest looks like this and can be identified by that the lines of interest always start with "20". This dataline along with the cell beneath this is listed in the separate sheet. I have managed this with 1xarrayformula per row which results and a formula that then identifies the row number of the array formula result and adds +1 on the row to get the row beneath

Please help with improving calculation speed.
Does anyone have any tips on how to remove the need for the array-formula or making it faster? Creating a better identifier on the first page with relevant row data that can be used as direct reference in the second sheet?
Very much open for suggestions and input!

Simplified input data example (Sheet1)

200000844506 00000000013520003030201000476529
50 ANDERSSON
345345220202
200000844507 00000000013520003030201000476529
50 JOHANSSEN
345345220345345934202
200000844508 00000000013520003030201000476529
50 MARK
23492342342938

End goal - Finished list in the a new sheet (Sheet 2)

200000844506 00000000013520003030201000476529	50 ANDERSSON
200000844507 00000000013520003030201000476529	50 JOHANSSEN
200000844508 00000000013520003030201000476529	50 MARK

Array formula per line currently used
{=INDEX(SHEET1!$A$2:$A$50022,MIN(IF(LEFT(SHEET1!$A$2:$A$50022,2)="20",ROW(SHEET1!$A$2:$A$50022)-ROW(SHEET1!$A$2)+1),ROWS(SHEET1!$A$2:$A2)))}

 

[sandy666]

here is M-code step by step

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"raw", type text}}),
    Filter = Table.SelectRows(Type, each Text.StartsWith([raw], "20") or Text.StartsWith([raw], "50")),
    Conditions = Table.AddColumn(Filter, "50", each if Text.StartsWith([raw], "50") then [raw] else null),
    Fill = Table.FillUp(Conditions,{"50"}),
    Distinct = Table.Distinct(Fill, {"50"})
in
    Distinct

Example: example_lindstroem.xlsx

and here more about PowerQuery aka Get&Transform

• What is PowerQuery
• Getting started with PQ
• PowerQuery Help
• PowerQuery M reference
• PowerQuery add-in for XL 2010/2013, all above has PQ built-in
• General: Excel Help

 

[lindstroem]

I'm not seeing how your original formula can work, using MIN, the array is always going to return the first entry that starts with "20".
Using the AGGREAGTE method, the formula is progressive and will return subsequent matches as it is filled down.
Below, column C uses your original formula, column D uses my suggestion from earlier, column E uses that suggestion with a dynamic named range which will make it even more efficient.
Without this, the array processes all rows in the range Sheet1!A2:A50022, even if they are empty. With the dynamic range, if the last entry is in A2022 then only A2:A2022 will be processed in the array (a 96% reduction in effort).

Book12345 (version 1).xlsb
	A	B	C	D	E
1			Original	jasonb75	Dyanmic
2	200000844506 00000000013520003030201000476529		200000844506 00000000013520003030201000476529	200000844506 00000000013520003030201000476529	200000844506 00000000013520003030201000476529
3	50 ANDERSSON		200000844506 00000000013520003030201000476529	200000844507 00000000013520003030201000476529	200000844507 00000000013520003030201000476529
4	345345220202		200000844506 00000000013520003030201000476529	200000844508 00000000013520003030201000476529	200000844508 00000000013520003030201000476529
5	200000844507 00000000013520003030201000476529		200000844506 00000000013520003030201000476529
6	50 JOHANSSEN		200000844506 00000000013520003030201000476529
7	345345220345345934202		200000844506 00000000013520003030201000476529
8	200000844508 00000000013520003030201000476529		200000844506 00000000013520003030201000476529
9	50 MARK		200000844506 00000000013520003030201000476529
10	23492342342938		200000844506 00000000013520003030201000476529
11
Sheet1

Cell Formulas
Range	Formula
C2:C10	C2	=INDEX(Sheet1!$A$2:$A$50022,MIN(IF(LEFT(Sheet1!$A$2:$A$50022,2)="20",ROW(Sheet1!$A$2:$A$50022)-ROW(Sheet1!$A$2)+1),ROWS(Sheet1!$A$2:$A2)))
D2:D10	D2	=IF(D1="","",IFERROR(INDEX(Sheet1!$A:$A,AGGREGATE(15,6,ROW(Sheet1!$A$2:$A$50022)/(LEFT(Sheet1!$A$2:$A$50022,2)="20"),ROWS(D$2:D2))),""))
E2:E10	E2	=IF(E1="","",IFERROR(INDEX(Sheet1!$A:$A,AGGREGATE(15,6,ROW(Index_Range)/(LEFT(Index_Range,2)="20"),ROWS(E$2:E2))),""))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

Named Ranges
Name	Refers To	Cells
Index_Range	=Sheet1!$A$2:INDEX(Sheet1!$A:$A,MATCH("ZZZ",Sheet1!$A:$A))	C2:E10

Hi and thanks again for your follow up response.

There might be some error in my listed base formula if you get the same result over and over again when pressing Ctrl+Shift+Enter on them as I get the correct varied results when using them. If thats the case I'm sorry, but it works nevertheless However using your listed alternatives for improved efficiency is still of interest and I will follow up on this in the later part of the week

Thanks again!

 

[lindstroem]

Double post

 

[lindstroem]

here is M-code step by step

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"raw", type text}}),
    Filter = Table.SelectRows(Type, each Text.StartsWith([raw], "20") or Text.StartsWith([raw], "50")),
    Conditions = Table.AddColumn(Filter, "50", each if Text.StartsWith([raw], "50") then [raw] else null),
    Fill = Table.FillUp(Conditions,{"50"}),
    Distinct = Table.Distinct(Fill, {"50"})
in
    Distinct

Example: example_lindstroem.xlsx

and here more about PowerQuery aka Get&Transform

• What is PowerQuery
• Getting started with PQ
• PowerQuery Help
• PowerQuery M reference
• PowerQuery add-in for XL 2010/2013, all above has PQ built-in
• General: Excel Help

Thank you very much! Will try it out this week! Thanks for your time

 

[sandy666]

You are most welcome
Thanks for the feedback