Thread: Excel help - combining two sets of columns with multiple and variable data in each

Hi all -
I finally ran up against an issue that I hope someone can assist with... nothing like the last 2011 Excel question on a Friday at 5:00 pm. But your help is needed, so here goes:

I am trying to run a comparison of several columns and deliver output:

Column A contains the name of a "Composite Role" (2750 rows / 600 unique values) and attached to each Composite Role is at least 1 Single Role (Column B (2000 unique values / total of 2700 rows).

COMPOSITE ROLE 1 SINGLE Role 1
COMPOSITE ROLE 1 SINGLE Role 2
COMPOSITE ROLE 1 SINGLE Role 3
COMPOSITE ROLE 2 SINGLE Role 1
COMPOSITE ROLE 2 SINGLE Role 4
COMPOSITE ROLE 3 SINGLE Role 5
etc.

Along with the above, I have Column D with additional information. It contains the name of a single role (identified also in column B) and in Column E, a named profile with at least one (usually more) piece of data. There are 3000 rows of data in Column D/E.
SINGLE Role 1 PROFILE 1
SINGLE Role 1 PROFILE 2
SINGLE Role 2 PROFILE 1
SINGLE Role 3 PROFILE 3
SINGLE Role 4 PROFILE 4
SINGLE Role 4 PROFILE 5
SINGLE Role 5 PROFILE 6
etc.


My request is the formula to deliver the output on a new page:

Composite Role (Col A) with the profile listed (Col B). I have put in parenthesis where the "middle man" is:

COMPOSITE ROLE 1 PROFILE 1 (from Single Role 1)
COMPOSITE ROLE 1 PROFILE 1 (from Single Role 2)
COMPOSITE ROLE 1 PROFILE 2 (from Single Role 1)
COMPOSITE ROLE 1 PROFILE 3 (from Single Role 3)
COMPOSITE ROLE 2 PROFILE 1 (from Single Role 1)
COMPOSITE ROLE 2 PROFILE 2 (from Single Role 1)
COMPOSITE ROLE 2 PROFILE 4 (from Single Role 4)
COMPOSITE ROLE 2 PROFILE 5 (from Single Role 4)
COMPOSITE ROLE 3 PROFILE 6 (from Single Role 5)
etc..


If anyone can assist on this, it would be greatly appreciated.

can you attach a file with an example of what you have, and what you want?