Search results

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)...

Which version of Excel? Your workbook full of real E-mail addresses and other personal details. This may be in breach of data protection laws in your country. This is a public forum.

2021 doesn't have LAMBDA or BYROW - they are in 2024 and 365. I worked on this for a couple of hours on and off yesterday and couldn't find an automated way without LAMBDA. There are solutions, but they'll be very clunky. Looking at a VBA option might be the optimal route here (but I can't...

The attached uses PowerQuery (Get & Transform) to copy the table with the columns in the right order. I have then added two slicers to allow you to filter the data.

In A18 and ENTER: =CHOOSECOLS(FILTER('Daily Transaction'!A2:G301,('Daily Transaction'!C2:C301=C4)*('Daily Transaction'!G2:G301=F4)),1,7,2,3,4,5,6) Will work in Excel 2024 or 365.

I don't see any logic, either. Don't know where Person A etc. are coming from in the QC column. Baffling.

You're welcome. :)

OK - then my solution will work for you.

If you have 365, in K5 followed by ENTER: =DROP(REDUCE("",J5:J8,LAMBDA(x,y,VSTACK(x,BYCOL(K4:W4,LAMBDA(c,COUNT(FILTER(E5:E130,(F5:F130=y)*(ISOWEEKNUM(E5:E130)=--SUBSTITUTE(c,"W",""))))))))),1)

Which version of Excel are you using?

OK - with 2016 it's not going to be as straightforward. I'll have a think and get back to you, maybe tomorrow.

OK - the monthly calendar is not designed to show items from the daily list, but that can be done. However, I need to know your Excel version. I can also explain to you how to set up the conditional formatting, but I am not going to do it fo you for all 14 colours. How does this sound?

What do you mean about the note not appearing on the calendar? Colour-wise, you haven't set up the conditional formatting for the colours, so that's no surprise.