Single Array, Multiple Criteria, Single Column Output

Hi. Before we get going on this one...

Is your forum profile showing the Excel PRODUCT that you need this request to work with?

The best solutions often rely on knowing WHICH Office PRODUCT (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date. If you aren't sure, in Excel go to File/Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this.

The three most recent Excel PRODUCTS are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the Version number in your profile (e.g. MS365 (PC) Version 2211). The version number is in the About Excel section further down the Account page.

Cheers,

Glenn.

Originally Posted by Glenn Kennedy

Hi. Before we get going on this one...
Is your forum profile showing the Excel PRODUCT that you need this request to work with?
The best solutions often rely on knowing WHICH Office PRODUCT (Excel, NOT Windows) that you have.
Cheers,
Glenn.

Thanks very much for getting back to me so quickly Glenn, apologies I didn't reply sooner I was awaiting an email notification which never came so I manually came back to see if anyone had replied.

Sorry that my profile didn't no include the excel product I'm using, I have now amended it and included it here for ease too - I'm using MS365 (PC) Version 2208.

Originally Posted by XLent

a few options... using your FILTER approach, you would need to return a scalar for your filter clause, so an "inline array" like {a,b} won't work as you will get a 1 x 2 result

old school OR approach i.e. c/o +

=MID(FILTER(A:A,ISNUMBER(SEARCH("Multirec ended",A:A))+ISNUMBER(SEARCH("Warning",A:A))),12,8)+0

assumption that terms are mutually exclusive

Cheers for the quick reply and sorry I missed it, I was waiting for an email notification which never arrived but I came back to the forums anyway hoping for the best.

I really appreciate your solution but unfortunately it only half fixes my issue because the INDEX and SEQUENCE I had in my original formula are so that the output array has a stopping point because the data in column A is 20,000+ rows and I only need the first 100 ish.

Is there anyway it can be re-written to include the INDEX and SEQUENCE (or something that has the same effect) and still keep you '+' solution?
Also please could you explain the '+0' at the end of the formula?

Thanks
Eric

Originally Posted by EricDonk

...unfortunately it only half fixes my issue because the INDEX and SEQUENCE I had in my original formula are so that the output array has a stopping point because the data in column A is 20,000+ rows and I only need the first 100 ish.

Is there anyway it can be re-written to include the INDEX and SEQUENCE (or something that has the same effect) and still keep you '+' solution?
Also please could you explain the '+0' at the end of the formula?

Yes, there are a few, you could apply an outer INDEX with SEQUENCE, as you had before, e.g:

=INDEX(MID(FILTER(A:A,ISNUMBER(SEARCH("Multirec ended",A:A))+ISNUMBER(SEARCH("Warning",A:A))),12,8)+0,SEQUENCE(A2))

That being said, there are lots of other alternatives, the above was simply to illustrate the traditional means of creating an OR / scalar for FILTER, @FlameRetired has illustrated the same c/o more advanced MMULT which is certainly the more elegant , and scalable, option of the two, and I would certainly echo comments about entire column references.

In terms of the +0 .. it is simply a method to coerce a string to number, e.g. a MID {string} return of "12:00:00" would, once coerced, become 0.5 (datetime values in XL are integer.decimal where 1=24 hours); @flameretired used the double unary to effect the same, that has always been considered to be the most efficient method for coercion however, these days you would never really notice the difference so it's really down to personal preference and whichever method you find most intuitive (some prefer the VALUE function).

EricDonk welcome to the forum.

I have an additional solution. I was waiting for response to Moderator Glenn Kennedy's request.

Originally Posted by FlameRetired

EricDonk welcome to the forum.

I have an additional solution. I was waiting for response to Moderator Glenn Kennedy's request.

Thanks FlameRetired and sorry to miss Glenn's reply at the time but I have now amended my profile to include the Excel product which I'm using. (MS365 PC Ver 2208) and look forward to a potential solution you might have?

PS. Sorry to reply 3 seperate times to the various people trying to help - I was going to make one super long post mentioning the various people but was worried it would get confusing. However I apologise if multi-posting within a thread goes against the forum rules.

In D2 find this formula. It utilizes the whole column reference you used. This is not recommended. I average time for this formula was around 0.5 seconds.

Formula:

=--MID(FILTER(A:A,MMULT(--ISNUMBER(SEARCH({"multirec ended","warning"},A:A)),{1;1})),12,8)

In the second formula I used a Dynamic Named Range (DNR). It's defined by formula and resizes automatically to fit your data. You will find it in Name Manager as Data_Column. Its definition is:

Formula:

=$A$3:INDEX(Example!$A:$A,MATCH("zzzz",Example!$A:$A))

Then replacing A:A with that DNR name in E2:

Formula:

=--MID(FILTER(Data_Column,MMULT(--ISNUMBER(SEARCH({"multirec ended","warning"},Data_Column)),{1;1})),12,8)

It averages just over 9/1000 of a second.

Except in the rarest of instances we recommend against whole column references. The DNR above is one of those exceptions.

If you want to limit the amount of results given from the array you can also use the function TAKE, in the below example it is set to 6:

Formula:

=LET(r,A3:A34,TAKE(VALUE(MID(FILTER(r,(ISNUMBER(SEARCH("multirec ended",r))+(ISNUMBER(SEARCH("warning",r))))),12,8)),6))

Originally Posted by CheeseSandwich

If you want to limit the amount of results given from the array you can also use the function TAKE.

Thank you ever so much for bringing the TAKE formula to my attention.
It had previously taken me a long time on google to work out I could even use INDEX and SEQUENCE to limit my array results, but the TAKE function seems to be a much easier way so I'll have to have a play around with it.

Thanks again fellow Kentian. (I'm from near Tunbridge Wells)

Originally Posted by FlameRetired

First formula utilizes the whole column reference you used. This is not recommended. I average time for this formula was around 0.5 seconds.

In the second formula I used a Dynamic Named Range (DNR). It's defined by formula and resizes automatically to fit your data. You will find it in Name Manager as Data_Column.

Then replacing A:A with that DNR name in the third formula, it averages just over 9/1000 of a second.

Except in the rarest of instances we recommend against whole column references. The DNR above is one of those exceptions.

Thanks so much for getting back to me FlameRetired. Your solution is amazing and I'm very thankful for the various steps and explanations you gave, although my primitive brain still has a few questions if I may?

Firstly I'm not familiar with the MMULT formula, are you able to explain what it does and how you are using it in my particular example?

Also you mentioned a DNR, which I'm was not familiar with either but having read the formula I understand, but could I clarify... Will it adapt perfectly when data in pasted into column A. There will be times when column A3 onwards is completely blank and then I paste around 20,000 to 100,000 rows. However there will also be times I paste more data into column A, so can the DNR handle that or does it only work when manually typing data into Column A?

Next you said "Except in the rarest of instances we recommend against whole column references. The DNR above is one of those exceptions." Does that mean this is an exception and I shouldn't use a DNR?

Finally are you able to limit your formula so the output array results only display the number given in A2 - The reason for this is because I often have over 500 results but only need the first 100 or so.

Thank you once again for the brilliant solution so far.

Originally Posted by EricDonk

Thanks so much for getting back to me FlameRetired......

1.) Firstly I'm not familiar with the MMULT formula, are you able to explain what it does and how you are using it in my particular example?

2.) Also you mentioned a DNR, which I'm was not familiar with either but having read the formula I understand, but could I clarify... Will it adapt perfectly when data in pasted into column A. There will be times when column A3 onwards is completely blank and then I paste around 20,000 to 100,000 rows. However there will also be times I paste more data into column A, so can the DNR handle that or does it only work when manually typing data into Column A?

