Combine variable number of columns

[Mack Neff]

I have a single spreadsheet with a list of clients, addresses and their
product interests. This table will be used to drive a Mailmerge document. In
the document, I want to be able to refer to the products in which they
indicated an interest. The problem is that while one client may have
identified only one product (one column) others have selected anywhere from
two to 170 products - each product is in a separate column. I can join two
columns with "&", but when I have an inconsistent number of columns how do I
do this efficiently? - I will have to do it for each client, each month, so
am not interested in manually typing all 171 combinations.

 

[Pete_UK]

Maybe you can use an extra column which is a count of the number of
items in the columns covering up to 170, eg:

=COUNT(G2:FT2), or
=COUNTA(G2:FT2)

if your product columns start with G.

Hope this helps.

Pete