Prev: Run formula automatically
Next: How to get result by using CountIF, if there are 3 conditions
From: driller on 6 Jul 2008 01:56
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

--
regards,

From: H�ctor Miguel on 6 Jul 2008 19:00
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 __
> 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.


From: Dave Peterson on 6 Jul 2008 19:10
Check your other post, too.

driller wrote:
>
> 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
>
> --
> regards,

--

Dave Peterson
 | 
Pages: 1
Prev: Run formula automatically
Next: How to get result by using CountIF, if there are 3 conditions