3.) Next you said "Except in the rarest of instances we recommend against whole column references. The DNR above is one of those exceptions." Does that mean this is an exception and I shouldn't use a DNR?

4.) Finally are you able to limit your formula so the output array results only display the number given in A2 - The reason for this is because I often have over 500 results but only need the first 100 or so.

Thank you once again for the brilliant solution so far.

For 1.) the MMULT function does what is called matrix multiplication. It multiplies columns by rows. (Please see the Excel help file for the details.) This part generates a 2 dimensional array 2 columns wide by over 1 million rows deep. Note the column separator “,” in {"multirec ended","warning"}. That’s what defines the two column array. The rest of that part SEARCHes the entire 1 million rows of column A:A for those two strings and returns numbers if found. Testing all two columns of 1 million rows whether they are numbers returns a massive number of TRUE/FALSE. The leading “—” (called a double unary) converts those T/F to their underlying numeric values of 1/0.
--ISNUMBER(SEARCH({"multirec ended","warning"},A:A))

Then the final argument in MMULT is a forced array {1;1} (recall from the help file that the number of columns in the first argument must equal the number of rows in the last argument. We know the number of columns in the first argument because of the number of search strings. MMULT sums each of those products with an resulting array of over 1 million 0s, 1s or even 2s. This array serves as the TRUE/FALSE test for the FILTER function as any non-zero number is deemed TRUE in Excel.

That filtered array is passed to MID which you clearly understand.

2.) DNR is short for Dynamic Named Range. As far as my experience goes with DNRs it adding/pasting/deleting shouldn’t make a difference. Although when it is completely blank there could be problems. In that case the DNR definition might need a default “dummy range” to hold its place in Name Manager.

