How to get the row value to map with column using Excel?

user_2320559 0
Viewed 207

As part of my learning to work with excel, I got stuck in two places:
1 - Get to reference the row values in the column.
2 - Is there a better way to calculate the sequence.

Formula Used:

for 1st sequence

=SEQUENCE(A2, 1, 1,1)

for remaining sequence

=SEQUENCE(A3,1,LOOKUP(2,1/(B:B<>""),B:B)+ 1,1)
=SEQUENCE(A4,1,LOOKUP(2,1/(C:C<>""),C:C)+ 1,1)
=SEQUENCE(A5,1,LOOKUP(2,1/(D:D<>""),D:D)+ 1,1)
=SEQUENCE(A6,1,LOOKUP(2,1/(E:E<>""),E:E)+ 1,1)
...

Instead of manually reference A3, A4, A5, A6;
How do get row values to the column?

I have used LOOKUP() to get the last value from the previous column and start the sequence. Just wanted to know if we have a better approach to do the same?

Output:

enter image description here

excel-365 excel-formula excel
2 Answers

You can use only one formula then drag across to fill desired output.

=SEQUENCE(INDEX($A$2:$A$10,COLUMN(A$1)),,IF(COLUMN()=2,1,MAX(A:A)+1))

enter image description here

user_55147470

It's a rainy day here (again), so I have sat down to see if this can be accomplished in a single formula. Yes, it can:

=LET(data,A2:INDEX(A:A,COUNT(A:A)+1),
rows,ROW(A1:INDEX(A:A,COUNT(A:A))),
columns,TRANSPOSE(ROW(A1:INDEX(A:A,COUNT(A:A)))),
array10,IF(rows>=columns,1,0),
totals,MMULT(array10,data),
totalsPrev,totals-data,
rowsOut,ROW(A1:INDEX(A:A,MAX(A:A))),
startValues,INDEX(totalsPrev,columns),
values,startValues+rowsOut,
endValues,INDEX(totals,columns),
IF(values<=endValues,values,"")
)

enter image description here

The basic idea is to develop running totals of the data, then use those totals to define the start and end points of the data in each column of the output array.

user_38949170
Related