Search results

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.

Please let me know how you got on with this - some acknowledgement would be nice. Thanks.

Seems not, as you have not told us about this: https://www.mrexcel.com/board/threads/consolidation-formula.1266890/

On the Daily workseet, unhide hidden columns and change the lookup grid to this: AliGW on MS365 Beta Channel (Windows 11) 64 bit N O P 5 Important i 1 6 Anniversary ¦ 2 7 Holiday = 3 8 Vacation = 4 9 Birthday = 5 10 0 6 Sheet: Daily Then update...

Can you please attach the template you have downloaded?

Cross-posted here: https://forums.excelguru.ca/threads/formula-for-consolidation.11978/

No, sorry - if you specifically did not want the LAMBDA function to be used, you should have said so at the start. I don't see why it should be an issue when ONLY the ranges in the first line will ever need changing: =LET(t,TEXTJOIN(" ",,J3:J5&I3:I5),c,C3:C32,de,D3:D32&E3:E32...

Here is a 365 solution: =LET(t,TEXTJOIN(" ",,J3:J5&I3:I5),c,C3:C32,de,D3:D32&E3:E32, f,FILTER(c,ISNUMBER(FIND(de,t))), SORT(UNIQUE(FILTER(f,BYROW(f,LAMBDA(r,SUMPRODUCT((f=r)*1)=3))))))

Which? If it has to work with both, them 365 functions such as FILTER can't be used.

GROUPBY is still only available to Office Insiders, I believe.

Let us know.

Have a look at MINIFS and MAXIFS. e.g. =MINIFS(B1:B8,B1:B8,"<"&0) (biggest decline)

I suggest yo copy everything into a new file. I am not, for one, going to open a huge file from a .RAR - sorry.

Copied from ExcelGuru: BYROW and WRAPROWS are in Excel 2024 and 365, but NOT in 2021, hence the #NAME? error.

I'd hardly call it 'pushing the boundaries'! It's nothing compared to what some people on other forums are doing with it, @Peter Bartholomew, but I am enjoying developing my use of it at my own pace. Thanks for the link.