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

 

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

 

[Stomli]

Thanks for your assistance on this. I think the scaling will be a problem as this tool will be used for 7,000 user IDs and 80 separate divisions. Also, the separate placement of the formulas will be a problem since the division range will change (column B) with any changes to the number of user IDs.

I am currently trying to develop 2 helper sections 1 that shows which month the dates are pulled from and 1 that reflects the proper User ID for the correct month. I will provide an update in a bit to show what I am doing.

 

[Stomli]

@DanteAmor Thank you for this post. I had started to write the previous post before seeing yours. Let me see how your formula works with this and I will follow up. Thanks!

 

[Stomli]

@DanteAmor That formula is great. Could you provide a brief breakdown of how these 2 formulas particularly how the Mid, Concat, Rept, and Sequence tie into each other?

Also, I noticed the Mid was based off of one character. What would I need to look into for a 3 character division name?

Since the dates are going to be tied to the user ID, is there a way to ensure that the user ID corresponds to the date and the division (the division is a lookup based on the user ID)?

Thanks again for your help on this. This forum is AWESOME!!!

 

[DanteAmor]

While explaining I found a simpler solution.

For Divisions. They can be 1 character, 3 characteres or whatever you want.

=TEXTSPLIT(CONCAT(REPT(B3:B4&"|",MAP(C3:E4,LAMBDA(m,m)))),,"|")

Fo dates:

=IFERROR(TEXTSPLIT(CONCAT(REPT(C2:E2&"|",MAP(C3:E4,LAMBDA(m,m)))),,"|")+0,"")

Example:

--------------
Regards
Cordially
Dante Amor
--------------​

"If life gives you lemons, learn how to make lemonade."
"Si la vida te da limones, aprende a hacer limonada."
- Dicho popular -​

 

[Fluff]

As the MAP function is not doing anything, you can simply use

=TEXTSPLIT(CONCAT(REPT(B3:B4&"|",C3:E4)),,"|")

 

[DanteAmor]

As the MAP function is not doing anything, you can simply use

=TEXTSPLIT(CONCAT(REPT(B3:B4&"|",C3:E4)),,"|")

Thanks fluf for the contribution, as I commented with ms365 the possible solutions increase and there may be more efficient ones.

I am learning to use a version of ms365 that is in OneDrive, it is not complete but it helps me to help others. (I can't use 365 because it's a work computer)

Here is the other formula for the date cells.

=IFERROR(TEXTSPLIT(CONCAT(REPT(C2:E2&"|",C3:E4)),,"|")+0,"")

"You don't have to get there first. But you have to know how to get there"
"Que no hay que llegar primero. Pero hay que saber llegar"
- José Alfredo Jiménez -​

 

[Stomli]

@DanteAmor Thanks again for the explanation. The updated formulas tie in well with the full sheet I'm working with. I will be using this going forward.

 

[Stomli]

@Fluff Thank you for the simplification on that section.