But if the original table were sorted first by col A (name)
then by col B (role index), the summary table could be
built much more efficiently.

Yep, that would make things much easier.

--
Biff
Microsoft Excel MVP


"Harlan Grove" wrote in message
...
"T. Valko" wrote...
If you have 100's of unique names this won't be very efficient....

But if the original table were sorted first by col A (name) then by
col B (role index), the summary table could be built much more
efficiently.

Names on Sheet1 in the range A2:A20 assigned the named range: Name
Roles on Sheet1 in the range C2:C20 assigned the named range: Role

Sheet2 B1:?1 = names
Sheet2 A2:A? = roles
...

If the names in row 1 of Sheet2 were in the same order as the sorted
names in the table in Sheet1 and roles in col A of Sheet2 were in the
same order as roles in table in Sheet1, you could use

B2:
=IF(ROWS(B$2:B2)<=COUNTIF(Name,B$1),
INDEX(Role,MATCH(B$1,Name)+ROWS(B$2:B2)-1),"")

Fill B2 right as far as needed so there's a formula under each name in
row 1, say to Z2, then select B2:Z2 and fill down as far as needed so
there are formulas in these columns to the right of every role index
in col A.

Sorting is good. Makes other things easy and efficient.