# sorting array formula

D

#### driller

Good day,

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

#### 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 __

D

#### Dave Peterson

Good day,

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