Re-purpose Data from rows to columns

[nanao56]

Hello!

I'm not quite sure how to do this in Excel. It seems simple but I'm trying to build a scale-able way to do it.

I am getting 2 data files, one source is prospect names with a unique ID. The second is a file with the unique ID and with campaigns they are attached to.

I'm trying to combine both files so that the end result takes the campaign attendance for each prospect and places it as a column.

1st file with prospects and ID's
People-ID First Name Last Name
89 John Smith
24 Jane Doe
13 Bill Foster

2nd file with prospect ID and campaign
People-ID Campaign Name Campaign ID
13 Campaign A FG-4222011
13 Campaign C PPX
24 Campaign A FG-4222011
24 Campaign B PH-512011
24 Campaign D RL-432011
89 Campaign A FG-4222011
89 Campaign C PPX

Final result of what I want

People-ID First Name Last Name Campaign A Campaign B Campaign C Campaign D
89 John Smith Y N Y N
24 Jane Doe Y Y N Y
13 Bill Foster Y N Y N

You might be able to see a better result with the attached mock-up.
https://skydrive.live.com/view.aspx?cid=8F7724F863564332&resid=8F7724F863564332%21230

I need some help on how to write a look-up formula that I can add unique names of the campaign names and add it horizontally next to each person's name and mark it with an X under each of the columns to indicate if they attended.

I'm sorry I'm not sure on how to do this dynamically.

Anyone know?

Thanks

David

 

[jbeaucaire]

Assuming you already know your campaign names:

1) Put the data on the data sheet as shown, listing the campaigns across the row1 in the order you wish.

Excel Workbook
	A	B	C	D	E	F	G
1	People-ID	First Name	Last Name	Campaign A	Campaign B	Campaign C	Campaign D
2	89	John	Smith
3	24	Jane	Doe
4	13	Bill	Foster
Data

2) Put the Campaign data on a second sheet called Campaign in the format you originally provided.

Excel Workbook
	A	B	C
1	People-ID	Campaign Name	Campaign ID
2	13	Campaign A	FG-4222011
3	13	Campaign C	PPX
4	24	Campaign A	FG-4222011
5	24	Campaign B	PH-512011
6	24	Campaign D	RL-432011
7	89	Campaign A	FG-4222011
8	89	Campaign C	PPX
Campaign

4) Now add this formula to cell D2:

=IF(ISNUMBER(MATCH($A2&D$1, INDEX(Campaign!$A$1:$A$1000&Campaign!$B$1:$B$1000, 0), 0)), "Y","N")

...and copy that down/across the whole table. I used 1000 rows on your Campaign sheet, hopefully that's close to enough. Don't overexpand that, it could affect your sheet performance speed.

Excel Workbook
	A	B	C	D	E	F	G
1	People-ID	First Name	Last Name	Campaign A	Campaign B	Campaign C	Campaign D
2	89	John	Smith	Y	N	Y	N
3	24	Jane	Doe	Y	Y	N	Y
4	13	Bill	Foster	Y	N	Y	N
Data

 

[nanao56]

Oh wow..Jerry. That works. Your formula is great because I think I understand what you did with the Index and Match.

Thanks!