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

Question

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:

Harun24hr · Answer 1 · 2021-08-09T08:25:15.330

2

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

edited Aug 09 '21 at 08:25

answered Aug 09 '21 at 08:13

Harun24hr

30,391
4
21
36

Error: Function SEQUENCE parameter 2 value is 0. It should be greater than or equal to 1. – John Smith Aug 09 '21 at 08:37
=ARRAY_CONSTRAIN(ARRAYFORMULA(SEQUENCE(INDEX($A$2:$A$10,COLUMN(A$1)), ,IF(COLUMN()=2,1,MAX(A:A)+1))), 4, 1) – John Smith Aug 09 '21 at 08:43
1

@JohnSmith Are you using `google-sheet` or Excel? – Harun24hr Aug 09 '21 at 09:14
If you use `google-sheet` then use `=SEQUENCE(INDEX($A$2:$A$10,COLUMN(A$1)),1,IF(COLUMN()=2,1,MAX(A:A)+1))` – Harun24hr Aug 09 '21 at 09:15
The same formula isn't working on excel 2003! – John Smith Aug 09 '21 at 13:04
1

@JohnSmith Sequence formula is only available to Excel-365. So earlier version will not work. – Harun24hr Aug 10 '21 at 03:54

score 1 · Answer 2 · answered Aug 09 '21 at 15:51

1

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,"")
)

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.

answered Aug 09 '21 at 15:51

Tom Sharpe

30,727
5
24
37

Really nice. I thought of making a single formula as well, but was not far enough along before I had to quit and get some real work done. Some rain could have helped, I suppose. Just curious, why do you not do SEQUENCE? As LET is 365, SEQUENCE is in the same version. I time tested SEQUENCE once and found it to be slow and should be used sparingly, but never compared it to ROW(x:INDEX. – mark fitzpatrick Aug 11 '21 at 06:36
Thanks Mark. Good question - I don't have anything against using SEQUENCE at all (although I hadn't thought about the time implications), but in this case I wanted to use ROW and COLUMN with it and the only way I could think of was to start with an actual range rather than an array. – Tom Sharpe Aug 11 '21 at 07:26
SEQUENCE has become a crutch to me, so I should use ROW(INDEX more often because it works in older versions. Someday (maybe) I will time test the two. I once did a reshaping & reordering formula with SEQUENCE for a range involving 1000's of cells and saw the impact of SEQUENCE, so I reworked the formula to do only 3 SEQUENCES instead of 6. At that scale, the impact was noticeable. – mark fitzpatrick Aug 11 '21 at 11:28

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

2 Answers2