vba - Excel transpose formula -

i've been wraping head around time , don't know how approach problem. table consists of groups of data want transpose rows columns. every row has index number in first column , of rows in 1 group have same index.

1 a
1 b
1 c
1 d
1 e
1 f
1 g
1 h
2 as
2 bs
2 cs
5 ma
5 mb
5 mc
5 md

and want final result be:

1 b c d e f g h
2 bs cs
5 ma mb mc md

is possible formulas or have in vba?

yes possible. need following functions:

1. if
2. match
3. isna
4. index

assume have data in sheet 1 in columns , b:

c1:

place value "1" in cell c1

c2:

=c1+1

drag down as needed

d1

=match(c1,a:a, 0) 

drag down as cell c2

e1

=match(c1,a:a, 1) 

drag down as cell c2

sheet 2:

now place following formulas in cell a1 in sheet2:

=if(isna(sheet1!$d1), "", if(sheet1!$d1="", "", if(column()-1+sheet1!$d1 <=sheet1!$e1, index(sheet1!$b:$b, column()-1+sheet1!$d1), ""))) 

drag / copy many cells needed:

result:

also have article on blog index function. might excel index function.

you can download complete file here.