Auto populate individual cells based off cell values from data table with NO VBA

[Stomli]

Hi Forum,

This is my first time posting. I am usually able to find my answer to other questions by looking through the forums but I haven't been able to find an answer for this issue I'm having. So I am reaching out to the experts on here. Please and Thank you!

I need to auto populate dates for specific users based on cell values inside a table. I would like to implement them with dynamic array formulas so this can scale and update based on new reports. Also, I can't do this with macros or VBA since the end users can't work with them.

First thing I would need to know is how I would go about is how to make duplicate cells based on values (numbers) in a cell. I have been looking up some nested If functions, but I am not sure how they could work for a multiple month table with blanks in them.

The other thing I need is a method on how to tie these duplicate cells to a unique user I.d. while omitting blank fields for months without changes. I was thinking after the duplication if figured out that a sequence formula could just drag the duplicated dates down and use a filter() formula to match them to the right division.

I could develop the back end in a helper column/tab. If this is too difficult for one direct solution, please suggest methods or formulas that I could use to solve this.

Thanks for your time.

S

 

[kweaver]

I don't understand your 2nd request. How about this for the first?

062023 Misc.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N
2	Div	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
3	A	0	2	0	0	1	0	1	0	0	4	0	0
4	B	0	0	0	3	0	0	0	2	0	0	0	0
DateChg

Cell Formulas
Range	Formula
C3:N4	C3	=SUM((--($B$8:$B$20=$B3))*(--MONTH($D$8:$D$20)=COLUMN()-2))

 

[lrobbo314]

kweaver, I think the portion you gave formulas for is being hard coded in. I think Stomli wants to generate the table below with the table above. I've already tried and reverted to PowerQuery to at least get something that works.

But I'm sure they want a formula do generate at least the date column, but probably the division column as well. Haven't been able to get a formula to work yet.

 

[Stomli]

Thanks for your explanation Irobbo314. Yes, that is what I want for the dates column the division is a xlookup that already links the IDs So I don't need any help on that column but I need to get the dates to follow downward from the top of the user id. So I need 2 Feb dates for the 1st 2 users then a May date for the next user. I would need to input these 1 after the other taking into account the months that don't have any dates in them and also the division for each user Id.

 

[Stomli]

Also sorry for the screenshot. My company has my excel locked down so I can't get the add-in.

 

[kweaver]

With several helpers, this does it, I think...but it's a little more effort than really desired.
I haven't figured out a LET function for it or another shorter way.

This might temporarily get you over a hump if there's not too much to handle.

062023 Misc.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N
1		1	2	3	4	5	6	7	8	9	10	11	12
2	Div	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
3	A	0	2	0	0	1	0	1	0	0	4	0	0
4	B	0	0	0	3	0	0	0	2	0	0	0	0
5
6
7	Div	User	Date		A				B
8	A		2/1/2024		2	2	1	2	3	4	1	4
9	A		2/1/2024		1	5	3	2	2	8	4	4
10	A		5/1/2024		1	7	4	5			6	4
11	A		7/1/2024		4	10	5	7				8
12	A		10/1/2024				9	10				8
13	A		10/1/2024					10
14	A		10/1/2024					10
15	A		10/1/2024					10
16	B		4/1/2024
17	B		4/1/2024
18	B		4/1/2024
19	B		8/1/2024
20	B		8/1/2024
DateChg

Cell Formulas
Range	Formula
C1:N1	C1	=SEQUENCE(,12)
D8:D15	D8	=DATE(2024,I8#,1)
F8:F11	F8	=TRANSPOSE(FILTER(C3:N3,C3:N3<>0))
G8:G11	G8	=TRANSPOSE(FILTER(C1#,$C$3:$N$3<>0))
I8:I15	I8	=XLOOKUP(SEQUENCE(SUM(F8#)),H8:H12,G8:G12,,-1)
J8:J9	J8	=TRANSPOSE(FILTER(C4:N4,C4:N4<>0))
K8:K9	K8	=TRANSPOSE(FILTER(C1#,$C$4:$N$4<>0))
M8:M12	M8	=XLOOKUP(SEQUENCE(SUM(J8#)),L8:L10,K8:K10,,-1)
L8,H8	L8	=1
L9:L10,H9:H12	L9	=L8+J8
D16:D20	D16	=DATE(2024,M8#,1)
Dynamic array formulas.

 

[DanteAmor]

Hello @Stomli .

Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.


Try the following formulas.

In cell B8:

=MID(CONCAT(REPT(B3:B4,MAP(C3:N4,LAMBDA(m,m)))),SEQUENCE(SUM(C3:N4)),1)

In cell D8:

=LET(a,C3:N4,--MID(CONCAT(REPT(C2:N2,MAP(a,LAMBDA(m,m)))),5*(SEQUENCE(SUM(a))-1)+1,5))

I attached the images so you can see the result.

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[DanteAmor]

Hi @Stomli

I updated the formulas and images:

In cell B8:

=MID(CONCAT(REPT(B3:B4,MAP(C3:N4,LAMBDA(m,m)))),SEQUENCE(SUM(C3:N4)),1)

Or like this, so you only have to change a range of cells.

=LET(a,C3:N4,MID(CONCAT(REPT(B3:B4,MAP(a,LAMBDA(m,m)))),SEQUENCE(SUM(a)),1))

In cell D8:

=LET(a,C3:N4,--MID(CONCAT(REPT(C2:N2,MAP(a,LAMBDA(m,m)))),5*(SEQUENCE(SUM(a))-1)+1,5))

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[kweaver]

Dante, why isn't "m" defined somewhere?

 

[DanteAmor]

Hi Kweaver:

Dante, why isn't "m" defined somewhere?

'm' in Lambda takes the matrix of the function where it is embedded, in this case Map(a is the function and matrix 'a' is the matrix 'm' inside Lambda.

MAP(a,LAMBDA(m,m))