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.

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... :-)

"esecan" wrote:

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 - thanks but I am still not there.

In the second formula, I think that you mean C2 down below when you really
but in B3 but please confirm. Even with that change, I am having a problem
doing a "right copy". When I select the two cells and then drag across the
row or do a copy paste, it doesn't build correctly. I get column B with one
conditional, column C with two, column D with one, column E with two. It
just alternates with one and two instead of building. What am I missing?
Really your solution is the same as my concatenation approach but you are
building up the list dynamically with the copy paste. (Now I just need to
figure that part out).

If you up to it, I have two more challenges that I would like to add to this
spreadsheet. I'll list them here now. First one easier than the second.

1) I'd like to add an 'All' column. If this box gets checked on the first
sheet then add that person to everyone else's list.

2) I'd like to add a "reciprical" column. If Amy pays for the reciprical
Amy will get added to anyone that buys a basket for Amy. This is a way to
not buy the whole list but to make sure that anyone who gets one for you will
also get your name on theirs.



"Sheeloo" wrote:

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... :-)

"esecan" wrote:

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.

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" wrote:

Sheeloo - thanks but I am still not there.

In the second formula, I think that you mean C2 down below when you really
but in B3 but please confirm. Even with that change, I am having a problem
doing a "right copy". When I select the two cells and then drag across the
row or do a copy paste, it doesn't build correctly. I get column B with one
conditional, column C with two, column D with one, column E with two. It
just alternates with one and two instead of building. What am I missing?
Really your solution is the same as my concatenation approach but you are
building up the list dynamically with the copy paste. (Now I just need to
figure that part out).

If you up to it, I have two more challenges that I would like to add to this
spreadsheet. I'll list them here now. First one easier than the second.

1) I'd like to add an 'All' column. If this box gets checked on the first
sheet then add that person to everyone else's list.

2) I'd like to add a "reciprical" column. If Amy pays for the reciprical
Amy will get added to anyone that buys a basket for Amy. This is a way to
not buy the whole list but to make sure that anyone who gets one for you will
also get your name on theirs.



"Sheeloo" wrote:

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... :-)

"esecan" wrote:

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.

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.

"Sheeloo" wrote:

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" wrote:

Sheeloo - thanks but I am still not there.

In the second formula, I think that you mean C2 down below when you really
but in B3 but please confirm. Even with that change, I am having a problem
doing a "right copy". When I select the two cells and then drag across the
row or do a copy paste, it doesn't build correctly. I get column B with one
conditional, column C with two, column D with one, column E with two. It
just alternates with one and two instead of building. What am I missing?
Really your solution is the same as my concatenation approach but you are
building up the list dynamically with the copy paste. (Now I just need to
figure that part out).

If you up to it, I have two more challenges that I would like to add to this
spreadsheet. I'll list them here now. First one easier than the second.

1) I'd like to add an 'All' column. If this box gets checked on the first
sheet then add that person to everyone else's list.

2) I'd like to add a "reciprical" column. If Amy pays for the reciprical
Amy will get added to anyone that buys a basket for Amy. This is a way to
not buy the whole list but to make sure that anyone who gets one for you will
also get your name on theirs.



"Sheeloo" wrote:

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... :-)

"esecan" wrote:

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.