Search results

I don't think you really understood my point. I am very well aware of the German 'z' (you know very well that I am fluent in German). I can see nothing offensive here, intended or otherwise. I am pretty certain that this is the member's real name. My reaction has nothing to do with leniency or...

NAZI is only a part of the user name. It is quite possible that this member has a real name that begins with Naz and is habitually shortened to Nazie. Furthermore, the 'z' may not be pronounced 'ts', but as an Anglicised 'z'. I think your reaction is most probably over-sensitive.

You're welcome. :)

By the way, I am not a Sir - do I really look like a man??? Try this: =IFERROR(FILTER(Data!$I$4:$J$10001,(LEFT(Data!$A$3:$A$10000,20)=LEFT(C18,20))*(Data!$G$4:$G$10001=B18)),"")

Please provide a desensitised sample workbook. Which version of Excel is this for?

Any chance you could explain your requirement in plain English? What happened when you tried to adapt the formula I gave you? Please don't drip feed your requirements.

Can't answer that. then? ;) In E18 copied down: =IFERROR(FILTER(Data!$I$4:$J$10001,(Data!$A$3:$A$10000=C18)*(Data!$G$4:$G$10001=B18)),"")

And your answer to the other question?

Why? In what way? Which version of Excel?

They could do, but it would require a rethink of the results layout. I have set the formula to use the first of the two. (TAKE). At no point has the OP made it clear that both results should be shown (they have been very unwilling to properly clarify the expected results throughout, which has...

The formula doesn't use a named range - the ranges are defined within the formula. It's not a half measure. :confused: Glad to have helped. :)

It's like this: =LET(reg,VSTACK(start:finish!K2:K54050), date,VSTACK(start:finish!M2:M54050), sum_insured,VSTACK(start:finish!U2:U54050), DROP(REDUCE("",TOCOL(A3:A54050,1),LAMBDA(x,y, VSTACK(x,TAKE(FILTER(HSTACK(TEXT(date,"Mmmyyyy"),sum_insured),reg=y,"Not Found"),1)))),1)) What is hard about...

No reply ... Is there still an issue here?

Done that - it's slow, but it works. What is the problem? AliGW on MS365 Beta Channel (Windows 11) 64 bit ABCD 2REGNOMONTH INSPECTEDData is in this monthsum insured 3KCP146MAug-24Aug20240.00 4KDM718DSep-24Aug2024850,000.00 5KDL922BAug-24Aug20240.00 6KDK276TAug-24Aug20240.00...

OK - I am looking at your workbook. What is the likelihood of ANY of the month sheets having one million rows of data?

You need to create a sample workbook using some of the data from the real workbook, but desensitised. Remove data from columns that are not needed in the formula. The sample workbook needs to show the problem.

I can't troubleshoot a workbook I have no access to. I can't troubleshoot from screenshots. Where it returns "Not Found", there is no match. Provide a workbook with that error and I'll see if I can tell you why, but if there is no exact match, then it's your data that isn't consistent. As I...

You need to take more care. You have messed up the end of the formula. Look: =LET(reg,VSTACK(start:finish!K2:K100), date,VSTACK(start:finish!M2:M100), sum_insured,VSTACK(start:finish!U2:U100), DROP(REDUCE("",TOCOL(A3:A1000000,1),LAMBDA(x,y...

Why do you keep on repeating this? We understand what you are trying to avoid, and my solution should (and hopefully will) make this unnecessary.

I got a like - does that mean that post #11 worked for you? If so, then an acknowledgement here in the thread would have been nice.

The attachment does not contain my formula, so I have no way of checking it for you. In what way is it NOT doing what you want? One guess: =LET(reg,VSTACK(start:finish!K2:K100), date,VSTACK(start:finish!M2:M100), sum_insured,VSTACK(start:finish!U2:U100)...

I think this works. Remove ALL expected results from C3 onwards, then in C3 followed by ENTER: =LET(reg,VSTACK(start:finish!K2:K100), date,VSTACK(start:finish!M2:M100), sum_insured,VSTACK(start:finish!U2:U100), DROP(REDUCE("",TOCOL(A3:A100,1),LAMBDA(x,y...

This uses column C instead: =MAP(A3:A17,B3:B17, LAMBDA(x,y, LET(s,LOWER(C3), k,INDIRECT("'"&s&"'!K:K"), u,INDIRECT("'"&s&"'!U:U"), XLOOKUP(x,k,u,"")))) It returns no matches in June. It's not my job to work out why matches aren't happening - that's up to you.

I have standardised your tab names as mmmyyyy. This in F3: =MAP(A3:A17,B3:B17, LAMBDA(x,y, LET(s,SUBSTITUTE(LOWER(y),"-","20"), k,INDIRECT("'"&s&"'!K:K"), u,INDIRECT("'"&s&"'!U:U"), XLOOKUP(x,k,u,"")))) You have a couple of rows that don't match - check for leading and trailing spaces there.

Building on what @Peter Bartholomew has already done, this in L3 copied down: =LET(n, QUOTIENT(COUNTA(GridPointTbl[@]), 2), k, SEQUENCE(n, 2), list, INDEX(GridPointTbl[@], k), distinct, UNIQUE(list), unique, UNIQUE(list, , 1), na, ROWS(distinct), nb, ROWS(unique), r, {1,1} * SEQUENCE(na + nb)...