Auto populate text in sequence with date and time

Administrative Note:

Is your forum profile showing the version of Excel that you need this to work for?

Members will tailor the solutions they offer to the version of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this.

The three most recent versions of Excel are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the release number in your profile (e.g. MS365 Version 2211). This is in the About Excel section further down the Account page.

Thanks.

Set using cell B2:

CF formula for green - =AND($B2<>0,$B2<1300)
CF formula for red - =$B2>=1300

Both rules applied to: =$A$2:$A$366

I have no idea what you are trying to do with the sequencing - intsead of just bumping the thread, try adding a clearer, more detailed explanation.

Just saw that you changed your mind!

Set using cell B2:

CF formula for green - $B2=0
CF formula for yellow - =AND($B2>0,$B2<1300)
CF formula for red - =$B2>1300

All rules applied to: =$A$2:$A$366

In the absence of any attempt to explain what you are trying to do with the sequencing, and not following your examples, I can't help with anything else. You need to give a clear explanation of what you are trying to achieve.

This:

Would like for this sheet to auto populate in sequence like the examples above

is NOT an explanation. What is the rationale/logic?

First, clear everything in the Stops columns, then in B2 copied down:

Please Login or Register  to view this content.

I've just updated the solution - you will need to look at the new attachment.

I know you have seen this, but you probably missed that I updated the formula and attachment, so you haven't seen the latest version.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

No - please attach a sample workbook showing the issue.

I suspect there's something different about the real data's layout that is cuasing the formula to fail. Unless all the columns match exactly with the sample you provided, it won't work.

I'll have a look later - on the face of it, it should work, so I need to investigate.

Give this a go:

Please Login or Register  to view this content.

or (shorter, but same end result):

=LET(Dat,Revenue!$D$6:$I$1500,A,FILTER(Dat,Revenue!$C$6:$C$1500=A2),B,SUM(INDEX(A,,1)),C,CHOOSECOLS(A,6,1,3),D,TRANSPOSE(BYROW(C,LAMBDA(x,TEXTJOIN(" / ",,INDEX(x,,1),"$"&INDEX(x,,2),TEXT(INDEX(x,,3),"hh:mm am/pm"))))),IFERROR(HSTACK(B,D),0))

That doesn't include the entries from the Estimate sheet, Glenn, so no wonder it's shorter!

or this (which calaculates EVERYTHING in one go... no copy/paste):

Formula:

Please Login or Register  to view this content.

Does THAT include the items from the Estimate sheet???

Mehh! Insufficient coffee here!

Just ignore...

An all-in-one spill array would be lovely, but I just can't get my head around BYROW and LAMBDA to be able to work it out myself. I am really struggling with that.

Which entries in the Estimate sheet?? It's blank??

No, it isn't blank - see the references in my formula.

It's not a great sample... with so many blank rows, but I DO see it now... the one lonely non-blank row.

There were more in the original sample.

Here it is... All in one cell. Delete ALL expecte dresults first.

FWIW:

Formula:

Please Login or Register  to view this content.

Not quite. Entries from the Estimates sheet need to be in the format: Place / ESTIMATE / Time (with the word 'estimate' hard coded in).

Driving. Back in an hour.

Got it!

Please Login or Register  to view this content.

I shall digest this now.

Yup. That should do it. Hopefully the OP will appreciate the responses!

I am sure he will - he has done so far. I need to scrutinise your formula now to understand better the LAMBDA and BYROW bits.

@Glenn - can you explain how Excel knows what x and y are in the LAMBDA? This is something I'm really struggling to get my head around. I've loooked at the online syntax explanations, but still haven't managed to suss it out.

There were two LAMBDAs in use in that one:

=IFERROR(DROP(REDUCE(0,A1:A366,LAMBDA(y,z,VSTACK(y,
LET(Rv,Revenue!A6:I2000,
Es,Estimate!A7:Q2000,
A,VSTACK(CHOOSECOLS(Rv,3,4,6,9),CHOOSECOLS(Es,16,4,17,9)),
B,SORTBY(A,INDEX(A,,1),1,INDEX(A,,3),1),
C,FILTER(B,INDEX(B,,3)<>""),
D,FILTER(C,INDEX(C,,1)=z),
E,SUM(INDEX(D,,2)),
F,TRANSPOSE(BYROW(D,LAMBDA(x,TEXTJOIN(" / ",,INDEX(x,,4),IF(INDEX(x,,2)="","ESTIMATE","$"&INDEX(x,,2)),TEXT(INDEX(x,,3),"hhmm AM/PM"))))),
IFERROR(HSTACK(E,F),0))))),1),"")

in red... one associated with REDUCE and one associated with BYROW

in green their respective parameters are defined... and

in blue they are used. I'm >>>beginning<<< to understand when to use BYROW/BYCOL, MAP and REDUCE... but still haven't got the faintest idea when/how to use MAKEARRAY. It's akin to examining sparrow's entrails for future portents... or double entry book-keeping, and other arcane practices...

Ah, this makes a bit more sense - I was looking inside the LAMBDA for the definition - doh!

Have you found any recent online tutorials that help at all? There still seems to be very little out there.

Not for LAMBDAs in the way that everyone uses them here... with array functions. There are a few... but they are utterly trivial.

There are also a few for uding LAMBDAs as pseudo-UDFs... but I found the confusing.

The best sources are, in no particular order, Bo Ry, TMS, FLUFF13, Hans Douwe, etc, etc.

Yes, I know about those here and I meant LAMBDA. The problem is that those offering such solutions here are rarely asked to explain them, and trying to use Evaluate Formula with a LAMBDA is not very easy.

I was hoping you might have found something on LAMBDA in formulae. Never mind.

What do you mean 'not working'? I corrected your CF rules in the original sheet.

Working fine here:

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	A	B
2	01/01/2023	$ -
3	02/01/2023	$ -
4	03/01/2023	$ -
5	04/01/2023	$ -
6	05/01/2023	$ -
7	06/01/2023	$ -
8	07/01/2023	$ -
9	08/01/2023	$ -
10	09/01/2023	$ -
11	10/01/2023	$ -
12	11/01/2023	$ -
13	12/01/2023	$ -
14	13/01/2023	$ -
15	14/01/2023	$ -
16	15/01/2023	$ -

Sheet: Schedule

Glad to have helped, but please thank Glenn as well who came up with the all-in-one option.

You will need to extend the range of the CF rules by one, actually.

Please mark the thread as SOLVED.