For 3.) I DO recommend using this DNR. The exception is to the do-not-use-whole column references ‘rule’. You can use it here because of the approximate match type used.

4.) Yes. Just replace the $A:$3:INDEX($A:$A,match(“zzz”,$A:$A)) with $A$3:INDEX($A:$A,$A$2). That way when you change numbers in A2 the last row in the DNR will be whatever you put in A2. Just be sure to make allowances for the offset in rows (probably add 3 to whatever you want that to be.)

@FlameRetired, your incite and explanation is above anything I could have asked for. I clearly have a lot to learn but the MMULT is somewhere to start so thanks for getting the ball rolling with you excellent description and all the outstanding help you have provided.

I will also be sure to to use named ranges in the future, instead of full column references - To clarify if I want to add a default range to a DNR am I right to add an IF formula to the beginning of the named range; Something like

Formula:

=IF(A3="",$A$3:$A$4,$A$3:INDEX(Example!$A:$A,MATCH("zzzz",Example!$A:$A)))

Thank you for the tweak to the DNR to limit the amount of rows it searches through, however I was actually looking to amend the output array results rather than how many rows to search (Because the amount of rows can vary per account but I will usually want just the first 100 array results.) That said I was able to piece your formula together with the TAKE function (a suggestion by CheeseSandwich) to get the desired outcome:

Formula:

=Take(--MID(FILTER(Data_Column,MMULT(--ISNUMBER(SEARCH({"multirec ended","warning"},Data_Column)),{1;1})),12,8),$A$2)

I wouldn't have worked this out without you so my hat it tipped to you and cheers again.

This forum and you in particular FlameRetired have made me really really happy once again, you are a superstar and after many stressful hours working on a solution you have sorted my problem in a matter of moments. I am in awe of everyone's skills here but thanks again to you for everything, it means a great deal to me and I truly appreciate your time. Everything you have done is very much appreciated and I will be adding Rep/ marking this as solved.

Originally Posted by XLent

Yes, there are a few, you could apply an outer INDEX with SEQUENCE, as you had before,

That being said, there are lots of other alternatives, the above was simply to illustrate the traditional means of creating an OR / scalar for FILTER, FlameRetired has illustrated the same c/o more advanced MMULT which is certainly the more elegant, and scalable, option of the two, and I would certainly echo comments about entire column references.

