looking up multiple values in an arrray

[idaho_bruce]

I need some hel.

I have three columns of data in a worksheet. Column A contains the names of
people. Each person's name may appear multiple times in column A depending
on how many roles (responsibilities) that person has. The roles are listed
in column C. Example: Column A may list "Mary" five times if she has five
different Roles listed in Column C. I've added an index in Column B relating
to the role in Column C. Column B contains the text entries "Role_1" through
"Role_32" as needed for each person. If a person has only one role, then
Column B will contain only one entry for that person and the entry would be
"Role_1." But, for "Mary", Column B contains five entries, "Role_1" through
"Role_5", corresponding to the descriptions of Mary's roles in Column C.

Now, I'm trying to set up a separate worksheet in which each person appears
once as a column header and the role names, ranging from Role_1 to Role_32,
are the row headers. In the cells, I want to display the text values from
column C of the first worksheet.

I've worked with vlookup and hlookup. This seems like kind of a nested
lookup function. I have not worked with array functions. Can you help me?

 

[T. Valko]

If you have 100's of unique names this won't be very efficient....

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

Enter this array formula** in Sheet2 B2:

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

Copy across as needed then down as needed.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[idaho_bruce]

Thank you! This hit the nail on the head. Now, I'll have to go study what
you did.
Thanks, again!!!

 

[Harlan Grove]

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.

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[T. Valko]

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.