Search results

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.

SPILL alternative: =SUBSTITUTE(DROP(IFERROR(REDUCE("",D1:D188,LAMBDA(x,y,VSTACK(x,IFERROR(TEXTSPLIT(y,CHAR(10)),"")))),""),1),"and ","")

With Excel 2024 or 365: =TEXTSPLIT(D2,CHAR(10))

Pleae explain the logic of your expected results - I don't get it.

Please attach a sample Excel workbook with yoyur requirements mocked up.

Is this an order or a request? It reads like the former ... You can do this with a formula: =LOWER(A1)

@shili12 I think you need to clarify to make your answer helpful - how do you use them and in what way are they essential?

Sorry - I don't understand the formula, so I can't help further.

Maybe this? =MIN(C6,IF(E2 <= E11, MIN(C6, CEILING(E2 / E9, 1) * C2 + D6), C6) + IF(E2 > E11, MIN(C6, CEILING((E2 - E11) / E9, 1) * C2 + D6), 0)) If that's not what you want, attach a workbook with some expected results mocked up.

OK - you know best. :)

If you don't want the 0 result, use this: =DROP(REDUCE(0,H7:H10,LAMBDA(a,b,VSTACK(TAKE(a,1),a+b))),1)