sorting array formula

[driller]

Good day,

Please help me prepare a sorted column of infos based on below (e.g) table

In sheet 1
col A col B col C col D col E
row 1 NAME 100 105 215 280
row 2 ABC A1 A2
row 3 CDE C1 C2
row 4 DEF X1 X3
row 5 FGH F1 F2

Result desired thru formulation
In Sheet 2 (e.g)

col A col B col C
row 1 NAME SN SQ
row 2 ABC A1 100
row 3 ABC A2 105
row 4 CDE C1 105
row 5 CDE C2 215
row 6 DEF X1 100
row 7 DEF X3 280
row 8 FGH F1 105
row 9 FGH F2 280

looking for short or long formula.
TIA

 

[Héctor Miguel]

hi, (...) ?

array formulae (Ctrl-Shift-Enter) in your sheet2: (watch for message line-wrapping)

[A2] =index(sheet1!a:a,small(if(sheet1!$b$2:$e$5<>"",row(sheet1!a$2:a$5)),row(a1)))

[B2] =index(sheet1!$a$1:$e$5,match(a2,sheet1!a:a,0),small(if(sheet1!a$2:a$5=a2,
if(sheet1!$b$2:$e$5<>"",column(sheet1!$b$2:$e$5))),countif(a$2:a2,a2)))

[C2] =index(sheet1!$a$1:$e$1,max((sheet1!$b$2:$e$5=b2)*column(sheet1!$b$2:$e$5)))

drag/copy down as needed (you'll need to catch errors)

hth,
hector.

__ OP __

 

[Dave Peterson]

Check your other post, too.

Good day,

Please help me prepare a sorted column of infos based on below (e.g) table

In sheet 1
col A col B col C col D col E
row 1 NAME 100 105 215 280
row 2 ABC A1 A2
row 3 CDE C1 C2
row 4 DEF X1 X3
row 5 FGH F1 F2

Result desired thru formulation
In Sheet 2 (e.g)

col A col B col C
row 1 NAME SN SQ
row 2 ABC A1 100
row 3 ABC A2 105
row 4 CDE C1 105
row 5 CDE C2 215
row 6 DEF X1 100
row 7 DEF X3 280
row 8 FGH F1 105
row 9 FGH F2 280

looking for short or long formula.
TIA