Require help with match, query and arrays


#1

Experts out there good morning

The red ID is:

=ArrayFormula(FILTER(Datalink!$A$2:I, MATCH(Datalink!$A$2:A, $M$2:M, 0))) 

and the blue ID is:

=QUERY(Datalink!L:T,"SELECT L,N,O,P,Q,R, Max(M), SUM(S) + SUM(T) WHERE L IS NOT NULL GROUP BY L,N,O,P,Q,R ORDER BY L ASC LABEL (L) 'MAP ID', SUM(S) + SUM(T) 'Build Ttl', Max(M) 'Timestamp Finished' ",1)

The red is an auto run ID:

=ArrayFormula(if(B2:B<>"",row(A2:A)-1,"")) 

and the red ID is a match to Red ID:

=iferror(INDEX($A$2:A,MATCH(M2,IF(($C$2:C=N2)*($D$2:D=O2)*($E$2:E=P2)*($F$2:F=Q2)*($G$2:G=R2),$B$2:$B),1)),"")

Common tell me that the blue ID be more than the Red ID. But why in this I filter blue ID base on Red ID and return a result that the Red ID is less than blue ID ?

This is the filter formula:

=ArrayFormula(FILTER(Datalink!$A$2:I, MATCH(Datalink!$A$2:A, $M$2:M, 0)))

#2

@desmondlee what are you trying to accomplish here?


#3

Hi Bobbly
I copy 2000 rows of this formula and it’s make the sheet become slow and sluggish. I want to achieve speed n no sluggish sheet.

Thanks for that arrayformula. I will try it next week when I am back from holiday.


#4

Alright, thanks!

@eugenia any further optimisation ideas?


#5

The Red font is automate ID generated in the response when the operator start a task and fill up the form called Work Order Start form. After she completed the task she needs to fill up the form called Work Order Finish Form. This will generated the Blue Font ID base on above condition I set. The is why the Blue Font ID is always less than the Red Blue ID because operator forgot to fill up the finish form.


#6

You can try on this file. Eugenia try that on column M but that is not the result I am looking for. I am looking for the result at column L. But I need to copy all the way down 2000 over rows and that makes it so slow.


#7

This is the link of the file. I hope there is some genius to help me
https://docs.google.com/spreadsheets/u/0/d/1K6d26_Rxl3nvJRYCL4LPh4gUMZ6URpo3kTsrhmV69fA/edit?usp=sharing


#8

Thanks for sharing that file. Are the values in the columns L and M supposed to be the same?

Right now they don’t always match.

image


#9

Hi Bobby
Column L is my formula and is the result I want. Column M is Eugenia arrayformula and is not what I want.

My formula at column is too slow when I copy all the way down 2000 row.


#10

Let’s get some help from the Stackoverflow magicians!


#11

For anyone reading this, master user Jimmy has this suggestion:

I just wanted to quickly mention, that if that user needs to powerfully “filter” on some multiple criteria within an array or arrays, don’t forget to try playing around with SUMPRODUCT formula too, although that is obviously more so going to help facilitate a mathematical result and perhaps not just an index/match result…