Combine text from multiple columns based on condition

[alok]

Hello, I have data in spread across multiple rows and columns. Column A is list of some email IDs and column B to Z are list of Interests (e.g. Games, Cooking, Philately, etc.) The input of interest is taken on a 10 point scale wherein no value represents that the email ID is not interested in that "Interest" and a number between 1 to 10 represents the interest level (with 10 being highest)


Sample Data

A1 - Email

A2 - xxx@xx.com

A3 - yyy@yy.com

and so on till A10


B1 - Hockey

B2 -

B3 - 2

and so on till B10


C1 - Tennis

C2 - 10

C3 - 2

and so on till C10


Requirement - I want to summarize by columns B to Z all the email IDs with their interest levels in a single cell separated by known identifiers (e.g. ":" and "|")


Output is required in row 11 as follows

B11 - yyy@yy.com:2

C11 - xxx@xx.com:10|yyy@yy.com:2


Hope my requirement is clear... Any help would be appreciated... Thanks!!!

 

[Faseeh]

Hi


Welcome to the forums, with you data in A1:D4, you can use following approach:

[pre]

ID	 Email	        Hockey	 Tennis
7	 xxx@xx.com		 10
8	 yyy@yy.com	2	 2
9	 yay@yy.com	3	 10[/pre]

...use: 


=B2&":"&" "&C2&" "&D2

..drag down.


Regards,

 

[NARAYANK991]

Hi Alok ,


Can you give a little more clarity on your requirements ?


If you have multiple email IDs , with multiple interests , you would like a summary for each column on row 11 ; thus if 7 email IDs have an interest in a game such as Tennis , then in that particular column , one particular cell should have 7 emails IDs and their corresponding interest levels in Tennis ; is this correct ?


If so , then row 11 in each column should contain a concatenated string consisting of each email ID which has an interest in that column's activity , along with the interest level in that column's activity.


Can you please confirm ?


Narayan

 

[alok]

Hi Faseeh and NarayanK,


Both your proposed solutions are known to me as well... which is by simply use of concatenate function or use of & sign. However my actual sheet is huge with numerous rows and thus its not possible for me to write a huge concatenate formula. I am looking for another smarter and time saving technique


@NarayanK: Your understanding is perfectly fine. Only thing is my data is really huge. For simplicity I gave 10 rows in my query above.

 

[NARAYANK991]

Hi Alok ,


The only smarter technique is to use VBA ; is this acceptable to you ?


Narayan

 

[Sylar]

create a function .. would be of the following sort ..


concatenate_new(array,delimiter) .. you can pass the array that has to be worked on plus the delimiter which separates them ..

 

[alok]

Hello Friends,


Thanks for your inputs. Looks like creating a function will be a neater solution. Can someone help me write such a function with two arguments 1. Array and 2. delimitter character. The output should come in a row with one string occupying one column and so on. Thanks in advance.

 

[SirJB7]

Hi, alok!


Give a look at this file:

https://dl.dropbox.com/u/60558749/Combine%20text%20from%20multiple%20columns%20based%20on%20condition%20%28for%20alok%20at%20chandoo.org%29.xlsm


The usage is:

=MultiConcatenate(<text_string&#62

where text_string is a sport/hobby/etc.


Regards!