Forum Discussion

cdavison's avatar
cdavison
Occasional Reader
Jun 25, 2026

HELP WITH EXCEL

Struggling to do a simple transition in Excel if anyone can give some advice:   I have 3 items I need to run up & down instead of across... is there any easy way ??? So I need the Name and two of t...
excel
Formulas and Functions
NikolinoDE's avatar
NikolinoDE
Platinum Contributor
Jun 25, 2026

Formula suggestion if Office 365 is being used…

=LET(

data, A1:Z2,

row1, INDEX(data, 1, SEQUENCE(, COLUMNS(data))),

row2, INDEX(data, 2, SEQUENCE(, COLUMNS(data))),

pos, FILTER(SEQUENCE(, COLUMNS(data)), row1<>""),

names, INDEX(row1, pos),

xx1, INDEX(row2, pos),

xx2, IFERROR(INDEX(row2, pos+4), ""),

out, HSTACK(TRANSPOSE(names), TRANSPOSE(xx1), TRANSPOSE(xx2)),

FILTER(out, INDEX(out,,1)<>""))

 

If you want a truly bulletproof version...

The only 100% flexible solution (no offsets at all) is:

Power Query “Unpivot + Group by Name detection”

That approach:

  • doesn’t care about spacing
  • doesn’t care about columns
  • rebuilds structure logically

My answers are voluntary and without guarantee!

 

Hope this will help you.

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jun 26, 2026

As a comment

INDEX(data, 1, 0)

or even

INDEX(data, 1, )

works exactly the same way as

INDEX(data, 1, SEQUENCE(, COLUMNS(data)))

Plus names with numbers (row1, row2, etc) are not accepted within LET. It could be rowA, rowB, etc.