List of cells that were selected

[esecan]

I have a list of 250 names where each name can select one or all of the same
names in the list to send a gift basket. The final output needs to have two
key columns: the name of the recipient and the list of names of people that
selected the person. I will pass this list to microsoft word in mail merge
and create a letter which lists all of the people that selected the person.

I created a 5x5 prototype which works but is not a great option for the 250
list. In my prototype I put 'X' in the grid and then did a large concenate
function where I concatenated the name from the heading if there was an 'X'
in the column. I guess I could do this for all 250 rows but there has to be
a better way. Any ideas?


example

Mary John Bill Ann
Mary X X
John X
Bill
Ann X X X

In this example, Mary bought a basket for John and Bill. John bought one
for Mary and Ann bought one for everyone. In reality everyone is going to
get ONE basket so the problem is just creating a letter that lists all of the
people that bought a basket for the person.

Mary would receive a letter with John and Ann's name, John would get one
from Mary and Ann. (You get the picture.)

My solution was to create a common separate list of all the names in a
single field and then use this in the mail merge of MS word. That isn't
going to work that well for 250 names but I guess it could if there is no
easier way.

 

[Sheeloo]

Cleanest solution would be through a macro...

But one solution could be as follows;

Since you have 250 names in first row and first column, you will have X or
blank in the range B2:IQ251, right? Suppose you have all this in Sheet1.
Copy row 1 and Col A from Sheet 1 to Sheet2

In B2 of Sheet2 enter this
=IF(Sheet1!B2="X",B$1,"")
in B3 of Sheet2 enter
=IF(Sheet1!C2="X",IF(B2="",C$1,B2&","&C$1),B2)
and copy right to IQ2

Now Copy B2:IQ2 to B3:IQ251

In Column IQ you will get the concatenated names...

The file I tested with was about 6MB in size...

Let me know how it goes...

 

[Sheeloo]

Yes, you are right. Second formula should go into C2.

Also just select C2 and copy right upto IQ2
Then copy the whole row down to 251

I will think about the other challenges and get back to you tomorrow.

 

[esecan]

I have solved the "all" problem and I have a plan for the reciprocal problem.
Thanks for the help and I don't need any more guidance unless I get stuck.

By the way, your solution was inverted. That was probably a fault of my
description but I inverted the rows to columns to aggregate the names
correctly.

Thanks.