As shown below, you need to convert the data on the left to the right in batches based on the number of recurrences
Introduce two quick implementation methods, VLOOKUP formula and TOCOL formula
Method 1: Use the VLOOKUP formula
First of all, you need to create a helper column on the far left of the data, and the formula entered in cell A2 is:
=SUM($C$1:C1)
The first C1 fixed reference indicates that the sum is accumulated starting from the C1 cell
When the data is populated downward, you need to exceed the original data row, as follows:
Then we just need to enter the formula:
=VLOOKUP (ROW ().-1,A:B,2,1)
The ROW() function is the line number that returns the current line
Then find a match for the secondary column and the data column, and use an approximate match to find it
Method 2: Use the TOCOL formula
If it is inconvenient to create auxiliary columns and want to complete them in one go, then you need to use the new function formula TOCOL
One formula will do the trick:
=TOCOL (IF (B2:B4>=COLUMN (1:1),A2:A4,NA ()), 2)
It seems that the formula is very complicated, but it is very simple for us to break it down step by step and understand its calculation ideas
首先是COLUMN(1:1)
It is a one-dimensional array, and the result is 1, 2, 3 all the way up to 16384, the maximum number of columns in the table
Then we use the formula:
=B2:B4>=COLUMN(1:1)
It is to use our numbers to judge whether they are greater or equal than the natural numbers
When the number is 2, it will have two TRUEs, and when it is 3, it will have 3 TRUE
Then we use:
=Ib(P2:P4>=Time(1:1),A2:A4N())
If it corresponds to the TURE above, the name will be returned, otherwise, the wrong value will be returned
The last step is very simple, we use the TOCOL formula to concatenate these 3 rows of data, and the parameter is 2 to ignore the error values and join them into a column:
Have you learned about this function formula?