In terms of the +0 .. it is simply a method to coerce a string to number, e.g. a MID {string} return of "12:00:00" would, once coerced, become 0.5 (datetime values in XL are integer.decimal where 1=24 hours); flameretired used the double unary to effect the same, that has always been considered to be the most efficient method for coercion however, these days you would never really notice the difference so it's really down to personal preference and whichever method you find most intuitive (some prefer the VALUE function).

Thanks for the response XLent - I'm silly for not thinking to add the index and sequence back on myself.

If I'm honest I was worried your formula would default to looking through all the data in Column A for only the first phrase and then re-look through Column A for only the second phrase. Then compile it into a single array, meaning the output result would provide the timings out of order. However this wasn't the case and I'm please to say your formula does as intended so thank you very much for the solution that adds onto what I was trying.

Cheers also for explaining the '+0', I am more familiar with '--' but appreciate you taking the time to let me know because I had no idea there was another way of doing this.

Finally I've taken on board that there are other clearer/ more efficient ways to present my formula, such as the MMULT and DNR mentioned by FlameRetired - I'll have to get familiar with them both having never used either. I used to think I was pretty good at Excel but the knowledge you all possess is amazing. I am again grateful to this amazing forum and the people on it. Cheers.

I will also be sure to to use named ranges in the future, instead of full column references - To clarify if I want to add a default range to a DNR am I right to add an IF formula to the beginning of the named range; Something like
Formula: [Select Code] copy to clipboard

=IF(A3="",$A$3:$A$4,$A$3:INDEX(Example!$A:$A,MATCH("zzzz",Example!$A:$A)))

Yes.

You are welcome. Glad to help. Thank you for your kind words, added rep and marking your thread Solved.

Thank you for the confirmation @FlameRetired, the DNR has certainly sped things up. So much so in fact that I've decided to update my spreadsheet with as many dynamic named ranges as possible because my spreadsheet references 50,000 rows a lot of times when it doesn't really need to.

Am I also right in assuming I can amend the 'A3' value in your below formula, to change the starting cell of the dynamic named range to 'A12', so that the DNR starts at A12 and goes until the final row of data?

Formula:

=$A$3:INDEX(Example!$A:$A,MATCH("zzzz",Example!$A:$A))

If so do you know why I'm facing a circular reference when I minus one DNR from another DNR? I ask because I'm looking to complete the following array calculation in D12 onwards using DNR's instead... =B12:B50000-C12:C50000
To start I have created DNR's for B12:B50000 and C12:C50000 using the formula above (tweaking it for Column B and Column C and changing the 3 to a 12) they are named End_Time and Start_Time respectively.

Then I’ve re-written the formula in D12 so it's now... =End_Time-Start_Time but it produces a circular reference and can't calculate. Do you have any idea why it would do that or could you perhaps show me how to minus one DNR from another? (I can start a new post if you would prefer?)

________________________

EDIT - I'm starting to see why the formula doesn't work and that's because it's not dynamically adjusting to the last cell of data (I thought it might be what we talked about in an earlier post regarding the fact that the data in these cells is only displayed after the paste in Column A? - However this isn't the case and for some reason the dynamic named range is only displaying 2 cells B12 and B11 - Would you like me to mock up some data so you can better see?)

Yes. It is almost always more helpful to upload a workbook sample.

No worries I thought that would be the case and have tried to re-create the issue as best I can.
Unfortunately it's not showing the exact circular reference error that my real spreadsheet is, but it is displaying a similar error so I'm hoping a fix here translates to a fix there too.

Thank you in advance and sorry to keep asking questions.


EDIT - To anyone that ends up here later... I was able to find a solution to my problem it was to do with the formula for a DNR in which it searched for "zzz" however when the column is formatted to end with a number you need to replace "zzz" with 999 (without the quotation marks)

It was creating a circular reference because the "zzz" finds the late cell with text which in my case was the header cell and therefore made the reference include some of the formula calculations underneath, so changing to 999 within the Dynamic Named Range fixed the problem.