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

 

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

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

But all must be 3 characters.

=LET(a,C3:N4,MED(CONCAT(REPETIR(B3:B4,MAP(a,LAMBDA(m,m)))),3*(SECUENCIA(SUMA(a))-1)+1,3))

---- *** ----​

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

You should show me how those IDs are.
The formula obtains the data of the dates and the divisions from left to right and from top to bottom.

---- *** ----​

Could you provide a brief breakdown of how these 2 formulas particularly how the Mid, Concat, Rept, and Sequence tie into each other?

Of course.
If with the formulas before MS365 there were several solutions to the same problem, now with the new MS365 formulas, the number of solutions for the same problem has increased considerably.

I explain my solution, maybe there are more solutions, maybe simpler or more complicated, but this is how I tried to solve it.

I have to tell you that I have barely 2 months of using a version of 365 that is in the cloud and the functions come in English and Spanish, add that it is a slightly different Spanish from the one I usually use in the functions. In fact it is the first time that I use the LAMBDA and MAP functions.

---- *** ----​

how the Mid, Concat, Rept, and Sequence tie into each other?

I will explain using an example of 3 months:

In this case, with the MAP function, I am passing the matrix from C3 to E4 to the LAMBDA function, which in this case does not require doing anything to the matrix, I only need it to return the matrix but applying to each data in the matrix the REPT function:

So in the example cell C3 has a 1, it means that the data in cell B3 is going to be repeated 1 time.
Cell D3 has a 3, it means that the data in cell B3 is going to be repeated 3 times.
Look in the following image how the division is repeated the number of times it appears in the matrix.

The CONCAT function concatenates all the results on a single line. This is the option I found. Maybe there is another solution.

Now the difficult thing is to separate each division in a cell vertically.

To separate the string into rows I use the MID function, this function needs the initial position, for that I use the sequence function, which will give us position 1, 2, 3 and even the total number of characters in the string, how many are? the sum of the numbers in the range C3:E4; and the last parameter of the MID function is the number of characters to take from the string, in this case the division is 1.

​

---- *** ----


I hope the explanation helps you.
Cordially
Dante Amor
--------------​

​

 

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