One play which produces the desired transformation
Illustrated in this sample:
http://freefilehosting.net/download/41mae
Extract multiple results horizontal.xls
Source data assumed in Sheet2, in A2:B2 down
In Sheet1,
List the unique Sheet2's col B source items in A1:D1
In A2: =IF(Sheet2!$B2=A$1,ROW(),"")
Copy A2 across to D2, fill down to cover the
max expected extent of source data in Sheet1
In F2, copied down to F5: =INDEX($A$1:$D$1,ROWS($1:1))
In G2:
=IF(COLUMNS($A:A)COUNT(OFFSET($A:$A,,ROWS($1:1)-1)),"",
INDEX(Sheet2!$A:$A,SMALL(OFFSET($A:$A,,ROWS($1:1)-1),COLUMNS($A:A))))
Copy G2 across as far as required, fill down to return the desired results.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:362 Subscribers:64
xdemechanik
---
Sheet-1 Sheet-2
Col-a Col-a col-b
aa-01 xy-001 aa-02
aa-02 xs-001 cc-01
cc-01 xx-002 aa-02
cc-02 yz-001 cc-02
I want result like in Sheet-1 at Col-b & C
Sheet-1
Col-a col-b col-c
aa-01
aa-02 xy-001 xx-002
cc-01 xs-001
cc-02 yz-001